8 ms·

select category, sum(amount) as total from mytable group by category HAVING total > 100 is equal to (and potentially more performant than) select cate

select category, sum(amount) as total from mytable group by category HAVING total > 100 is equal to (and potentially more performant than) select category, total from ( select category, sum(amount) as total from mytable group by category ) as mysubquery WHERE total > 100

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 ago
  My 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.

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 ago
My 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.