3 ms·

why isnt materialized CTE a default optimization in sql? why do we need to be explicit about it any trade off

simonw 6y ago
I think the changelog is a bit confusing on that point. I interpret it as meaning that CTE is a default optimization: "The default behavior was formerly NOT MATERIALIZED, but is now changed to MATERIALIZED for CTEs that are used more than once" The default was NOT MATERIALIZED only because previous versions of SQLite didn't have this feature at all. "now changed to MATERIALIZED for CTEs that are used more than once" means that the query planner DOES use this optimization by default if you don't do anything - the optimization only makes sense for CTEs that are used more than once in the same query.
- systems 6y ago
  I think the change introduced this syntax with history as materialized { .. } so you have to declare the CTE as materialized to get this optimization
  - simonw 6y ago
    That's not how I interpret the release notes, but I can see why they are being misunderstood like that (assuming I'm right in my interpretation).
  - masklinn 6y ago
    > so you have to declare the CTE as materialized to get this optimization Not necessarily. You can get a materialized CTE if you request it regardless of its usage pattern, but you will also get one if the CTE is used more than once unless you suppress it.
- masklinn 6y ago
  > The default was NOT MATERIALIZED only because previous versions of SQLite didn't have this feature at all. The default was NOT MATERIALIZED because it's what the sqlite devs had selected whe they implemented CTEs. Pg did the exact opposite before the feature was added (and exposed to end-users): CTEs would always be MATERIALIZED. And similarly, with the introduction of [NOT] MATERIALIZED they changed the default in some cases: for pg if the CTE is non-recursive, has no side-effects, and is only referenced once, then it defaults to NOT MATERIALIZED.

I think the changelog is a bit confusing on that point. I interpret it as meaning that CTE is a default optimization: "The default behavior was formerly NOT MATERIALIZED, but is now changed to MATERIALIZED for CTEs that are used more than once" The default was NOT MATERIALIZED only because previous versions of SQLite didn't have this feature at all. "now changed to MATERIALIZED for CTEs that are used more than once" means that the query planner DOES use this optimization by default if you don't do anything - the optimization only makes sense for CTEs that are used more than once in the same query.

systems 6y ago
I think the change introduced this syntax with history as materialized { .. } so you have to declare the CTE as materialized to get this optimization
- simonw 6y ago
  That's not how I interpret the release notes, but I can see why they are being misunderstood like that (assuming I'm right in my interpretation).
- masklinn 6y ago
  > so you have to declare the CTE as materialized to get this optimization Not necessarily. You can get a materialized CTE if you request it regardless of its usage pattern, but you will also get one if the CTE is used more than once unless you suppress it.
masklinn 6y ago
> The default was NOT MATERIALIZED only because previous versions of SQLite didn't have this feature at all. The default was NOT MATERIALIZED because it's what the sqlite devs had selected whe they implemented CTEs. Pg did the exact opposite before the feature was added (and exposed to end-users): CTEs would always be MATERIALIZED. And similarly, with the introduction of [NOT] MATERIALIZED they changed the default in some cases: for pg if the CTE is non-recursive, has no side-effects, and is only referenced once, then it defaults to NOT MATERIALIZED.