4 ms·

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

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.

srcreigh 4y ago
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
- warmwaffles 4y ago
  How would this look with a sample query? (using AS NIT MATERIALIZED)
  - jdmichal 4y ago
    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.
    - warmwaffles 4y ago
      This is incredibly helpful. I did not know this was a possibility.
trimethylpurine 4y ago
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.

srcreigh 4y ago

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

warmwaffles 4y ago
How would this look with a sample query? (using AS NIT MATERIALIZED)
- jdmichal 4y ago
  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.
  - warmwaffles 4y ago
    This is incredibly helpful. I did not know this was a possibility.

trimethylpurine 4y ago

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.