Comment by seanhunter | Hacker News Reader

Comment by seanhunter | Hacker News Reader

It's worth throwing into the mix that CTEs can be an elegant solution to more than just recursive queries. I've used them to "refactor" out common parts of queries where the same subtable is referenced multiple times in the same query.

So instead of

   select something from x join y on x.foo = y.foo and blah = 'cheese' where some_complicated_join_conditions ...
   union
   select something from x join y on x.foo = y.foo and blah <> 'cheese' where -ome_other_complicated_join_conditions

...you can have

   with nerk as (
     select something from x join y on x.foo = y.foo
     where
       hopefully you can find some common part of the complicated where clauses
   )
   select something from nerk where blah = 'cheese' and something
   union
   select something from nerk where blah <> 'cheese' and something_else

...obviously a trivial example but you get the idea. When I tested this on postgres (some time ago) this was very significantly faster for my use case.

It also helps in use cases where you want to allow user-defined filtering and ordering logic controlled by a gui. You can put your basic query as the CTE and then have all the user-controlled bits and pieces in the final select and they don't get mixed up together.

I do this as well and consider CTE's just a macro that is expanded when executing the query - similar to an inline view.

I've run into issues before with systems where the result of a CTE is so large that it is actually advantageous to instead place it into a temp table to avoid re-fetching the data at each invocation. Sometimes that can be better than a CTE for this use case - YMMV.

While the constraint was recently relaxed somewhat in postgres 11 or 12, it is worth noting that a cte can be an optimization barrier. This means that a cte isn't just a cookie cutter replace operation. Usually this is ok, especially if the cte result set is on the smaller side, but it's just something to keep in mind.

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

I'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.

100% agree.

There 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.

Reply on news.ycombinator.com