Hacker News Reader

Comment by netcraft | Hacker News Reader

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.

View on HN

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

Reply on news.ycombinator.com