3 ms·
I'd ding the over-use of CTEs, when subselects are often more appropriate and better-performing. Kind of a "every problem a nail" thing going on here.
by sk5t 6y ago
I'd ding the over-use of CTEs, when subselects are often more appropriate and better-performing. Kind of a "every problem a nail" thing going on here.
- combatentropy 6y agoDo you mean subselects in the SELECT clause or in the FROM? Usually subselects in the SELECT clause have been the source of dogslowness. Reorganizing them into the FROM clause sped up queries 1,000 times. And I thought CTEs were just a way of moving subselects from the midst of the FROM clause to the top of the statement, where they're easier to understand.
- sk5t 6y agoIn the FROM clause -- although I understand future versions of pgsql will address this, the "WITH foo AS ... " approach can be horribly wasteful, materializing all the results, whereas "SELECT * FROM ( SELECT f(x) AS a FROM baz ) qq limit 1" lets the optimizer do its thing.
- meritt 6y agoMost people have no idea what an optimization fence is and opt for CTEs because they yield "cleaner" queries, despite nearly always killing performance. I've always found explicit temporary tables, where I can add indexes, are often a great solution for performance and readability.
- matwood 6y agoI mostly agree. I'd start with the simplest possible query and move from there. I enjoy SQL, but once I start querying large datasets or doing anything remotely complex, the 'right' solution is the one that is fastest. And fast always depends. Typically I have to write up a solution a few different ways, test and tweak.
- eyelidlessness 6y agoAt least on recent versions of Postgres, CTEs are often equal in performance to subqueries (if they're side-effect free and non-recursive they get inlined).