News for hackers. Stuff that matters.
Re:CTEs as Lookup Tables by jbrwn on Wednesday January 11, 2023 @07:00PM (#34346940)
CTEs can also perform very poorly and often in surprising ways. For example, predicate pushdown is a problem on both MSSQL and Postgresql.
Re:CTEs as Lookup Tables by garfij on Wednesday January 11, 2023 @08:23PM (#34347803)
My understanding was that Postgres fixed this back in version 12. Are there still limitations here?
Re:CTEs as Lookup Tables by epgui on Wednesday January 11, 2023 @08:29PM (#34347855)
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).
Re:CTEs as Lookup Tables by masklinn on Thursday January 12, 2023 @01:00AM (#34349764)
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-mat...

But so can MAT CTEs: https://dba.stackexchange.com/questions/257014/are-there-sid...

So the limitations are that it’s very much ymmv.

Re:CTEs as Lookup Tables by no-s on Thursday January 12, 2023 @09:23AM (#34353876)
> 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.

Re:CTEs as Lookup Tables by fphhotchips on Friday January 13, 2023 @10:37PM (#34377170)
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.