Subquery Scan in PostgreSQL EXPLAIN

What it does

PostgreSQL usually flattens a subquery in FROM (or a view) into the outer query, so the two are planned as one and you never see the boundary. Some subqueries can’t be flattened without changing their meaning: ones with DISTINCT or DISTINCT ON, LIMIT, GROUP BY and aggregates, window functions, set operations. For those, the subquery is planned on its own, and a Subquery Scan node reads its output for the outer query.

Its jobs are to apply a Filter (outer conditions that couldn’t be pushed inside) and to produce the columns the outer query wants. When neither is needed, the planner leaves it out, which is why you don’t see one for every subquery.

UNION ALL branches also show up as Subquery Scan on "*SELECT* 2" and so on, when a branch needs its columns adjusted, for example to add a constant.

When the planner picks it

• The outer query filters on a column computed inside a subquery that can’t be flattened: WHERE rn = 2 on a window function’s result, WHERE total > 500 on rows picked by DISTINCT ON.
• A UNION ALL branch selects expressions or constants that the other branches compute differently.
• Some views, when queried with conditions that can’t be pushed through their DISTINCT, LIMIT or window functions.

PostgreSQL does push conditions into a subquery when it’s safe: a condition on a GROUP BY column, for instance, can be applied before grouping. A Filter on the Subquery Scan is what’s left when it isn’t safe.

Reading its numbers

Subquery Scan on latest  (cost=9693.16..9859.59 rows=3081 width=26) (actual time=11.219..12.073 rows=259.00 loops=1)
  Filter: (latest.total > '500'::numeric)
  Rows Removed by Filter: 241
  ->  Unique  (… rows=500.00 loops=1)

• on latest: the subquery’s alias. "*SELECT* 2" names the second branch of a UNION.
• Filter: outer conditions applied to the subquery’s rows.
• Rows Removed by Filter: rows the subquery produced only for them to be thrown away. Compare with the child’s rows: here 500 produced, 259 kept.
• Everything else (time, buffers) is mostly the subquery’s.

When it’s a problem

1. The subquery does far more work than the result needs. A large Rows Removed by Filter means the inner query computed rows that were discarded. Ask whether the condition can be moved inside. It often can’t without changing the meaning: “the latest order per customer, if it’s over 500” is not the same as “the latest order over 500 per customer”. When it can, write it inside.
2. Top-N per group with a window function. WHERE rn <= 3 on row_number() used to be a Subquery Scan with a filter over every row of every partition. Since PostgreSQL 15, the WindowAgg stops computing a partition once the condition can no longer be true (Run Condition), and for rn <= 3 the Subquery Scan disappears. See the version comparison below and WindowAgg.
3. A view that blocks push-down. Querying a view with DISTINCT, LIMIT or window functions with a selective WHERE makes the view compute everything first. A function or a version of the view that takes the condition inside avoids that.

Example

PostgreSQL 18.6, default settings:

CREATE SCHEMA seo_explain;
SET search_path = seo_explain;

CREATE TABLE customers (
  id         int PRIMARY KEY,
  name       text NOT NULL,
  country    text NOT NULL,
  created_at timestamptz NOT NULL
);
INSERT INTO customers
SELECT i, 'Customer ' || i,
       (ARRAY['GB','US','DE','FR','NL','IE','ES','IT','SE','NO',
              'DK','FI','PL','PT','BE','AT','CH','CA','AU','NZ'])[1 + (i * 7) % 20],
       timestamptz '2023-01-01' + i * interval '20 minutes'
FROM generate_series(1, 50000) AS i;

CREATE TABLE orders (
  id          bigint PRIMARY KEY,
  customer_id int NOT NULL REFERENCES customers (id),
  status      text NOT NULL,
  total       numeric(10,2) NOT NULL,
  created_at  timestamptz NOT NULL
);
INSERT INTO orders
SELECT i,
       1 + (i::bigint * 7919) % 50000,
       CASE WHEN i % 100 < 90 THEN 'shipped'
            WHEN i % 100 < 97 THEN 'pending'
            ELSE 'refunded' END,
       round(((i * 37) % 100000) / 100.0, 2),
       timestamptz '2024-01-01' + i * interval '1 minute'
FROM generate_series(1, 1000000) AS i;
CREATE INDEX orders_customer_id_idx ON orders (customer_id);
CREATE INDEX orders_created_at_idx ON orders (created_at);
VACUUM ANALYZE customers, orders;

Each of the first 500 customers’ latest order, kept only if it’s over 500:

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM (
  SELECT DISTINCT ON (customer_id) customer_id, id, total, created_at
  FROM orders WHERE customer_id <= 500
  ORDER BY customer_id, created_at DESC
) latest
WHERE total > 500;

