4 ms·

Make sure you understand what optimization fences are and how they affect your performance. CTEs are nice to read but routinely destroy the performance. [1] ht

by meritt 6y ago

Make sure you understand what optimization fences are and how they affect your performance. CTEs are nice to read but routinely destroy the performance. [1] https://thoughtbot.com/blog/advanced-postgres-performance-tips#common-table-expressions-and-subqueries https://thoughtbot.com/blog/advanced-postgres-performance-ti...

bjt 6y ago
As of Postgres 12 this has changed substantially. Instead of being materialized, CTEs are inlined and optimized with the rest of the query. Exceptions: 1. If the results of the CTE are used more than once then it is materialized by default, though you can override this by adding "NOT MATERIALIZED" to the call. 2. Recursive and INSERT/UPDATE/DELETE CTEs are always materialized. https://paquier.xyz/postgresql-2/postgres-12-with-materialize/ https://paquier.xyz/postgresql-2/postgres-12-with-materializ...
oarabbus_ 6y ago
>CTEs are nice to read but routinely destroy the performance. This may be true on archaic versions of MySQL and Postgres but is not the case today, barring some esoteric edge cases (bugs) where the optimizer gets thrown out of whack. Once while doing data science consulting I rewrote a ~1000 line query in Aurora (MySQL flavored) which had a ~2.5s runtime, which was far too slow for the client's use-case. After rewriting all the CTEs (there were many) into subqueries, there was a 2-3% increase in query speed, barely (on the order of under a tenth of a second). There was a very tiny improvement far smaller than the normal variance of the runtime. Then I rebuilt the query and the joins, and was able to get the query to consistently run in the range of 0.8 - 1.2s. For my own purposes I then duplicated the query, re-implemented the CTEs, and did validate that indeed there is only a negligible increase in query time when using CTE.

4 ms·

Make sure you understand what optimization fences are and how they affect your performance. CTEs are nice to read but routinely destroy the performance. [1] ht

by meritt 6y ago

bjt 6y ago
As of Postgres 12 this has changed substantially. Instead of being materialized, CTEs are inlined and optimized with the rest of the query. Exceptions: 1. If the results of the CTE are used more than once then it is materialized by default, though you can override this by adding "NOT MATERIALIZED" to the call. 2. Recursive and INSERT/UPDATE/DELETE CTEs are always materialized. https://paquier.xyz/postgresql-2/postgres-12-with-materialize/ https://paquier.xyz/postgresql-2/postgres-12-with-materializ...
oarabbus_ 6y ago
>CTEs are nice to read but routinely destroy the performance. This may be true on archaic versions of MySQL and Postgres but is not the case today, barring some esoteric edge cases (bugs) where the optimizer gets thrown out of whack. Once while doing data science consulting I rewrote a ~1000 line query in Aurora (MySQL flavored) which had a ~2.5s runtime, which was far too slow for the client's use-case. After rewriting all the CTEs (there were many) into subqueries, there was a 2-3% increase in query speed, barely (on the order of under a tenth of a second). There was a very tiny improvement far smaller than the normal variance of the runtime. Then I rebuilt the query and the joins, and was able to get the query to consistently run in the range of 0.8 - 1.2s. For my own purposes I then duplicated the query, re-implemented the CTEs, and did validate that indeed there is only a negligible increase in query time when using CTE.