> because Postgres will check the security for all rows before filtering on the joins, doing anything with WHERE clauses, doing anything to even tentatively take LIMIT into account, etc.
Note that the above only happens for non-inlinable[1] functions used inside RLS policies.
Going from what you mentioned below, it seems your main problem are SECURITY DEFINER functions, which aren't inlinable.
It's possible to avoid using SECURITY DEFINER, but that's highly application-specific.
Try it with RLS policies that have any plain JOINs in them to reference other tables and you'll see execution times balloon massively (as in, orders of magnitude worse) for a lot of simple use cases, because it's then doing the RLS checks against every involved table to determine if your original RLS check is allowed to use them. The only way around that if you have multiple tables involved in determining access is to use cached subqueries with SECURITY DEFINER functions that aren't subject to the recursive RLS checking.
Note that the above only happens for non-inlinable[1] functions used inside RLS policies.
Going from what you mentioned below, it seems your main problem are SECURITY DEFINER functions, which aren't inlinable.
It's possible to avoid using SECURITY DEFINER, but that's highly application-specific.
[1]:https://wiki.postgresql.org/wiki/Inlining_of_SQL_functions#I...