Suppose one report needs the completed count and sales total, the pending count, and the canceled count from the same table. Separate subqueries or queries scan the table once per aggregate. Conditional aggregation instead reads the table once and routes each row to the appropriate counters as it goes.

-- ✗ One full scan per aggregate (5 million rows × 4)
SELECT
  (SELECT COUNT(*) FROM orders WHERE status = 'completed'),
  (SELECT SUM(amount) FROM orders WHERE status = 'completed'), ...

-- ✓ Every aggregate in one scan with PostgreSQL's FILTER clause
SELECT COUNT(*) FILTER (WHERE status = 'completed'), ...
FROM orders;

From the 5-million-row production orders table, return these four values in one row with only one scan: completed_cnt, completed_amt, pending_cnt, and cancelled_cnt.

Imagine replacing four KPI queries on a dashboard with a single query.

Expected output (one row):

Four subqueries scan the equivalent of 5 million × 4 = 20 million rows. Conditional aggregation scans 5 million rows once.

SELECT
  COUNT(*)    FILTER (WHERE status = 'completed') AS completed_cnt,
  SUM(amount) FILTER (WHERE status = 'completed') AS completed_amt,
  COUNT(*)    FILTER (WHERE status = 'pending')   AS pending_cnt,
  COUNT(*)    FILTER (WHERE status = 'cancelled') AS cancelled_cnt
FROM orders;   -- this is the only table scan

/*
  Execution order:
  1. Scan orders from beginning to end once (Seq Scan)
  2. Evaluate every aggregate's FILTER condition for each row
  3. Update only the counters or sum whose conditions match
  4. Return all four aggregate values as one row after the scan finishes
  */

ORDER BY may look like a small final step, but it is a blocking operation that cannot begin producing ordered output until it has received the necessary input. Large sorts can exceed work_mem and fall back to an expensive disk-based external merge. A B-tree index, however, is always ordered. When its columns and order match the query, the database can remove the sorting stage entirely.

-- ✗ No index: read every row → sort → take the first three
Limit → Sort (Sort Method: top-N heapsort) → Seq Scan

-- ✓ published_at index: the Sort stage disappears
Limit → Index Scan Backward using idx_articles_pub
       -- The index guarantees order; stop after three rows

Return the three newest articles from the 2-million-row production articles table in descending published_at order. Also define an index that eliminates the sorting stage. Return article_id, title, published_at.

Think of a news site's “latest articles” panel: sorting 2 million rows on every request versus reading only three entries.

Expected output (published_at descending, 3 rows):

With the index, the Sort node disappears and the plan reads only the first three index entries plus three heap rows.

CREATE INDEX idx_articles_pub
  ON articles (published_at DESC);  -- create an index in the ORDER BY sequence

SELECT   article_id, title, published_at
FROM     articles
ORDER BY published_at DESC   -- matches index order, so no Sort node is needed
LIMIT    3;                    -- stop the scan after three rows

/*
  Execution order:
  1. Position at the leading edge of idx_articles_pub (largest published_at)
  2. Read index entries in order and fetch the three columns from the heap
  3. LIMIT stops the scan after three rows; no Sort stage exists
  */

UNION removes duplicates while UNION ALL preserves them, but their performance is fundamentally different. UNION must sort or hash every selected column of every combined row to eliminate duplicates. UNION ALL merely appends one result to another, adding almost no work. When the data model makes overlap impossible, duplicate elimination is a tax with no benefit.

-- UNION: eliminate duplicates after Append; every row enters Sort or HashAggregate
HashAggregate  -- a hash table or sort for 5 million rows
  └─ Append → Seq Scan ×2

-- UNION ALL: only append the streams
Append → Seq Scan ×2   -- no duplicate-elimination stage exists

The 3-million-row domestic_orders table and 2-million-row overseas_orders table use separate order-ID ranges, so the same order can never occur in both. Return one combined list of order_id, customer, amount without incurring an unnecessary duplicate-elimination stage.

Is “use UNION to be safe” really safe for performance? Consider what happens across 5 million rows.

Expected output (5 rows, no duplicate-elimination stage):

UNION ALL needs only Append. UNION performs duplicate checks across 5 million rows to produce the same answer.

SELECT order_id, customer, amount
FROM   domestic_orders
UNION ALL                       -- mutually exclusive sources need no duplicate elimination
SELECT order_id, customer, amount
FROM   overseas_orders;

/*
  Execution order:
  1. Scan domestic_orders and stream each row directly to the output
  2. Scan overseas_orders next and append its rows
  3. Perform no sorting, hashing, or duplicate comparisons
  */

This final problem combines the second half's building blocks: 1. UNION ALL for mutually exclusive sources (Q9), 2. NOT EXISTS for exclusions (Q6: NULL-safe and Anti Join-friendly), and 3. FILTER for multiple conditional aggregates in one scan (Q7). The opposite pattern—UNION + NOT IN + one subquery per aggregate—costs several times more and may return a wrong result when NULL appears.

-- Composition pattern, read from the inside outward
SELECT aggregate FILTER (...)        -- 3. conditional metrics in one scan
FROM ( ... UNION ALL ... ) s    -- 1. combine exclusive sources
WHERE NOT EXISTS ( ... )        -- 2. NULL-safe exclusion
GROUP BY ...

Build a shipment report. Combine domestic shipments_jp and international shipments_intl, whose ID systems are mutually exclusive and whose production tables each contain millions of rows. Exclude shipments from test accounts, then aggregate pending and shipped counts by region. test_accounts.customer_id is nullable because pending applications may appear. Return region, pending_cnt, shipped_cnt ordered by region ascending.

Also identify where a UNION + NOT IN + status-specific subqueries version would become slow or incorrect.

Expected output (region ascending):

Only ship_id=53, the shipment for test account 999, is excluded. NOT IN would return zero rows because test_accounts contains NULL.

SELECT   s.region,
         COUNT(*) FILTER (WHERE s.status = 'pending') AS pending_cnt,
         COUNT(*) FILTER (WHERE s.status = 'shipped') AS shipped_cnt
FROM (
  SELECT 'JP'   AS region, status, customer_id FROM shipments_jp
  UNION ALL                       -- exclusive sources: ALL avoids duplicate-elimination cost
  SELECT 'INTL', status, customer_id FROM shipments_intl
) s
WHERE    NOT EXISTS (              -- NULL-safe exclusion through a Hash Anti Join
           SELECT 1 FROM test_accounts t
           WHERE  t.customer_id = s.customer_id
         )
GROUP BY s.region
ORDER BY s.region;

/*
  Execution order:
  1. Append shipments_jp and shipments_intl while adding region
  2. Build a hash table from test_accounts for matching
  3. Exclude matching rows with an Anti Join
  4. Group by region and update the FILTER counters
  5. Sort by region ascending and return the result
  */