4 ms·
Optimization in postgres (like all databases I've used) is super counter-intuitive and baffling at times and the only real way to be sure of anything is to try
by seanhunter 7y ago
Optimization in postgres (like all databases I've used) is super counter-intuitive and baffling at times and the only real way to be sure of anything is to try it yourself on your own data and see (and repeat the test whenever you do a major db upgrade). As an example which may well have changed but definitely was the case the last time I tested (which is circa pg9) it used to be the case that using ANSI join syntax would make a query 5-10% slower. ie
select a.foo, b.bar
from a, b
where a.b_id = b.id
and a.something ='blah'
would be reliably 5-10% faster than
select a.foo, b.bar
from a
join b on a.b_id = b.id
where a.something ='blah'
even if everything was indexed etc.
Conversely, projecting off unnecessary columns used to result in a big speedup. ie
select a1.foo, b.bar
from
(select foo, b_id from a where something='blah') a1
join b on a1.b_id = b.id
used to be significantly faster than either of those queries on wide tables.
edit: few typos and additional example added
- jimktrains2 7y agoI'm not disagreeing, but it also helps to look at the EXPLAIN output (including things like width and rows) for queries to better understand what's actually happening. That helps build intuition, but like you said, major version releases can change that behavior. Postgres is usually pretty good at documenting those changes, though.
- seanhunter 7y ago100% agree.
- anarazel 7y agoThere has to be more to the situation than the aove. Unless you configured join_collapse_limit to be way lower than the default, the JOIN query just gets transformed into the former. Any chance you either had a lot more tables joined together, or you were seeing caching effects? > Conversely, projecting off unnecessary columns used to result in a big speedup. That also just gets inlined, unless you have more than from_collapse_limit items in the from list. And we push down the list of columns we actually need, so there's nothing this would improve anyway.