Earlier quoted context omitted.
At its core it's a pretty simple multi-tenancy arrangement. Think something like this: tenants (id, updated_at) tenants_users (id, updated_at, tenant_id, user_id) products (id, updated_at, name, tenant_id) product_variants (id, updated_at, product_id, name) One of the tenants views a page that does a simple `SELECT * FROM products ORDER BY updated_at LIMIT 100`. The RLS checks have to reference `products` -> `tenants…
Do you have an index on `updated_at` ?
Yes. That's also irrelevant to the cause of the performance issues, which all happen before the ORDER BY and LIMIT even come into the picture in Postgres' query optimization.
Edit: To give a better idea of the impact of RLS here, writing up an equivalent query outside of the RLS context [1] has an under-1-second response time, where RLS turns that into 10x the time even in the most optimized case.
[1]: This kind of thing, roughly:
SELECT *
FROM products
JOIN tenants ON products.tenant_id = tenants.id
JOIN tenants_users ON tenants.id = tenants_users.tenant_id
WHERE tenant_users.user_id = auth.uid()
ORDER BY updated_at
LIMIT 100