4 ms·
Hey, I think you'll probably get better performance if you swap your subquery around a little from: SELECT COUNT(*) FROM runs runs_other WHERE (SELECT CO
by mijoharas 5y ago
Hey, I think you'll probably get better performance if you swap your subquery around a little from:
SELECT COUNT(*)
FROM runs runs_other
WHERE (SELECT COUNT(*)
FROM stacks
WHERE stacks.id = runs_other.stack_id
AND stacks.account_id = accounts.id
AND stacks.worker_pool_id = worker_pools.id) > 0
AND runs_other.worker_id IS NOT NULL
To:
SELECT COUNT(*)
FROM runs runs_other
WHERE EXISTS (SELECT *
FROM stacks
WHERE stacks.id = runs_other.stack_id
AND stacks.account_id = accounts.id
AND stacks.worker_pool_id = worker_pools.id)
AND runs_other.worker_id IS NOT NULL
Your previous option needs to hit every row in the stacks subquery to generate the count. The modified one uses EXISTS so it only needs to check there's at least one row, and then can short-circuit and exit that subquery. (a smart enough query planner would be able to do the same thing, but PG generally doesn't).
- cube2222 5y agoThanks for the tip! I didn't know EXISTS can provide such benefits. However, I've tried it out and it resulted in a 10x performance loss. Looking at the query plan, it actually got PostgreSQL to be too smart and undo most of the optimizations I've done in this blog post... It's still an order of magnitude faster than the original query though!
- mijoharas 5y agoI stand corrected, and always glad to see the main point about perf optimisation proven. (always measure! :) )