3 ms·
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 op
by adamzegelin 11y ago
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.
- netcraft 11y agoCTE'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.
- keeperofdakeys 11y agoI'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.
- anonetal 11y agoThis is an old discussion on the dev list: http://www.postgresql.org/message-id/201209191305.44674.db@kavod.com 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).
- losvedir 11y agoI'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".
- NickNameNick 11y agoI've used them to replace multiple similar sub-selects to great performance advantage. That was a very awkward query though.
- brusch 11y agoI'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.