Fun fact, this can also be more performant, depending on the engine.
When is it more performant?
CTEs as lookup tables
101–110 of 115 posts
Re: CTEs as lookup tables
#102Removing CTEs from your codebase by replacing them with the creation of a temp table and then using separate queries with as few joins as possible to populate it will give you low-locking performance hundreds of times better nearly every time. No problem with readability.
But careful! In the concurrent environment, separate queries may need a higher transaction isolation level to mean the same thing as one query.
Re: CTEs as lookup tables
#103Earlier quoted context omitted.
Note that there are basically no special performance considerations when using CTEs in recent versions of postgres… unless your CTE is recursive or does some weird side-effect (which is unlikely).
This is incorrect. If you use a CTE more than once in a query, Postgres will materialize that data into a temp table that has no indexes. If that CTE gets joined a lot, it will incur many O(N) searched through unindexed data. I've seen cases where a few GBs of product data gets pulled into a CTE this way. Queries take 10s to complete from processing GBs of unindexed data
Is there a way to force the temp table to have indices within the CTE definition?
Re: CTEs as lookup tables
#104Earlier quoted context omitted.
Note that there are basically no special performance considerations when using CTEs in recent versions of postgres… unless your CTE is recursive or does some weird side-effect (which is unlikely).
This is incorrect. If you use a CTE more than once in a query, Postgres will materialize that data into a temp table that has no indexes. If that CTE gets joined a lot, it will incur many O(N) searched through unindexed data. I've seen cases where a few GBs of product data gets pulled into a CTE this way. Queries take 10s to complete from processing GBs of unindexed data
Wow, this is amazing. Just the other day I was staring at a query with multiple CTEs that took 20+ seconds to run. Ended up adding an additional WHERE clause on an indexed column to the slowest CTE, which took the execution time down to 100s of ms.
Just tried the original query, but with explicitly not materialized CTEs and the runtime was basically as fast as the manually pre-filtered solution.
Re: CTEs as lookup tables
#105Earlier quoted context omitted.
The trick is to change the name of the CTE in your final select. This will allow you to inspect that particular step. This works as long as your CTE’s are correct SQL and only the logic is wrong or suspect.
I guess the point is you could just stick the results of each step that is currently a CTE into #table as separate queries, and you're only doing the work once to step through the stages? From a debug point of view that feels more convenient to _me_.
I guess it depends, materialising all the steps might be slow and you might need to do a refactor to get a CTE-based query. If you want just want to check a few (maybe changing) records because you expect problems there a final select with an appropriate where is probably faster.
Edit: also I don’t like to leave a mess behind and CTE’s don’t require cleaning up afterwards.
Re: CTEs as lookup tables
#106Earlier quoted context omitted.
I'm also curious how turning some of those into views would compare, but this is also probably dependent on which database.
I'd say it would be almost as bad. In SQL you're describing the result that you want -- you aren't (and shouldn't be) saying how to achieve that result. The engine goes off and (hopefully) finds the optimal way to create the result you are describing. When you break queries down too far, the engine has more trouble finding the optimal path. SQL Server can "see through" views and CTEs optimize them combined with a que…
If writing CTEs is breaking the optimization I’d argue that’s also an example of not really meeting the “declarative” aspect of the design fully.
Re: CTEs as lookup tables
#107Earlier quoted context omitted.
I'd say it would be almost as bad. In SQL you're describing the result that you want -- you aren't (and shouldn't be) saying how to achieve that result. The engine goes off and (hopefully) finds the optimal way to create the result you are describing. When you break queries down too far, the engine has more trouble finding the optimal path. SQL Server can "see through" views and CTEs optimize them combined with a que…
If writing CTEs is breaking the optimization I’d argue that’s also an example of not really meeting the “declarative” aspect of the design fully.
I agree but all systems have limitations and SQL engines get better at the job every year.
The result of the query is say declaration X. In this case, imagine you can construct a query to formulate X with out a CTE. The database goes off and computes X from the data.
But if you start adding CTE's for say readability or because you're building up the query piece by piece and testing it in stages. You might be adding additional declarations A, B, and C and combining them to ultimately get X. In theory, the database engine should be able to see that you're still trying to get X and perform the query that same way as if it was a single query without a CTE. And, in some cases, it can actually do that.
But if A, B, and C are complicated enough then it's just unable to determine that it all equals X in the end. This is a fairly understandable limitation of the engine -- SQL can get pretty complicated. It's also making the assumption that all these CTE's are actually needed to compute the result. It took me a whole day to convert a CTE based query into a non-CTE based query and get all the same results in the same way.
Re: CTEs as lookup tables
#108Earlier quoted context omitted.
This is incorrect. If you use a CTE more than once in a query, Postgres will materialize that data into a temp table that has no indexes. If that CTE gets joined a lot, it will incur many O(N) searched through unindexed data. I've seen cases where a few GBs of product data gets pulled into a CTE this way. Queries take 10s to complete from processing GBs of unindexed data
Is there a way to force the temp table to have indices within the CTE definition?
No, but this would make an incredible Postgres extension.
(I’d also love one that lets Postgres use a covering index for primary storage. This can reduce data size and load less data off disk due to locality. MySQL has this by default and SQLite has it via WITHOUT ROWID tables.)
Re: CTEs as lookup tables
#109This is a great idea if you disable materialization (which the author of this post does not mention). Yes, materialization is fine for small hardcoded values in the post, but for most other lookup tables, it's definitely not fine. For example, you read this article and think great, I'll make a lookup table to map various ids to be linked across tables. with ids as ( select u.user_id, u.token, s.customer_id from users…
side note: I asked chatgpt to rewrite this query to use subqueries and it did a reasonable job. next step: include cte-to-subquery translation step as a part of your build pipeline. never needed tools like that but i guess they must exist since it's been such a common issue. using gpt for this is like nuking a mosquito from orbit.
AS NOT MATERIALIZED is equivalent to using sub queries
Re: CTEs as lookup tables
#110Earlier quoted context omitted.
This is incorrect. If you use a CTE more than once in a query, Postgres will materialize that data into a temp table that has no indexes. If that CTE gets joined a lot, it will incur many O(N) searched through unindexed data. I've seen cases where a few GBs of product data gets pulled into a CTE this way. Queries take 10s to complete from processing GBs of unindexed data
Wow, this is amazing. Just the other day I was staring at a query with multiple CTEs that took 20+ seconds to run. Ended up adding an additional WHERE clause on an indexed column to the slowest CTE, which took the execution time down to 100s of ms. Just tried the original query, but with explicitly not materialized CTEs and the runtime was basically as fast as the manually pre-filtered solution.
Not materializing lets Postgres push extra where clauses and join conditions into the CTE so this makes sense!
Now you know why I spam all the “CTEs are amazing” threads about materialization (there have been 5 or so in the last few months)