Skip to content
mengfung.com
writing
programmingpostgresrailssql

When a CTE makes a Postgres query faster

What a WITH clause changes in the plan, measured on 2 million rows, and how to write the same queries in Rails.

 ·  9 min read

A CTE, the WITH name AS (...) at the top of a query, is usually sold as a readability tool. You name each step, and the query reads top to bottom instead of inside out. That part is true. Less well known is that a CTE also changes what Postgres executes, sometimes for the better and sometimes for the worse, and you can control which.

At work we have a scheduled worker that runs every hour, it’s job was to calculate which location needs an instance of a list. At the start it took under an hour to process but with more clients, more lists, and more data it started to push the worker to over 3 hours. So it would spawn workers every hour but take 3 hours to complete you start to see why it was causing issues. Using CTEs in which it used a query to find location/list pairs that needed instances at a given time, I cut process time down to less than an hour no matter how big the data was getting.

Everything below was run on Postgres 16 against a test database with 50,000 customers, 2 million orders and a five-level category tree. Times are from EXPLAIN ANALYZE on a laptop. The absolute numbers will differ on your hardware; the ratios are the point.

create table customers (id bigint primary key, name text not null, region text not null);
create table orders (
  id bigint primary key, customer_id bigint not null references customers(id),
  status text not null, total numeric(10,2) not null, created_at timestamptz not null);
create index on orders (customer_id);
create index on orders (created_at);
create table categories (id int primary key, parent_id int references categories(id), name text not null);

A CTE is not a fence any more

Before Postgres 12, every CTE was computed on its own and stored, and the rest of the query read from that stored result. Filters in the outer query could not reach inside. That is where the advice “CTEs are slow in Postgres” comes from.

Since 12, a CTE that is referenced once, is not recursive, and has no side effects is inlined: the planner treats it like a subquery and optimizes across it. You can see the difference by forcing the old behavior with MATERIALIZED:

with recent as (
  select * from orders where created_at > now() - interval '90 days'
)
select * from recent where customer_id = 4242;
default (inlined)   Bitmap Index Scan on orders_customer_id_idx   34 rows read     0.16 ms
AS MATERIALIZED     builds all recent orders, then filters        164,398 rows     138 ms

Inlined, Postgres combines both conditions and uses the index on customer_id. Materialized, it builds every order from the last 90 days first, then throws away all but five. Same query, about 850 times slower. So the first rule: on Postgres 12 and later, a CTE you use once costs nothing. Write them freely.

Compute once, use twice

The flip side is where a CTE earns its keep. When a CTE is referenced more than once, Postgres materializes it by default, so the expensive part runs once. Customers who spent more than twice the average:

with totals as (
  select customer_id, sum(total) as spent
  from orders
  group by customer_id
)
select *
from totals
where spent > (select avg(spent) * 2 from totals);

totals is used for the rows and again for the average. The plan scans the 2 million orders once and reads the stored 50,000 totals twice. Writing the same aggregate as two subqueries scans the orders table twice:

CTE used twice (materialized)      one scan of orders    ~305 ms
same aggregate as two subqueries   two scans of orders   ~640 ms

Adding NOT MATERIALIZED to the CTE forces it back to two scans and the slower time. It exists for the opposite case: a CTE used twice where each use filters down to a few rows, and you would rather each one hit an index than build the whole thing.

Filters and window functions

Window functions are where people expect a filter to be pushed down and get surprised, or expect it not to be and get a different answer. The latest order per customer:

with latest as (
  select o.*,
         row_number() over (partition by customer_id order by created_at desc) as rn
  from orders o
)
select * from latest where customer_id = 4242 and rn = 1;

This one is fast, 0.23 ms. customer_id is the partition key, so filtering on it before ranking cannot change any rank, and Postgres pushes it into the CTE and uses the index.

Now filter on something that is not the partition key:

-- filter outside: rank every order, then keep the pending ones
with latest as (
  select o.*, row_number() over (partition by customer_id order by created_at desc) as rn
  from orders o
)
select count(*) from latest where status = 'pending' and rn = 1;
-- 8,472 rows, 1.8 s

-- filter inside: rank only pending orders
with latest as (
  select o.*, row_number() over (partition by customer_id order by created_at desc) as rn
  from orders o
  where status = 'pending'
)
select count(*) from latest where rn = 1;
-- 49,940 rows, 240 ms

Postgres will not move status = 'pending' inside for you, because the two queries ask different questions. The first is “customers whose most recent order is pending”. The second is “each customer’s most recent pending order”. Both are reasonable, and the second ranks a sixth of the rows. When the second is what you mean, putting the filter inside the CTE is both the correct query and the fast one.

Several writes, one statement

A CTE can contain INSERT, UPDATE or DELETE with RETURNING, and the rest of the statement can use the rows it returns. Archiving old cancelled orders becomes one statement instead of a select, an insert and a delete:

with moved as (
  delete from orders
  where status = 'cancelled' and created_at < now() - interval '2 years'
  returning *
)
insert into orders_archive select * from moved;

On the test data that moved 111,734 rows in under a second, in one round trip, and it is atomic without an explicit transaction. There is no window where a row exists in both tables or in neither.

One rule catches people out. Every part of the statement sees the same snapshot, taken before any of it runs, so the main query cannot see what the CTE changed:

