3 ms·

CTEs can also perform very poorly and often in surprising ways. For example, predicate pushdown is a problem on both MSSQL and Postgresql.

by jbrwn 4y ago

garfij 4y ago
My understanding was that Postgres fixed this back in version 12. Are there still limitations here?
- epgui 4y ago
  Nope, there’s no performance downside to using CTEs in recent postgres versions, unless the CTE is recursive or has side effects (which would be weird).
- masklinn 4y ago
  Various comments above expand upon it, but pg12 only changed CTEs which are referenced once to default to NOT MATERIALIZED. Multi-referenced CTEs remain materialized by default. Also not-mat CTEs can perform a lot worse: https://stackoverflow.com/questions/64016236/postgres-12-materialized-cte-much-faster https://stackoverflow.com/questions/64016236/postgres-12-mat... But so can MAT CTEs: https://dba.stackexchange.com/questions/257014/are-there-side-effects-to-postgres-12s-not-materialized-directive https://dba.stackexchange.com/questions/257014/are-there-sid... So the limitations are that it’s very much ymmv.
no-s 4y ago
> predicate pushdown is a problem on both MSSQL and Postgresql Not sure you’re completely on target here regarding CTE performance. I don’t have deep insight into Postgresql (but “create temporary view", yeah!). MSSQL does a pretty good job in Sql2019. If the predicate is sargeable in some way pushdown is reliable. If performance is a concern, examining the residuals can lead to significant insights e.g. applying index filtering which solves obvious problems. Recursion is another story. CTE is more likely used by data analyst queries because it is an abstraction of composition. It’s not a great abstraction but it’s better than nothing, which is mostly what you get with SQL.
fphhotchips 4y ago
Apologies I'm late to reply to this, but yes. The way each compiler/optimizer handles CTEs can be dramatically different from RDBMS to RDBMS. For this specific use case though, my comment was made because CASE statements tend to just be bad in comparison to CTEs. Probably dependent on engine though, I don't know them all by heart.

CTEs can also perform very poorly and often in surprising ways. For example, predicate pushdown is a problem on both MSSQL and Postgresql.

by jbrwn 4y ago

CTEs can also perform very poorly and often in surprising ways. For example, predicate pushdown is a problem on both MSSQL and Postgresql.

garfij 4y ago

My understanding was that Postgres fixed this back in version 12. Are there still limitations here?

epgui 4y ago
Nope, there’s no performance downside to using CTEs in recent postgres versions, unless the CTE is recursive or has side effects (which would be weird).
masklinn 4y ago
Various comments above expand upon it, but pg12 only changed CTEs which are referenced once to default to NOT MATERIALIZED. Multi-referenced CTEs remain materialized by default. Also not-mat CTEs can perform a lot worse: https://stackoverflow.com/questions/64016236/postgres-12-materialized-cte-much-faster https://stackoverflow.com/questions/64016236/postgres-12-mat... But so can MAT CTEs: https://dba.stackexchange.com/questions/257014/are-there-side-effects-to-postgres-12s-not-materialized-directive https://dba.stackexchange.com/questions/257014/are-there-sid... So the limitations are that it’s very much ymmv.

no-s 4y ago

> predicate pushdown is a problem on both MSSQL and Postgresql Not sure you’re completely on target here regarding CTE performance. I don’t have deep insight into Postgresql (but “create temporary view", yeah!). MSSQL does a pretty good job in Sql2019. If the predicate is sargeable in some way pushdown is reliable. If performance is a concern, examining the residuals can lead to significant insights e.g. applying index filtering which solves obvious problems. Recursion is another story. CTE is more likely used by data analyst queries because it is an abstraction of composition. It’s not a great abstraction but it’s better than nothing, which is mostly what you get with SQL.

fphhotchips 4y ago

Apologies I'm late to reply to this, but yes. The way each compiler/optimizer handles CTEs can be dramatically different from RDBMS to RDBMS. For this specific use case though, my comment was made because CASE statements tend to just be bad in comparison to CTEs. Probably dependent on engine though, I don't know them all by heart.