6 ms·
Heed the caution about CTEs being optimization fences. I had a case just this past week, where one of our engineers needed help with a query that used a LEFT JO
by rosser 7y ago
Heed the caution about CTEs being optimization fences. I had a case just this past week, where one of our engineers needed help with a query that used a LEFT JOIN against a CTE which generated text-search vectors for some subset of rows, in some large table. The query was taking 9-10 minutes, cache-warmed.
Used that way — an outer join against a CTE — the planner was forced to generate a plan that produced a row for every candidate row in the table being used as input for the text-search, whether or not it would be needed, visiting nearly all of its pages.
Just by moving the CTE inline (that is: "LEFT JOIN ( #{ subquery } ) AS blah"), the query completed in 17ms.
This way, the planner could apply conditions from the rest of the query to the subplan from which text-search vector rows were being produced, such that it only pages containing rows it already knew it would care about would even be retrieved.
The rest of this article is pretty on-point, too.
Source: This stuff is my day job.
EDIT: As a counter-point, because they are incredibly useful, I've also had countless cases where rewriting a query to use CTEs was the several orders of magnitude win. This also wasn't the only possible fix; the qualifying conditions in the CTE could have been improved, eliminating the extra work where it would have occurred.
Like all things computers, the real answer is, "it depends..."
- war1025 7y agoI think I read somewhere that they are fixing some of optimization issues for CTEs in the next release of Postgres. Does anyone know whether that's true or not?
- rosser 7y agoAre you thinking of the "[NOT] MATERIALIZED" clause? Yes, that's coming. https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=608b167f9f9c4553c35bb1ec0eab9ddae643989b https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit...
- war1025 7y agoThanks for the info! I am a big fan of the readability of CTEs. Though I guess in production code we use SqlAlchemy, so it would just be a matter of swapping `.cte()` for `.subquery()` probably...
- ellisv 7y agoIIRC Postgres 12 is suppose to remove most of the optimization fences around CTEs.
- ggm 7y agoLike all things computers, the real answer is, "it depends..." This is what makes the job exist. If it was algorithmic we'd already have been replaced by EXPLAIN
- Timucin 7y agoEach of the ~5000 stored procedures I had to strip out from a client’s large MySQL cluster agrees with this statement.
- emilsedgh 7y agoOn Postgres 12, CTE's are not optimization fences anymore. [0] [0] https://www.depesz.com/2019/02/19/waiting-for-postgresql-12-allow-user-control-of-cte-materialization-and-change-the-default-behavior/ https://www.depesz.com/2019/02/19/waiting-for-postgresql-12-...
- rosser 7y agoNot quite true. The default behavior changes, and new syntax is added allowing users to specify their desired behavior, but if the sub-query in the CTE does certain things, it will still be materialized instead, regardless. That is: they mostly aren't a fence any more, but can be when you want them to, and sometimes still must be.
- drunkpotato 7y agoQuery planners are like compilers: sophisticated and sometimes surprising but not perfect. Writing sql is just like writing any other code: optimize for an easy to understand query first, performance if needed (and always comment your performance optimizations)! They are also like just-in-time hotspot optimizations: the query planner keeps statistics and the second run of a query can be 10x or more faster than the first run. (Side note: most analytics queries in my experience are run against cold data so it’s usually worth optimizing for first-query performance.) This stuff is also my day job, and fun!