3 ms·
On my local Postgres 13 instance, the EXPLAINation of both of those queries looks identical – which is a trap, because the path of the query planner's understan
by MrDOS 5y ago
On my local Postgres 13 instance, the EXPLAINation of both of those queries looks identical – which is a trap, because the path of the query planner's understanding is narrow. It's quite easy to write a subquery which must be evaluated over and over again for each row of the parent query. Grouping (either inside or outside of the subquery) is usually a good way to trigger this pathological behaviour (which sucks, because grouping is often where you want to use a subquery!).
I usually find CTEs to be a relatively tolerable middle ground of readability and performance (although I don't run from HAVING, either):
with category_totals as (select category,
sum(amount) as total
from mytable
group by category)
select category,
total
from category_totals
where total > 100;
Either way, subqueries are a code smell for me.
- simondotau 5y agoMy fully custom website (thousands of pages per minute) has an authentication query that runs on every page—and it has a subquery inside a subquery inside a subquery. That's four layers of SQL inception. And it executes in under ten milliseconds. Subqueries are indeed a performance red flag when the query is returning more than a few rows AND the subquery references fields outside of the subquery. Or put more simply, a subquery that will need to be executed an unreasonable number of times per run. But so long as you aren't doing anything like that, and your database engine has a competent query planner, there should be zero difference in performance. For this reason I don't consider subqueries to be a code smell. For me a query is "smelly" when it does things which aren't obvious to a human parsing the query with their brain.