PostgreSQL’s CTEs are optimisation fences

As part of AXLE project work I recently found myself re-examining issues around optimisation fencing and non-semantic query execution changes in PostgreSQL. One key issue was the inability to use a CTE where optimisation (inlining, push-up/pull-down, flattening, etc) was desired. I’d like to explain that in more detail, as it’s something that can surprise new and experienced PostgreSQL users alike.

When optimising queries in PostgreSQL (true at least in 9.4 and older), it’s worth keeping in mind that – unlike newer versions of various other databases – PostgreSQL will always materialise a CTE term in a query.

This can have quite surprising effects for those used to working with DBs like MS SQL:

  • A query that should touch a small amount of data instead reads a whole table and possibly spills it to a tempfile; and
  • You cannot UPDATE or DELETE FROM a CTE term, because it’s more like a read-only temp table rather than a dynamic view.

It means that these two queries are surprisingly different:

WITH aggs(k,n) AS (SELECT x, count(x) FROM big_table GROUP BY x)
SELECT
   k, n
FROM aggs
WHERE k = 42;

and

SELECT
   k, n
FROM (SELECT x, count(x) FROM big_table GROUP BY x) aggs(k,n)
WHERE k = 42;

In other DBs they’d generally be different ways of writing the same thing, but in PostgreSQL the former will read and aggregate the whole table, then throw most of the work away, wheras the latter will generally get turned into:

SELECT
   k, n
FROM (SELECT x, count(x) FROM big_table GROUP BY x WHERE x = 42) aggs(k,n)

by the query optimiser and only read a small subset of the underlying table.

As there is significant opposition to changing this behaviour, you may find that you want to stick to using subqueries in FROM even when a CTE would be a lot clearer to read.

Essentially, PostgreSQL currently uses CTEs as query hints in order to avoid having actual query hints like Oracle’s /*+ materialize */. So the semantics of the query are conflated with execution policy about the query.

Because people have been advised to use CTEs as optimisation fences to work around planner issues in the past:

Another possible application is to prevent unwanted multiple evaluations of functions with side-effects. However, the other side of this coin is that the optimizer is less able to push restrictions from the parent query down into a WITH query than an ordinary sub-query. The WITH query will generally be evaluated as written, without suppression of rows that the parent query might discard afterwards.

… it’s going to be more difficult to get this changed in future, though it doesn’t look like the SQL spec requires CTEs to behave this way.

Note that PostgreSQL also uses another hint technique for blocking qualifier pull-up or push-down, the “offset 0″ hack, e.g.:

SELECT
   k, n
FROM (SELECT x, count(x) FROM big_table GROUP BY x OFFSET 0) aggs(k,n)
WHERE k = 42;

… which also acts as an optimisation fence, but is an obviously quirky anomaly that doesn’t serve any other purpose, unlike WITH, so it stands out as a hint way to modify execution without changing query semantics.

There are a few more like it, too.

If this CTE quirk bites you, feel free to leave a comment here or drop a note on pgsql-general mentioning how you ran into the issue. Probably calm down a bit before writing one like this though.


The research leading to these results has received funding from the European Union’s Seventh Framework Programme (FP7/2007-2013) under grant agreement n° 318633

This Post Has 12 Comments

  1. RobJ says:

    I’m a developer/DBA, not a Postgres hacker. To me, it seems natural that a with-query would be materialized, because it could be used for other with-queries in the same statement, not just the main SELECT statement. However, if the DBMS knows that a faster execution plan would merge the with query into the statement, it makes sense to allow it to do so.

    If merging optimization is a worthy goal, then this sounds like a good candidate for a postgresql.conf setting. The first release to include the setting would default it to the current fencing behavior, then a later release would default it to allow the merging behavior in a later release, and a final release would remove the setting and always allow merging. Calm folks could settle on the exact timeline.

  2. Josh Berkus says:

    The obvious answer here is going to be to have GUC which controls the optimization fence behavior.

    • craig.ringer says:

      That’s my view as well, since we’re stuck with the fence as a feature. I seem to recall significant opposition to doing that last time it came up on -hackers; I’d need to re-read the whole thread to find it.

      • Josh Berkus says:

        It was probably something to do with writeable CTEs, which *must* be fenced for obvious reasons.

        • craig.ringer says:

          wCTEs must obviously be fenced, but that’s no good reason for a SELECT to be. We know at parse time whether a CTE term is a wCTE or not.

          That isn’t true of functions with side effects in a regular SELECT term in a CTE. Though that isn’t really greatly different to subqueries, views, etc – we’re not, AFAIK, particularly careful to avoid pullup/pushdown of quals if it might affect the rows affected by VOLATILE functions.

          I suppose in an ideal world we’d be able to mark functions as “VOLATILE READ ONLY” vs “VOLATILE READ WRITE” (or something) then scan the parse tree for r/w functions. But we haven’t had it so far and we still inline views and all sorts of other things without it.

          Ideally (to me) a SELECT term in a CTE should work just like a view that’s only visible for the life of the query – complete with being simply updatable if applicable. I’m not even sure it’s complicated to implement and I’ve done enough work in the rewriter that I should have a go.

          If people want them to be materialized they should use our materialize hint OFFSET 0 hack.

  3. Jon Erdman says:

    You might want to point out that the CTEs themselves are now writable (i.e. you can do an UPDATE/DELETE/INSERT inside the CTE itself, which can do some pretty interesting things when combined with RETURNING).

  4. Josh Kupershmidt says:

    One notable exception to the “(non-writeable) CTEs are always materialized” rule is that if a non-writeable CTE node is not referenced anywhere, it won’t actually be evaluated. For example, this query returns 1 instead of bombing out:

    WITH not_executed AS (SELECT 1/0),
    executed AS (SELECT 1)
    SELECT * FROM executed;

  5. Noah Yetter says:

    Given that the Postgres development group stubbornly and foolishly refuses to implement hints, this is fully necessary. If CTEs *didn’t* act as an optimization fence, there would be essentially no way to hand-tune queries in Postgres.

    • craig.ringer says:

      Actually, I showed how to do just that above: the OFFSET 0 hack prevents pushdown/pullup too. Unlike CTEs it has no other utility – it really is a hint, it just isn’t called a hint.

      So I disagree that CTE fencing is the only way to achieve such tweaks.

  6. Len Jaffe says:

    Why the subqueries at all?

    Why aren’t you just writing

    select x, count(*) from table where x = 42 group by x; ?

    • craig.ringer says:

      I needed to construct a simple query that’d demonstrate the behaviour without being complex or confusing enough to detract from the explanation.

      Most of the real queries where this becomes a problem are complicated enough that they’d need a fair bit of explanation that’s unrelated to the topic at hand.

      You’re correct that you can just flatten it by hand. That’s not the point of the article, though.

Leave A Reply