CTE Scan

A CTE Scan reads the rows of a WITH query that PostgreSQL computed once and kept, in memory or in temp files. Since PostgreSQL 12 that only happens when the planner cannot fold the CTE into the main query. A CTE is inlined, and has no node of its own, when it is referenced once, only selects, and is not marked MATERIALIZED. The sample's web_orders meets all three, so its plan has no CTE Scan: the WHERE channel = 'web' AND placed_at >= ... runs as a plain Seq Scan on commerce.orders.

Why it matters

Materializing is right when the CTE is expensive and used twice or more, when it is recursive, or when it writes. An INSERT ... RETURNING inside a WITH is the last case. It is the problem when it stops an outer WHERE being pushed into the CTE. A materialized web_orders with WHERE w.customer_id = $1 outside it would compute all 206,880 web orders, keep them, then read them back to find one customer's. Compare the CTE Scan's rows out with the rows the CTE produced. A wide gap is a filter that belonged inside.

What to do

1. If a query changed speed at the PostgreSQL 12 upgrade, look here first: before 12 every CTE was materialized.
2. If a CTE used twice is materialized and you want it folded anyway, WITH web_orders AS NOT MATERIALIZED (...). The planner then computes it at each reference.
3. If a folded CTE lands in a bad plan, WITH web_orders AS MATERIALIZED (...) computes it once and keeps the result.

In the Plan tab

A CTE Scan is drawn with the CTE's name where other scans show a table, and has no wheel, since there is no table behind it. For the sample's web_orders there is nothing to find. Look at the Seq Scan on commerce.orders instead.