4 ms·

What is the impact of the inline CTEs? I've found CTEs very useful for avoiding multi round trips for related modifications, although this is limited.

by Dowwie 7y ago

ohlookabird 7y ago
I suppose it is much more common that CTEs are used to make queries more readable (I certainly do!). If this is the case, I feel making the common case the default behavior (inlining) is preferable, which is what PG12 did. It also is less surprising behavior in my opinion. The old behavior can be enforced using the MATERIALIZED keyword with the CTE. However, I agree that this will make me go through every CTE I use and make sure there is no MATERIALIZED keyword missing. And I already know quite a few where I, like you, use CTEs to prevent recomputation and promote reuse. I will likely add the MATERIALIZED keyword there for clarity, but inlining will only happen if the CTE isn't used multiple times anyway (they will also not be inline if they have side-effects or are recursive).
- pgaddict 7y ago
  Yeah, there was a lengthy discussion about what the default behavior should be during development. Ultimately, inline by default prevailed, and I think it was the right decision. Inlining seems like the right choice for vast majority of cases, but yeah - there are cases where enforcing materialization is a win. Once you try this on your application, it'd be interesting to share some stats about the queries / CTEs that benefited from materialization. That might be quite useful for improving the "materialize vs. inline" logic in future releases.
  - ohlookabird 7y ago
    That's a good suggestion. I try to gather some numbers and post to the mailing list.
pgaddict 7y ago
That very much depends on the CTE. The release notes say this: > CTEs are automatically inlined if they have no side-effects, are not recursive, and are referenced only once in the query. Inlining can be prevented by specifying MATERIALIZED, or forced for multiply-referenced CTEs by specifying NOT MATERIALIZED. Previously, CTEs were never inlined and were always evaluated before the rest of the query. So if you have a CTE with data modifications (e.g. no DELETE/INSERT/UPDATE), then that will not be inlined. The problem the inlining is trying to solve is that CTEs block optimizations. Consider an example like this: CREATE TABLE t (id SERIAL PRIMARY KEY, ...); WITH x AS (SELECT * FROM t) SELECT * FROM x WHERE id = 1000; Without the inlining, the WITH serves as "optimization fence" which means PostgreSQL won't be able to use the index on the ID column. The database essentially evaluates the CTE, stashes the results somewhere (in a work_mem buffer / temp file) and then queries this intermediate result. There are historical reasons why it was originally implemented like this, but the trouble is, people often don't realize this and use it just as a nice way to name subqueries. So they treat WITH x AS (SELECT * FROM t) SELECT * FROM x WHERE id = 1000; as semantically the same as SELECT * FROM (SELECT * FROM t) foo WHERE id = 1000; but it was executed quite differently. So for people using CTEs like this, the automatic inlining by default is likely a huge improvement. There may be cases of regression, but that's why it's possible to enforce materialization by using MATERIALIZED.
dspillett 7y ago
> What is the impact of the inline CTEs? It depends greatly on the size of the data and the nature of the query inside the CTE. Sometimes it'll make no difference at all, of course. Sometimes it could mean the difference between scanning an entire table and being able to perform index seeks. As the result is materialised in earlier versions this could mean a fair chunk of write IO as well as read IO if the result is too large to just keep in RAM. Even worse for recursive CTEs the process could be repeated at every level of the recursive depth. Of course the query planner will still need to do that for bad queries or queries against structures that need better indexing, and there may be many situations out there where a better query/schema wasn't created because before v12 it would make no difference anyway so some rewriting is needed to take advantage of the new optimisation potential. If the innermost CTE contains filters that significantly limit the data and there are sufficient indexes to support that filtering, or if the data is small to start with, then this change will likely make little or no difference.

What is the impact of the inline CTEs? I've found CTEs very useful for avoiding multi round trips for related modifications, although this is limited.

by Dowwie 7y ago

What is the impact of the inline CTEs? I've found CTEs very useful for avoiding multi round trips for related modifications, although this is limited.

ohlookabird 7y ago

I suppose it is much more common that CTEs are used to make queries more readable (I certainly do!). If this is the case, I feel making the common case the default behavior (inlining) is preferable, which is what PG12 did. It also is less surprising behavior in my opinion. The old behavior can be enforced using the MATERIALIZED keyword with the CTE. However, I agree that this will make me go through every CTE I use and make sure there is no MATERIALIZED keyword missing. And I already know quite a few where I, like you, use CTEs to prevent recomputation and promote reuse. I will likely add the MATERIALIZED keyword there for clarity, but inlining will only happen if the CTE isn't used multiple times anyway (they will also not be inline if they have side-effects or are recursive).

pgaddict 7y ago
Yeah, there was a lengthy discussion about what the default behavior should be during development. Ultimately, inline by default prevailed, and I think it was the right decision. Inlining seems like the right choice for vast majority of cases, but yeah - there are cases where enforcing materialization is a win. Once you try this on your application, it'd be interesting to share some stats about the queries / CTEs that benefited from materialization. That might be quite useful for improving the "materialize vs. inline" logic in future releases.
- ohlookabird 7y ago
  That's a good suggestion. I try to gather some numbers and post to the mailing list.

pgaddict 7y ago

That very much depends on the CTE. The release notes say this: > CTEs are automatically inlined if they have no side-effects, are not recursive, and are referenced only once in the query. Inlining can be prevented by specifying MATERIALIZED, or forced for multiply-referenced CTEs by specifying NOT MATERIALIZED. Previously, CTEs were never inlined and were always evaluated before the rest of the query. So if you have a CTE with data modifications (e.g. no DELETE/INSERT/UPDATE), then that will not be inlined. The problem the inlining is trying to solve is that CTEs block optimizations. Consider an example like this: CREATE TABLE t (id SERIAL PRIMARY KEY, ...); WITH x AS (SELECT * FROM t) SELECT * FROM x WHERE id = 1000; Without the inlining, the WITH serves as "optimization fence" which means PostgreSQL won't be able to use the index on the ID column. The database essentially evaluates the CTE, stashes the results somewhere (in a work_mem buffer / temp file) and then queries this intermediate result. There are historical reasons why it was originally implemented like this, but the trouble is, people often don't realize this and use it just as a nice way to name subqueries. So they treat WITH x AS (SELECT * FROM t) SELECT * FROM x WHERE id = 1000; as semantically the same as SELECT * FROM (SELECT * FROM t) foo WHERE id = 1000; but it was executed quite differently. So for people using CTEs like this, the automatic inlining by default is likely a huge improvement. There may be cases of regression, but that's why it's possible to enforce materialization by using MATERIALIZED.

dspillett 7y ago

> What is the impact of the inline CTEs? It depends greatly on the size of the data and the nature of the query inside the CTE. Sometimes it'll make no difference at all, of course. Sometimes it could mean the difference between scanning an entire table and being able to perform index seeks. As the result is materialised in earlier versions this could mean a fair chunk of write IO as well as read IO if the result is too large to just keep in RAM. Even worse for recursive CTEs the process could be repeated at every level of the recursive depth. Of course the query planner will still need to do that for bad queries or queries against structures that need better indexing, and there may be many situations out there where a better query/schema wasn't created because before v12 it would make no difference anyway so some rewriting is needed to take advantage of the new optimisation potential. If the innermost CTE contains filters that significantly limit the data and there are sufficient indexes to support that filtering, or if the data is small to start with, then this change will likely make little or no difference.