Subquery Scan on latest  (cost=9693.16..9859.59 rows=3081 width=26) (actual time=11.219..12.073 rows=259.00 loops=1)
  Filter: (latest.total > '500'::numeric)
  Rows Removed by Filter: 241
  Buffers: shared hit=8346
  ->  Unique  (cost=9693.16..9744.05 rows=9243 width=26) (actual time=11.211..12.028 rows=500.00 loops=1)
        Buffers: shared hit=8346
        ->  Sort  (cost=9693.16..9718.61 rows=10178 width=26) (actual time=11.209..11.599 rows=10000.00 loops=1)
              Sort Key: orders.customer_id, orders.created_at DESC
              Sort Method: quicksort  Memory: 853kB
              Buffers: shared hit=8346
              ->  Bitmap Heap Scan on orders  (cost=119.30..9015.65 rows=10178 width=26) (actual time=1.454..8.010 rows=10000.00 loops=1)
                    Recheck Cond: (customer_id <= 500)
                    Heap Blocks: exact=8334
                    Buffers: shared hit=8346
                    ->  Bitmap Index Scan on orders_customer_id_idx  (cost=0.00..116.76 rows=10178 width=0) (actual time=0.666..0.666 rows=10000.00 loops=1)
                          Index Cond: (customer_id <= 500)
                          Index Searches: 1
                          Buffers: shared hit=12
Planning Time: 0.090 ms
Execution Time: 12.143 ms

total > 500 couldn’t move inside the DISTINCT ON without changing which order counts as the latest, so all 500 latest orders were found first and 241 dropped afterwards.

A UNION ALL branch that adds a constant:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, 'order' AS kind, created_at FROM orders WHERE customer_id = 4242
UNION ALL
SELECT id, 'customer', created_at FROM customers WHERE id = 4242;

Append  (cost=4.58..90.32 rows=21 width=48) (actual time=0.203..0.593 rows=21.00 loops=1)
  Buffers: shared read=26
  ->  Bitmap Heap Scan on orders  (cost=4.58..81.89 rows=20 width=48) (actual time=0.202..0.472 rows=20.00 loops=1)
        Recheck Cond: (customer_id = 4242)
        Heap Blocks: exact=20
        Buffers: shared read=23
        ->  Bitmap Index Scan on orders_customer_id_idx  (cost=0.00..4.58 rows=20 width=0) (actual time=0.073..0.073 rows=20.00 loops=1)
              Index Cond: (customer_id = 4242)
              Index Searches: 1
              Buffers: shared read=3
  ->  Subquery Scan on "*SELECT* 2"  (cost=0.29..8.32 rows=1 width=48) (actual time=0.117..0.118 rows=1.00 loops=1)
        Buffers: shared read=3
        ->  Index Scan using customers_pkey on customers  (cost=0.29..8.31 rows=1 width=44) (actual time=0.112..0.112 rows=1.00 loops=1)
              Index Cond: (id = 4242)
              Index Searches: 1
              Buffers: shared read=3
Planning:
  Buffers: shared hit=82 read=5
Planning Time: 0.364 ms
Execution Time: 0.647 ms

Here the Subquery Scan only converts the second branch’s columns to match the first. It costs nothing worth worrying about.

Top three orders per customer, PostgreSQL 14 against 15 and later

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM (
  SELECT id, customer_id, total,
         row_number() OVER (PARTITION BY customer_id ORDER BY total DESC) AS rn
  FROM orders WHERE customer_id <= 100
) ranked
WHERE rn <= 3;

PostgreSQL 14.24 computes every row number, then filters:

Subquery Scan on ranked  (cost=4753.17..4816.77 rows=652 width=26) (actual time=217.059..218.929 rows=300 loops=1)
  Filter: (ranked.rn <= 3)
  Rows Removed by Filter: 1700
  Buffers: shared hit=9 read=2001
  ->  WindowAgg  (cost=4753.17..4792.31 rows=1957 width=26) (actual time=217.058..218.845 rows=2000 loops=1)
  …

PostgreSQL 15.19 (and 16–18) has no Subquery Scan at all; the condition became a Run Condition on the WindowAgg, which stops numbering a partition after row 3:

WindowAgg  (cost=4740.96..4779.96 rows=1950 width=26) (actual time=195.439..195.796 rows=300 loops=1)
  Run Condition: (row_number() OVER (?) <= 3)
  …

With WHERE rn = 2 instead, 17 and 18 keep the Subquery Scan (equality isn’t a stopping condition by itself) but still give the WindowAgg a Run Condition of row_number() <= 2, so the filter removes 100 rows instead of 14’s 1,900.

In Inlet

Inlet draws EXPLAIN ANALYZE as a tree and highlights the slowest step and badly misestimated row counts.