[rgharris]

I think the article and you are correct - the article is worded a little oddly though and ignores the fact that in Postgres 12 CTEs that are referenced multiple times are MATERIALIZED by default.

Before Postgres 12 CTEs were always materialized so you did not get any query optimization benefits of CTEs acting like inline subqueries.

After Postgres 12 all CTEs default to NOT MATERIALIZED if only referenced once or MATERIALIZED if referenced more than once. You can override via MATERIALIZED or NOT MATERIALIZED when defining the CTE.

Their example is showing that you can let Postgres (before 12) optimize a CTE for you by writing it as an inline subquery instead of a CTE:

  SELECT *
  FROM (
    SELECT *
    FROM sale
  ) AS inlined
  WHERE created_by_id = 1

But with Postgres 12 their "don't" example would result in an index scan without refactoring to the "do" example. Basically their advice on do vs don't applies to before Postgres 12.

https://www.postgresql.org/docs/12/queries-with.html is pretty thorough on this

[Dowwie]

Thanks for confirming