MATERIALIZED vs. NOT MATERIALIZED

Was sich in PG 12 geändert hat

Bis PG 11:

WITH paid AS (
    SELECT * FROM orders WHERE status = 'paid'
)
SELECT * FROM paid WHERE total > 100;

Postgres 11 hätte ausgewertet: erst alle paid-Bestellungen sammeln (Materialisierung), dann in der äußeren Query nach total > 100 filtern. Der äußere Filter konnte den inneren nicht beeinflussen — ein Optimizer-Stopper.

Postgres 12+ macht's anders: erkennt, dass der CTE einfach inline-bar ist, und wandelt die Query in:

SELECT *
FROM (
    SELECT * FROM orders WHERE status = 'paid'
) paid
WHERE total > 100;

…und kann beide Filter zusammenziehen. Ein einzelner Index-Lookup auf (status, total) erledigt das in einem Schritt — viel schneller bei großen Tabellen.

Wann inlinet PG 12+ einen CTE?

Drei Bedingungen müssen erfüllt sein, damit Postgres automatisch inlinet:

1. CTE wird nur einmal in der Query referenziert
2. CTE ist nicht rekursiv (kein WITH RECURSIVE)
3. CTE ist nicht modifizierend (kein INSERT/UPDATE/DELETE darin)

In allen anderen Fällen wird automatisch materialisiert.

Explizit steuern

WITH paid AS MATERIALIZED (
    SELECT * FROM orders WHERE status = 'paid'
)
SELECT * FROM paid WHERE total > 100;

MATERIALIZED zwingt Postgres, das CTE-Resultat zu materialisieren — auch wenn nur einmal referenziert. Sinnvoll, wenn:

• der CTE eine teure Operation ist (z. B. komplexe Aggregation), die du vor dem Outer-Filter haben willst
• du einen Plan-Hint brauchst, weil der Optimizer einen schlechten Plan wählt
• du Side-Effects in einem CTE garantieren willst (Lesen mit Lock-Akquirierung etc.)

WITH paid AS NOT MATERIALIZED (
    SELECT * FROM orders WHERE status = 'paid'
)
SELECT * FROM paid WHERE total > 100;

NOT MATERIALIZED zwingt zum Inlining. Default-Verhalten ab PG 12 — nur in Spezial-Fällen explizit nötig.

EXPLAIN-Vergleich

Bei einer großen orders-Tabelle kann man den Unterschied direkt sehen:

myapp=> EXPLAIN
        WITH paid AS (SELECT * FROM orders WHERE status='paid')
        SELECT * FROM paid WHERE total > 100;
                               QUERY PLAN
----------------------------------------------------------------
 Index Scan using orders_status_total_idx on orders
   Index Cond: ((status = 'paid') AND (total > 100))
 Planning Time: 0.123 ms

myapp=> EXPLAIN
        WITH paid AS MATERIALIZED (SELECT * FROM orders WHERE status='paid')
        SELECT * FROM paid WHERE total > 100;
                               QUERY PLAN
----------------------------------------------------------------
 CTE Scan on paid  (rows=49423)
   Filter: (total > 100)
   CTE paid
     ->  Seq Scan on orders
           Filter: (status = 'paid')

Inlining: ein einziger Index Scan. Materialisierung: ein Sequential Scan über die ganze Tabelle, dann Filter auf das Zwischen-Resultat.

Bei wenigen Zeilen oft egal. Bei Millionen-Tabellen Faktor 10-100.

Wann lohnt MATERIALIZED?

Trotz schlechterer Default-Performance gibt es echte Anwendungsfälle:

1. Mehrfach-Verwendung mit teurer Aggregation

WITH expensive AS MATERIALIZED (
    SELECT
        customer_id,
        count(*)         AS orders_count,
        sum(total)       AS revenue,
        array_agg(id)    AS order_ids
    FROM orders
    GROUP BY customer_id
)
SELECT * FROM expensive WHERE revenue > 1000
UNION ALL
SELECT * FROM expensive WHERE orders_count > 50;

Ohne MATERIALIZED würde Postgres den teuren Aggregat zweimal ausführen — einmal pro UNION-Ast. Mit Materialisierung einmal — und dann zweimal aus dem Cache lesen.

2. Gewollter Optimizer-Stopper

Manchmal weiß der Entwickler mehr als der Optimizer. Wenn der Plan nach Inlining schlecht wird (z. B. wegen schlechter Statistiken), kann erzwungene Materialisierung helfen. Eher selten — meistens lieber Statistiken refreshen.

3. Side-Effects in der Reihenfolge

Bei Data-Modifying-CTEs ist die Materialisierung Pflicht — Postgres macht das automatisch. Mehr im Artikel Data-Modifying-CTEs.

Migration von alten Queries

Wer Code von PG 11 oder älter auf PG 12+ migriert: Verhalten kann sich ändern. Queries, die vorher implizit auf Materialisierung gesetzt haben, könnten jetzt langsamer oder schneller laufen — je nach Query.

Pragmatisch:

1. Bestehende Queries laufen lassen, mit EXPLAIN ANALYZE Performance prüfen.
2. Bei spürbar langsameren Queries: MATERIALIZED ausprobieren (manchmal ist der alte Plan tatsächlich besser).
3. Bei spürbar schnelleren Queries: nichts tun — der Optimizer hat richtig entschieden.

Meistens gewinnt die neue Inline-Logik. Bei komplexen Reports mit ungewöhnlichen Daten-Verteilungen lohnt sich Messen.

Weiterführende Ressourcen

Externe Quellen

• WITH Queries – Materialization
• Release Notes 12 – CTE Inlining
• EXPLAIN – PostgreSQL Documentation

Verwandte Artikel

• CTE-Grundlagen
• Rekursive CTEs
• Data-Modifying-CTEs