Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

CTEs as lookup tables

misfra.me

Post reply on HN
Print view View original

71–80 of 115 posts

Re: CTEs as lookup tables

#71

Post by recursive »

TheCleric wrote: post #66
cfeduke wrote: post #13

CTEs (common table expressions) are wonderful, they make SQL grokkable and maintainable. But, before some dev goes crazy refactoring all of the SQL in their codebase into maintainable CTEs: always benchmark. CTEs can cause your query planner to optimize incorrectly. In some cases, CTEs can force the query optimizer to choose a plan it otherwise is not choosing and be more performant - perhaps up to a certain point. (…

100% true. I wrote some very elegant, readable SQL to perform a complex query. It was dog slow. I handed it to a DBA, they ripped out the CTEs and replaced them all with temp tables. The query was an unreadable mess at the end, but boy was it orders of magnitude faster.

Behold, the declarative 4th generational language.

Re: CTEs as lookup tables

#72

Post by masklinn »

garfij wrote: post #40
jbrwn wrote: post #14

Earlier quoted context omitted.

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

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

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

#73

Post by srcreigh »

jvolkman wrote: post #24
cfeduke wrote: post #13

CTEs (common table expressions) are wonderful, they make SQL grokkable and maintainable. But, before some dev goes crazy refactoring all of the SQL in their codebase into maintainable CTEs: always benchmark. CTEs can cause your query planner to optimize incorrectly. In some cases, CTEs can force the query optimizer to choose a plan it otherwise is not choosing and be more performant - perhaps up to a certain point. (…

CTEs used to be "optimization fences" in PostgreSQL, but that changed with v12. https://www.depesz.com/2019/02/19/waiting-for-postgresql-12-...

They still are sometimes. Whenever you have a CTE which is referenced more than once, to be specific.

Re: CTEs as lookup tables

#74

Post by rkagerer »

emodendroket wrote: post #59
hobs wrote: post #22

Earlier quoted context omitted.

In my experience a huge portion of the userbase who loves CTEs are analysts or devs running queries on decently large datasets, and they mostly like them because of readability and don't understand performance possibilities. I appreciate that tools like dbt allow materialization options as both CTEs and views/tables/etc because being able to pivot between them is super nice.

Readability over optimization is often a reasonable compromise, don't you think?

So could an optimizer be built that let's you have your cake and eat it too? CTE syntax but without the performance compromise?

Re: CTEs as lookup tables

#75

Post by srcreigh »

epgui wrote: post #41
hobs wrote: post #22

Earlier quoted context omitted.

In my experience a huge portion of the userbase who loves CTEs are analysts or devs running queries on decently large datasets, and they mostly like them because of readability and don't understand performance possibilities. I appreciate that tools like dbt allow materialization options as both CTEs and views/tables/etc because being able to pivot between them is super nice.

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

Re: CTEs as lookup tables

#76

Post by srcreigh »

hobs wrote: post #22
bob1029 wrote: post #15

Earlier quoted context omitted.

I have strongly encouraged reckless use of CTEs throughout our product. We use in-memory instances of SQLite to evaluate any queries which would leverage CTEs. These datasets are usually small enough to reside within L1 (and certainly within L2).

In my experience a huge portion of the userbase who loves CTEs are analysts or devs running queries on decently large datasets, and they mostly like them because of readability and don't understand performance possibilities. I appreciate that tools like dbt allow materialization options as both CTEs and views/tables/etc because being able to pivot between them is super nice.

Postgres has materialization options for CTEs. You can ask it to rewrite the CTE as a join via "WITH foobar AS NOT MATERIALIZED (...)". Alternatively you can force materialization by writing "WITH foobar AS MATERIALIZED (...)"

Re: CTEs as lookup tables

#77

Post by ttfkam »

zaroth wrote: post #68
ttfkam wrote: post #65

Earlier quoted context omitted.

That's because Redshift doesn't support MATERIALIZED CTEs. In mainline Postgres, a MATERIALIZED CTE acts more like a temporary table with the performance characteristics you're looking for.

Except those won’t have any indexes on them, so any joins will be horrifically slow?

Are you putting indexes on your temporary tables? Are you including the time it takes to create them?

I can't speak for others, but most of my CTEs tend to be small enough where an index wouldn't help much. Of course toggling MATERIALIZED is an easy way to see whether the equivalent of a temp table would work best or if the planner can successfully include the body of CTE in optimization. A lot harder syntactically to swap out a subquery for a temp table, which is what the MATERIALIZED keyword essentially does under the hood (at least in Postgres).

Re: CTEs as lookup tables

#78

Post by emodendroket »

rkagerer wrote: post #74
emodendroket wrote: post #59

Earlier quoted context omitted.

Readability over optimization is often a reasonable compromise, don't you think?

So could an optimizer be built that let's you have your cake and eat it too? CTE syntax but without the performance compromise?

Based on other comments, this has been done for psql, or at least to some extent. So that's the other thing... pre-emptively writing code in a non-intuitive way for performance reasons often leads to practices sticking around long past the rationale for them even being true.

Re: CTEs as lookup tables

#79

Post by TylerE »

ttfkam wrote: post #77
zaroth wrote: post #68

Earlier quoted context omitted.

Except those won’t have any indexes on them, so any joins will be horrifically slow?

Are you putting indexes on your temporary tables? Are you including the time it takes to create them? I can't speak for others, but most of my CTEs tend to be small enough where an index wouldn't help much. Of course toggling MATERIALIZED is an easy way to see whether the equivalent of a temp table would work best or if the planner can successfully include the body of CTE in optimization. A lot harder syntactically t…

Hell, on small tables Postgres often won’t do an index scan even when one exists because it’s faster to do a linear scan and not an index read + fetchs - or at least the planner thinks so.

Re: CTEs as lookup tables

#80

Post by TylerE »

acjohnson55 wrote: post #55
reactivenz wrote: post #39

Earlier quoted context omitted.

those UNION's should be UNION ALL otherwise they are deduplicated. Thus you code is worse, also the VALUES express is nicer when done in longer form WITH my_cte AS ( SELECT \* FROM VALUES (1, 'column 2 value', 3.0), (2, 'column 2 value', 3.0), (3, 'column 2 value', 3.0), (4, 'column 2 value', 3.0) ) you can often alias the VALUES values like: WITH my_cte AS ( SELECT \* FROM VALUES (1, 'column 2 value', 3.0), (2, 'col…

In the last example, it seems like it would be nice for the DB to let you omit the `SELECT * FROM` part.

In any non trivial code select * is a code smell anyway. Thus parsers don’t spend cycles looking for forms that are unlikely to be useful in real code.
Post reply on HN