Hacker News Reader

Comment by simonw | Hacker News Reader

I find CTEs (the WITH statement) greatly improved my ability to run more complex queries because they offer an abstraction I can use to name and compose queries together.

SQL views are useful for that kind of thing too.

View on HN

The only issue I see with CTE's and have encountered, is poor query performance. The query planner needs to improve in this regard. At one point I noticed some of my CTE with queries being written to a temp file on disk and used to join against, when an inlined query did not do this producing the same results.

In Postgres you can use AS NIT MATERIALIZED to avoid the temp buffers. A recent PG version also makes this default, but only when the CTE is only queried once later.

This can definitely cause issues. I’ve seen queries with CTEs that basically process unindexed 100 MB data due to querying the materialized rows.

It would be cool to be able generate indexes with the CTEs though

How would this look with a sample query? (using AS NIT MATERIALIZED)

    WITH one AS NOT MATERIALIZED (
      SELECT 1 AS value, 'one' AS name
    )
    SELECT *
    FROM one

As srcreigh said, this is the default since I believe PostgreSQL 12, as long as the CTE is only used a single time. If you use it more than once, it by default wants to materialize it instead of recalculating each time. You can also force materialization by dropping the `NOT`.

Also note that materialization acts as an optimization fence. That is, PostgreSQL will not push down filter criteria and such from the query into the `WITH` clause. It can do such if it's not materialized.

This is incredibly helpful. I did not know this was a possibility.

What you're saying is analogous to "My horse won't run when I tie its legs together." You'll see what I mean here and hopefully it helps you.

Not sure which engine you're using, but for MSSQL at least, the engine writes stuff to disk where it can index it so that it can avoid table scans on retrieval. That is ideal where you have big joins or large tables. And is generally good for most use cases.

When you use CTE's you're telling the engine, "I want this in memory, don't do any indexing." That's awesome for write heavy workloads. But if you plan on joining the results of the CTE later, you will find that you can't read from it very quickly because for every row joined you must scan the entire table.

Now you'd think that since it's also forcing the data to stay in memory (if there's enough) that this would be faster. But that's not true. There's compute power at work to make comparisons on each row. The point of indexing is to reduce the required compute power by reducing the number of rows that you need to compare.

With that in mind, it's obvious that it kills performance if you just told your engine to not use indexes but do a big join on a large set of data.

Temporary tables are a better choice for read heavy workload compared with CTEs. MSSQL will index temp tables automatically. And in very rare cases where the automatic indexes aren't performant, then you can index them manually with a few extra keywords.

Happy querying.

Right, but can you 1) store CTEs and 2) pass arguments in to CTEs, such that the same basic 'code' can be shared between multiple different queries?

I'm a principal engineer who is relatively new with SQL (just learned it 5 years ago, using it for a side project, not my daily work) and almost completely self-taught; and the biggest problem I have with SQL is that I find it nearly impossible to do even basic levels of DRY in a way that's not ugly.

I believe what you're looking for is (in PostgreSQL) called a set returning function.

The problem at least with MSSQL is that these Table Valued Functions or User Defined Functions (scalar) are definitely not zero-cost abstractions.

Another issue is that the “SELECT” is also part of the function, which makes it even worse - so you can’t create just one function and reuse it all over the place. If only you could tell SQL that a certain JOIN always contains one row, or zero or one row (for a LEFT JOIN), then there would be more room for optimization.

I think that's subjective. You can index to optimize for whatever your function is doing.

I don't see any reason that the "select" limits reusability. If you define your function with relevant parameters you can create theoretically infinite reusability.

Yes, you can. Thats called a table valued function.

How to use a CTE with a group of several following statements? I think this is where you would use a view instead, but would appreciate confirmation.

For complex SELECT queries, I do:

WITH cte1 AS ( /* whatever / ), cte2 AS ( / you can refer to cte1 here / ), cte3 AS ( / you can refer to cte1 and cte2 here */ ), ...

If I need access into multiple statements that I can't refactor into a single one, explicitly using temporary tables is a viable option (some engines implement CTEs and other operations using implicit temporary tables anyway). Otherwise, yes, views.

Reply on news.ycombinator.com