Are there side effects to Postgres 12's NOT MATERIALIZED directive?

Question

I was doing some performance benchmarking on some of my company's SQL, comparing PG10 to PG12. We use a lot of CTEs in our code, and PG12 didn't natively optimize the CTEs, so the performance was the same between PG10 and PG12.

My next experiment was to add the NOT MATERIALIZED directive to the CTEs and the result was astounding: it cut query times dramatically (halved them in some cases).

I read here that MATERIALIZED was the default functionality prior to PG12. And that functionality would write all the contents of the CTE into a temporary location.

So my question is mainly around NOT MATERIALIZED:

1. What does the NOT MATERIALIZED functionality do with the data behind the scenes in contrast to MATERIALIZED?
2. Are there any side effects to NOT MATERIALIZED I should be aware of before refactoring our codebase?

Answer 1

It's explained very well in the documentation.

A useful property of WITH queries is that they are normally evaluated only once per execution of the parent query, even if they are referred to more than once by the parent query or sibling WITH queries. Thus, expensive calculations that are needed in multiple places can be placed within a WITH query to avoid redundant work. Another possible application is to prevent unwanted multiple evaluations of functions with side-effects.

So far, so good, BUT:

However, the other side of this coin is that the optimizer is not able to push restrictions from the parent query down into a multiply-referenced WITH query, since that might affect all uses of the WITH query's output when it should affect only one. The multiply-referenced WITH query will be evaluated as written, without suppression of rows that the parent query might discard afterwards.

So, as pointed out in the example given, if you have a query like this:

WITH w AS (
    SELECT * FROM big_table  -- big_table has an INDEX on a field called key!
)
SELECT * FROM w AS w1 
  JOIN w AS w2 ON w1.key = w2.ref  -- w is called TWICE, so DEFAULT is MATERIALIZED
                                   -- PostgreSQL can't take advantage of big_table.key
WHERE w2.key = 123;

So, in this case:

the WITH query will be materialized, producing a temporary copy of big_table that is > then joined with itself — without benefit of any index

Far better to have:

WITH w AS NOT MATERIALIZED (
    SELECT * FROM big_table
)
SELECT * FROM w AS w1 JOIN w AS w2 ON w1.key = w2.ref
WHERE w2.key = 123;

So that the optimizer can "fold" the CTE query "into" the main query and make use of the INDEX on the key field of big_table!

Re. the DEFAULT of NOT MATERIALIZED:

However, if a WITH query is non-recursive and side-effect-free (that is, it is a SELECT containing no volatile functions) then it can be folded into the parent query, allowing joint optimization of the two query levels. By default, this happens if the parent query references the WITH query just once, but not if it references the WITH query more than once.

So the DEFAULT is NOT MATERIALIZED if:

    the_query IS NOT recursive 
AND the_query is_side_effect_free 
AND the_query is_referenced_only_once

otherwise you have to tell PostgreSQL to use NOT MATERIALIZED.

The only small problem that I see is that testing will be required to see if NOT MATERIALIZED is an improvement or not? I can see circumstances where the balance will swing between the two depending on table size, fields selected and indexes on the fields and tables used in the CTE - in other words, there's no substitute for knowledge and experience. The DBA isn't dead and gone yet! :-)

Answer 2

Here is an illustration to Verace's answer and discussion in the comments under it. The gist is that by trying NOT MATERIALIZED and MATERIALIZED in WITH-clauses, you are effectively doing the planner's optimisation work for it. If you have many CTEs, you might have to try many combinations. But if you re-write your CTEs as views, the planner can do better on its own.

In my particular example, the planner struggles with nested CTEs (PostgreSQL 16.3). Adding NOT MATERIALIZED reduces query execution time by orders of magnitude.

My Table_1 and Table_2 have 4M and 8M rows, columns include "client_id", "transaction_date" and attributes. I want to get this week's counts for selected clients, which should be a lightning fast index-only scan, because in both tables I have indices on ("transaction_date") including relevant columns.

The code can be found below. Note that I use cte_shorthand as syntactic sugar, and that it is nested in cte_1 and cte_2.

The query runs in ~100ms when I have:

WITH cte_shorthand AS NOT MATERIALIZED ...
WITH cte_1 AS ...
WITH cte_2 AS ...

However, the execution takes ~17s if I remove NOT MATERIALIZED option from cte_shorthand, even if I add it to other CTEs:

WITH cte_shorthand AS ...
WITH cte_1 AS [NOT MATERIALIZED] ...
WITH cte_2 AS [NOT MATERIALIZED] ...

In the query plan I can see that the planner used index-only scan on cte_1, but not on cte_2. Instead of pushing "transaction_date" condition into retrieving rows from Table_2, it did a nested loop join of Table_2 (sequential scan) with cte_shorthand and filtered out almost all rows. I could not find any explanation, because cte_1 and cte_2 are symmetric in most ways, yet are treated differently.

One can get even worse performance ~70s when all CTEs are materialised.

Converting cte_shorthand to inline expressions has the same effect as NOT MATERIALIZED and results in the optimal performance. My own takeaway from this is: CTEs should not be used as syntactic sugar.

---

CREATE VIEW view_outer AS (
  WITH cte_shorthand AS NOT MATERIALIZED (
    SELECT  CURRENT_DATE - EXTRACT('dow' FROM NOW())::INT AS weekstart
    ),
  cte_1 AS (
    SELECT
      "client_id" AS "client_id",
      count(1) FILTER (WHERE ("attribute" = 1)) AS "counts_1",
      count(1) FILTER (WHERE ("attribute" = 2)) AS "counts_2"
    FROM "Table_1", cte_shorthand
    WHERE ("transaction_date" >= cte_shorthand.weekstart)
    GROUP BY "client_id"
  ),
  cte_2 AS (
    SELECT
      "client_id" AS "client_id",
      count(1) FILTER (WHERE ("attribute" = 1)) AS "counts_3",
    FROM "Table_2", cte_shorthand
    WHERE ("transaction_date" >= cte_shorthand.weekstart)
    GROUP BY "client_id"
  )
  SELECT cte_1.*, "counts_3"
  FROM cte_1, 
  FULL OUTER JOIN cte_2 ON cte_1."client_id" = cte_2."client_id"
);
-- Typical usage:
SELECT * FROM view_outer
WHERE "client_id" IN (1,2,3,4,10,11,12);

Answer 3

The only side effect should be performance-related (which I guess makes it the main effect, not a side effect). If there is any other side effects, those would have to be bugs. It is bit weird that that there is no "let the planner decide" setting.