DEV Community

[Comment from a deleted post]
Collapse
 
chambliss profile image
Charlene Chambliss

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.

Collapse
 
helenanders26 profile image
Helen Anderson

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:

Collapse
 
chambliss profile image
Charlene Chambliss

Very good to know, thanks for the links! :)