Hacker News Reader

Comment by adamzegelin | Hacker News Reader

Why is everyone pushing CTE's for "readability" for Postgres queries? I've seen this come up multiple times in the past few months.

CTE's (WITH clauses) are optimisation barriers — the query planner in Postgres won't inline CTEs, but instead will materialise their result into a temporary table first, preventing qual (WHERE clause) push-down and other optimisations.

I know this because it bit me, hard — I went nuts and wrote a lot of the queries in a new system to use WITH clauses, for readability. Performance was atrocious, and required refactoring to inline all the WITH clauses as sub-SELECTS.

CTE's are useful, but should not be used to improve readability.

View on HN

CTE's should be used to improve readability, they shouldn't be used if performance is a concern. There are many queries where the difference will be negligible if even noticeable. We should write code for humans first.

That being said, does anyone know if there are any plans to address them being optimisation barriers, or what the challenges are? Seems like even a naive rewrite into subselects before even creating the plan would reap a lot of benefits.

This is an old discussion on the dev list: http://www.postgresql.org/message-id/201209191305.44674.db@k...

.. which indicates that reliance on them being optimization fences may be too ingrained to change. I had assumed that they were treated as sub-selects (given the generally advanced optimizer that PostgreSQL has), and was quite surprised to realize that they aren't. I think this is a major shortcoming of PostgreSQL, especially given the readability advantages of CTEs makes them quite attractive to use (I usually recommend heavy use of CTEs to beginning SQL-ers).

I'm pretty sure that the optimisation barrier is a 'feature'. This can actually be handy, especially when replacing a complex subquery that the query planner turns into a nested loop.

I've used a CTE before specifically for its optimisation barrier. The query planner wasn't generating the query I knew would be faster, so I re-jiggered it a bit with a CTE and it dropped from 100ms to 1ms, as I expected. I know there's ways to fiddle with the query planner but sometimes it's just easier to tell postgres: "No, do it this way".

I've used CTE's in DB2 for hierarchical data using recursive SQL (8 ? years ago) and the performace was much better than using subselects.

I don't know much about Postgres, but for some things CTEs are a good tool.

I've used them to replace multiple similar sub-selects to great performance advantage. That was a very awkward query though.

Reply on news.ycombinator.com