with gone as (delete from orders where id = 1 returning id)
select (select count(*) from orders where id = 1) as still_visible,
       (select count(*) from gone) as deleted;
-- still_visible = 1, deleted = 1

Use the RETURNING rows to pass data between steps, never a second read of the table.

Recursive CTEs instead of a loop in the app

Trees stored as parent_id, such as categories, org charts or threaded comments, usually get walked from application code: load the children, then their children, one query per level or one per node. A recursive CTE does the whole walk in the database:

with recursive tree as (
  select id, parent_id, name, 1 as depth from categories where id = 2
  union all
  select c.id, c.parent_id, c.name, t.depth + 1
  from categories c
  join tree t on c.parent_id = t.id
)
select * from tree;

That returns all 341 categories under node 2, five levels deep, in one query that takes 3 ms. The app version is five queries if it goes level by level, and 341 if it goes node by node.

The risk with recursion is bad data. If a category somehow becomes its own ancestor, the query never ends. I tested this by making node 2 a child of one of its own descendants, and the plain version ran until statement_timeout killed it. Postgres 14 added a CYCLE clause that tracks the path and stops:

with recursive tree as (
  select id, parent_id from categories where id = 2
  union all
  select c.id, c.parent_id from categories c join tree t on c.parent_id = t.id
) cycle id set is_cycle using path
select * from tree where is_cycle;  -- finds the loop instead of hanging

In Rails: activerecord-cte

At work, I wrote these queries in SQL first, checked them with EXPLAIN ANALYZE, and then had Claude translate them into ActiveRecord using the activerecord-cte gem. Writing the SQL first meant I knew what the query had to be before anything translated it, and the translation could be checked against it with to_sql.

The gem adds a with method to relations. Each key becomes a CTE name, and the value can be a relation:

# customers who spent more than twice the average
totals = Order.select(:customer_id, "sum(total) AS spent").group(:customer_id)

Customer.with(totals: totals)
        .joins("JOIN totals ON totals.customer_id = customers.id")
        .where("totals.spent > (SELECT avg(spent) * 2 FROM totals)")

To read from the CTE as if it were the model’s own table, alias it in from. You get Order objects back, with the extra rn column as an attribute:

# each customer's latest pending order
latest = Order.where(status: "pending")
              .select("orders.*, row_number() OVER (PARTITION BY customer_id ORDER BY created_at DESC) AS rn")

Order.with(latest: latest).from("latest AS orders").where("orders.rn = 1")

Recursive CTEs take :recursive as the first argument:

tree = <<~SQL
  SELECT * FROM categories WHERE id = 2
  UNION ALL
  SELECT categories.* FROM categories JOIN tree ON categories.parent_id = tree.id
SQL

Category.with(:recursive, tree: tree).from("tree AS categories")

I ran all three against the test database on Rails 7.0 and they return the same rows as the SQL above. A few things did not work, or do not exist:

  • The whole-string form fails. The README shows Post.with("posts_with_tags AS (SELECT ...)"). On Rails 7.0 with Postgres that raises TypeError: no implicit conversion of nil into String, because Rails’ Postgres code that writes the WITH clause expects a table name and the string has none. Use the hash form; a hash value can be a plain SQL string.
  • No MATERIALIZED option. When you need it, build the node yourself: 
    recent = Arel::Nodes::As.new(
  Arel::Table.new(:recent),
  Arel.sql("MATERIALIZED (#{Order.where("created_at > now() - interval '90 days'").to_sql})")
)
Order.with(recent).from("recent AS orders").where(customer_id: 4242)
  • No writes in CTEs. The gem’s README says update_all and delete_all with CTEs are not supported. The archive query stays SQL, run through ActiveRecord::Base.connection.execute.

If you are on Rails 7.1 or later, you may not need the gem. Rails 7.1 added with to ActiveRecord itself, and 7.2 added with_recursive. The two relation examples above work unchanged on Rails 8 without the gem. The recursive one becomes:

Category.with_recursive(
  tree: [Category.where(id: 2), Category.joins("JOIN tree ON categories.parent_id = tree.id")]
).from("tree AS categories")

The built-in with only takes a hash. For a raw SQL value, wrap it in Arel.sql. It rejects strings, Arel::Nodes::As and even Arel::Nodes::Cte with materialized: true, so I did not find a way to get MATERIALIZED out of it without dropping to SQL.

The trap is upgrading with the gem still installed. The gem defines its own with on every relation, replacing the one Rails ships. On Rails 8 with the gem loaded, the gem-style recursive query still works, but Rails’ own with_recursive fails with relation "tree" does not exist. The recursive half of the union ends up being run on its own, as a plain query, before the full statement is ever built. If you upgrade to 7.2 or later, move with(:recursive, ...) calls to with_recursive and remove the gem in the same change.

Check the plan, not the shape

None of this is visible in the query text. Whether a CTE is inlined or materialized, whether a filter reaches the index, whether a window ranks 2 million rows or 330,000: that is all in the plan. Run EXPLAIN ANALYZE on the SQL, and in Rails, call to_sql on the relation and run that. A CTE in the plan shows up as a CTE Scan when it is materialized and disappears when it is inlined, which is the quickest way to tell which one you got.