The query is select * from events where tenant_id = $1 order by created_at desc limit 50. Without the tenant filter it returns in about 8ms. With it, 300 to 400ms, and worse for small tenants than big ones, which makes no sense to me. Table is around 30 million rows and there's an index on created_at and a separate one on tenant_id. Postgres 15, stats look current.
Run explain (analyze, buffers) on both versions and look at the buffer numbers rather than the timings. You'll see something like 12 shared hits on the fast one and 400,000 on the slow one, and that difference is the whole story. Timings vary with cache state, buffers don't lie.
Also drop the select * while you're in there. On a 30 million row events table you're very likely pulling a jsonb payload column across the wire for 50 rows you're only going to show three fields from, and that cost shows up in the client, not in the query plan.
Worth checking whether that payload is big enough to be TOASTed as well. If it is, each row is potentially an extra fetch from the toast table and it never appears anywhere obvious.
Longer term, if tenants vary in size by three orders of magnitude, look at partitioning by tenant or at least keeping per-tenant statistics targets higher. The planner's default sampling is not great at very skewed distributions and you'll hit this shape of problem again on a different query.
@cold_storage_kim · 6mo ago · 3 replies
The planner is walking backwards down the
created_atindex and throwing away every row that isn't your tenant until it collects 50. For a tenant with 40% of the table that's quick. For a tenant with 0.1% of the table it reads millions of rows to find fifty, which is why small tenants are slower. That inversion is the signature of this exact problem.You want one composite index in the order the query needs:
create index concurrently on events (tenant_id, created_at desc);Then the equality column narrows first and the sort comes free from the index order. Your two single-column indexes can't do that, the planner has to pick one and pay for the other half.
Drop the standalone
tenant_idindex afterwards, the composite covers everything it did.Reply
Report
@cold_storage_kim · 6mo ago
Rule of thumb that gets you most of the way: equality columns first, then the range or sort column, then anything you're only selecting. And build them with
concurrentlyon a live table unless you enjoy explaining a lock to your colleagues.Reply
Report
@arraysformula · 6mo ago
3ms. And it's now the same for every tenant. The bit I hadn't understood is that column order in a composite index matters that much.
Reply
Report