re: SQL 201: 10 Ways to Tweak Slow Running Queries VIEW POST

re: 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 recommend...

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.

Read more about the why with examples:

code of conduct - report abuse