Very concise and useful tips! I use CTEs all the time for readability and didn't realize it could be impacting performance. Do you have a recommendation for where you learned the "why" behind these tips? A little context/background can help make things like this a bit easier to remember.
I like using CTEs to make the query easier to read but it was pointed out to me when writing my article on CTEs (from a SQL Server point of view) that these can
be an optimisation fence in Postgres.
It's worth noting that in PostgreSQL a CTE is an "optimiser fence", in that the query that is contains is executed without further optimisation. So if you apply a further predicate to the CTE result in another CTE or the main query, this predicate isn't pushed down to the original CTE.
On the other hand, in PostgreSQL a CTE can execute an insert, update or delete, and return values from the affected rows for use later on in the same query, which is very powerful.
Very good to know, thanks for the links! :)
We're a place where coders share, stay up-to-date and grow their careers.
We strive for transparency and don't collect excess data.