Performance Optimization — Learn Practical INDEX Design, LATERAL, and Top-N in Practice

QUESTION 1

Date Half-Open Intervals — Cut Through Function and BETWEEN Traps with Range Conditions

Half-Open Intervaldate_truncBETWEENRange Scan

Background ▼

When extracting a period such as “orders in May 2026,” writing date_trunc('month', ordered_at) = '2026-05-01' wraps the column in a function and prevents the index from being used (a Sargable violation). Moreover, BETWEEN '2026-05-01' AND '2026-05-31' is dangerous for a timestamp column: every row after May 31 at 00:00:00 is missed. The correct form is a half-open interval (greater than or equal to the start, less than the end).

-- ✗ Wrap the column in a function → function evaluation for every row = Seq Scan
WHERE date_trunc('month', ordered_at) = '2026-05-01'

-- ✗ BETWEEN includes both endpoints → rows after 5/31 00:00:00 are missed
WHERE ordered_at BETWEEN '2026-05-01' AND '2026-05-31'

-- ✓ Half-open interval: the index works and the month-end boundary is exact
WHERE ordered_at >= '2026-05-01' AND ordered_at < '2026-06-01'

Why half-open intervals are correct: With the end set to < the start of the next period, you never need to reason about fractional seconds, and you need no special cases for 28-, 30-, or 31-day months. The same form works for days, months, and years, making it the universal form for period conditions.

Problem ▼

The orders table (3 million actual rows) has an index idx_orders_ordered_at on ordered_at (timestamp). Retrieve orders from May 2026 in ordered_at ascending order, with the index usable and every fractional second at month-end included. Return order_id, ordered_at, amount.

Source table

- orders (ordered_at is timestamp / indexed)

order_id	ordered_at	amount
1	2026-04-30 23:59:59	700
2	2026-05-01 00:00:00	3400
3	2026-05-14 12:30:00	800
4	2026-05-20 18:05:00	950
5	2026-05-31 23:59:59.9	2600
6	2026-06-01 00:00:00	1500

※ order_id=5 is May 31 at 23:59:59.9. Also consider what happens to this row with BETWEEN ... AND '2026-05-31'.

Expected Output ▼

Expected output (ordered_at ascending):

order_id	ordered_at	amount
2	2026-05-01 00:00:00	3400
3	2026-05-14 12:30:00	800
4	2026-05-20 18:05:00	950
5	2026-05-31 23:59:59.9	2600

With a half-open interval, only the contiguous index range is read, and the row right at month-end (id=5) is included exactly.

Model Answer ▼

SELECT   order_id, ordered_at, amount
FROM     orders
WHERE    ordered_at >= TIMESTAMP '2026-05-01'  -- Start: greater than or equal to the beginning of May
  AND    ordered_at <  TIMESTAMP '2026-06-01'  -- End: less than the next month’s start (half-open interval)
ORDER BY ordered_at;                           -- Same as index order → no additional sort

/*
  Logical execution order:
  1. Binary-search idx_orders_ordered_at  → jump to the start of the range
  2. Scan the leaf blocks to the right in time order  → contiguous scan
  3. Stop at the end of the range  → do not scan rows outside it
  4. Index order = ordered_at ascending  → return without a sort stage
  */

Explanation (table transitions & key points) ▼

SELECT   order_id, ordered_at, amount
FROM     orders
WHERE    ordered_at >= '2026-05-01'
  AND    ordered_at <  '2026-06-01'
ORDER BY ordered_at;

LEGEND ▼

Rows read / loaded

1. Source table — focus on the month boundaries ▼

FROM ordersThis data tests both performance and correctness. id=1 and id=6 are just outside May, while id=5 is just inside May (23:59:59.9). The quality of the query is exposed by how it handles these four boundary cases.

1 / 5

order_id	ordered_at	amount
1	2026-04-30 23:59:59	700
2	2026-05-01 00:00:00	3400
3	2026-05-14 12:30:00	800
4	2026-05-20 18:05:00	950
5	2026-05-31 23:59:59.9	2600
6	2026-06-01 00:00:00	1500

6 rows (3 million actual rows)

LEARNING POINTS

Keep the column raw; transform the condition side: date_trunc(column) = value transforms the column, whereas a half-open interval transforms the boundary constant. The constant is calculated once before execution, so the index remains usable. The calculation that finds the month start is not bad in itself; which side you write it on is everything (for example, ordered_at >= date_trunc('month', now()) is fine).

Treat BETWEEN as for discrete values: BETWEEN is a closed interval that includes both endpoints, so it is safe for discrete values such as integers and DATE. But for a continuous value such as timestamp, “the last day” means that day at 00:00:00, so fractional times are omitted. Escaping with <= '…23:59:59' is also unsafe because millisecond or microsecond precision can create another omission. Use a half-open interval for continuous periods.

A ::date cast has the same problem: WHERE ordered_at::date = '2026-05-14' also transforms the column and causes a Seq Scan. Use >= '2026-05-14' AND < '2026-05-15' for one day as well, so monthly, daily, and arbitrary-period extraction all follow the same pattern.

ANTI-PATTERNS

Filter by month with EXTRACT(YEAR ...) AND EXTRACT(MONTH ...): EXTRACT(YEAR FROM ordered_at) = 2026 AND EXTRACT(MONTH FROM ordered_at) = 5 applies two functions and completely disables the index, and it cannot be extended cleanly to a range crossing years. It is common in reporting SQL, but whenever you find it, it is worth rewriting as a half-open interval.

Calculate “month end” in the application and pass it in: Calculating “the end of May = 5/31” in the application and passing it to BETWEEN carries both month-length bugs (such as leap-year February) and fractional-time omissions. Always pass the start of the next period; the calculation then only needs to add one month to the month start.

Practical note: Time zones and the boundary of “one day”

For an international service using a timestamptz column, the next question is “whose midnight is 2026-05-01?” Writing WHERE ordered_at >= '2026-05-01' without qualification makes the boundary depend on the session’s TimeZone setting, so explicitly state the business-standard time zone ('2026-05-01 00:00:00+09' or AT TIME ZONE). The key point is that the time-zone conversion also leaves the index unharmed when applied to the constant side. The moment you apply ordered_at AT TIME ZONE 'Asia/Tokyo' to the column, it falls back to a Seq Scan. In Q2, we will design the column order of a composite index when there are two or more filtering conditions.

Model answers and explanations are for members

Read every model answer, explanation and table-transition visualization across all 48 advanced sets (240 questions)
One-time purchase — no subscription. Future advanced sets are included
Background, problem and expected output stay free

Unlock for $20 USD

Payments are securely processed by Stripe — your card details never touch this site.

You are charged in US dollars. If your card is issued in another currency, your bank’s exchange rate and any foreign transaction fee apply.

Already purchased? Sign in on your account page.

QUESTION 2

Composite Index Column Order — Design for “Equality → Range → Sort”

Composite indexColumn orderEquality → rangeAvoiding sort

Background ▼

A composite index (A, B) is like a telephone book: it is ordered by A, then by B among rows with the same A (family name → given name). The condition shape that makes the most of this structure is “equality → range → sort”: fix the first column with equality (=), and the next column becomes a neatly ordered contiguous range inside it, allowing both the range condition and ORDER BY to use the index.

-- For index (status, shipped_at):
WHERE status = 'shipped'              -- 1. Fix the first column with equality (one interval point)
  AND shipped_at >= '2026-06-01'      -- 2. Range scan inside the fixed interval
ORDER BY shipped_at                    -- 3. Already ordered inside the interval → no sort

What about the reverse order (shipped_at, status)? With the range column first, you must read a wide interval containing every status and judge status row by row. “Put columns narrowed by equality on the left; range and sort columns on the right.” This is the most important composite-index design principle.

Problem ▼

The shipments table (2 million actual rows) has a composite index idx_ship_status_date (status, shipped_at). Retrieve records where status is 'shipped' and shipment date is on or after June 1, 2026, with both filtering and sorting completed using only this index, ordered by shipped_at ascending. Return ship_id, status, shipped_at, dest.

Source table

- shipments (composite index on (status, shipped_at))

ship_id	status	shipped_at	dest
1	pending	2026-06-02	Tokyo
2	shipped	2026-05-28	Osaka
3	shipped	2026-06-01	Tokyo
4	delivered	2026-06-03	Nagoya
5	shipped	2026-06-04	Sapporo
6	pending	2026-05-30	Fukuoka
7	shipped	2026-06-09	Tokyo
8	delivered	2026-06-05	Kobe

※ Also consider how the scan would change if the index were reversed to (shipped_at, status).

Expected Output ▼

Expected output (shipped_at ascending):

ship_id	status	shipped_at	dest
3	shipped	2026-06-01	Tokyo
5	shipped	2026-06-04	Sapporo
7	shipped	2026-06-09	Tokyo

Equality + range + ORDER BY all use one index, so the Sort node disappears from the execution plan.

Model Answer ▼

SELECT   ship_id, status, shipped_at, dest
FROM     shipments
WHERE    status = 'shipped'                -- Equality: fix the first index column at one value
  AND    shipped_at >= DATE '2026-06-01'  -- Range: scan contiguously inside the fixed interval
ORDER BY shipped_at;                        -- Already ordered inside the interval → omit the sort

/*
  Logical execution order:
  1. Binary-search the composite key in idx_ship_status_date  → status interval
  2. Jump directly to the start inside the interval          → range start
  3. Scan right in shipped_at order                           → contiguous scan
  4. Stop when status changes                                 → range upper bound
  5. Index order satisfies ORDER BY                           → no Sort stage
  */

Explanation (table transitions & key points) ▼

SELECT   ship_id, status, shipped_at, dest
FROM     shipments
WHERE    status = 'shipped'
  AND    shipped_at >= '2026-06-01'
ORDER BY shipped_at;

LEGEND ▼

Rows read / loaded

1. Source table — two conditions: status and shipped_at ▼

FROM shipmentsThere are two filtering conditions—status (equality) and shipped_at (range)—plus a sort by shipped_at. The question is whether one index can do all three jobs.

1 / 5

ship_id	status	shipped_at	dest
1	pending	2026-06-02	Tokyo
2	shipped	2026-05-28	Osaka
3	shipped	2026-06-01	Tokyo
4	delivered	2026-06-03	Nagoya
5	shipped	2026-06-04	Sapporo
6	pending	2026-05-30	Fukuoka
7	shipped	2026-06-09	Tokyo
8	delivered	2026-06-05	Kobe

8 rows (2 million actual rows)

LEARNING POINTS

A composite index can be used only continuously from the left: With (status, shipped_at), a query with only WHERE shipped_at >= ... is like looking in a telephone book for everyone named “Taro”: you cannot skip the first column, so the index is barely useful (the leftmost-match principle). Make a habit of reading from the left to understand which WHERE shapes an index serves.

Columns to the right of a range condition lose their sort guarantee: For (A, B, C) with A=equality and B=range, C is ordered only inside each individual B value, so ORDER BY C cannot omit the sort. Use equality → range → sort column order, with at most one range column, as the practical composite-index guideline.

Choose column order by usage, not selectivity: The old rule “put the high-cardinality column first” is less important than putting the column fixed by equality first. Even a low-cardinality column such as status fixes the interval at one point when used with equality, activating the range and sort inside it.

ANTI-PATTERNS

Put up two single-column indexes and call it done: With separate idx(status) and idx(shipped_at) indexes, BitmapAnd may combine them, but the ordering information is lost and the ORDER BY sort returns. For a query that frequently uses “two WHERE conditions + ORDER BY,” one composite index is almost always better.

Miss a direction mismatch in ORDER BY: If the requirement is ORDER BY shipped_at DESC but only an ascending index exists, a B-tree can scan backward and simple cases are fine. But a ORDER BY a ASC, b DESC mixed sort does not match the index order, so Sort returns. If necessary, create an index with explicit direction, (a ASC, b DESC).

Practical note: An index is an investment in query shape

Composite-index design is an investment not in individual queries, but in the WHERE + ORDER BY patterns that the application issues repeatedly. The process is: (1) identify frequent, high-load queries with pg_stat_statements, (2) classify their WHERE columns as equality / range / sort, (3) design an index in the order equality → range (one column) → sort, and (4) confirm with EXPLAIN ANALYZE that the Sort node disappeared. One good composite index can rescue a family of similarly shaped queries (different equality values and different range widths). In Q3, we will apply this “equality → range → LIMIT” scan repeatedly for each group with LATERAL JOIN.

Model answers and explanations are for members

Read every model answer, explanation and table-transition visualization across all 48 advanced sets (240 questions)
One-time purchase — no subscription. Future advanced sets are included
Background, problem and expected output stay free

Unlock for $20 USD

Payments are securely processed by Stripe — your card details never touch this site.

You are charged in US dollars. If your card is issued in another currency, your bank’s exchange rate and any foreign transaction fee apply.

Already purchased? Sign in on your account page.

QUESTION 3

LATERAL JOIN — Fetch the Latest 2 Orders per Customer Directly from the Index

LATERALTop-N per GroupLIMITIndex Scan × N

Background ▼

“The latest N rows per customer” is one of the most common practical requirements, but it is surprisingly difficult to write efficiently. A window function such as ROW_NUMBER() can express it, but when there are few target customers, scanning and sorting every row is excessive. With LATERAL, a subquery can receive each row on the left as an argument, making the minimal scan possible: for each customer, use the index to pick only the latest two orders and stop immediately.

FROM customers c
CROSS JOIN LATERAL (              -- The subquery runs for each row of c
  SELECT ...
  FROM   orders
  WHERE  customer_id = c.customer_id  -- The outer column is visible inside (LATERAL’s privilege)
  ORDER BY ordered_at DESC
  LIMIT  2                       -- Stop at two rows per customer
) o

Where the speed comes from: With a composite index (customer_id, ordered_at DESC), each subquery fixes the customer interval by equality, then reads two rows from the already ordered start and stops (the Q2 application). Even with one million orders, the scan is only customer count × 2 rows.

Problem ▼

From customers (3 rows) and orders (1 million actual rows, with composite index idx_orders_cust_date (customer_id, ordered_at DESC)), retrieve the latest 2 orders for each customer with a LATERAL JOIN. Return customer_id, name, order_id, ordered_at, amount, ordered by customer_id ascending and ordered_at descending.

Source tables

- customers

customer_id	name
101	Sato
102	Suzuki
103	Tanaka

- orders (composite index on (customer_id, ordered_at DESC))

order_id	customer_id	ordered_at	amount
1	101	2026-06-01	1200
2	102	2026-06-02	800
3	101	2026-06-03	3000
4	103	2026-06-04	500
5	101	2026-06-05	900
6	102	2026-06-06	1500
7	103	2026-06-07	700
8	101	2026-06-08	2200
9	102	2026-06-09	600

※ Customer 101 has four orders. The strength of LATERAL + LIMIT is that the two older orders (ids 1 and 3) are not scanned at all.

Expected Output ▼

Expected output (customer_id ascending, ordered_at descending):

customer_id	name	order_id	ordered_at	amount
101	Sato	8	2026-06-08	2200
101	Sato	5	2026-06-05	900
102	Suzuki	9	2026-06-09	600
102	Suzuki	6	2026-06-06	1500
103	Tanaka	7	2026-06-07	700
103	Tanaka	4	2026-06-04	500

Model Answer ▼

SELECT   c.customer_id, c.name,
         o.order_id, o.ordered_at, o.amount
FROM     customers c
CROSS JOIN LATERAL (                       -- Subquery evaluated once for each customer row
  SELECT order_id, ordered_at, amount
  FROM   orders
  WHERE  customer_id = c.customer_id        -- Reference the outer c (correlation)
  ORDER BY ordered_at DESC                  -- Follows the index order
  LIMIT  2                                  -- Stop immediately after reading two rows
) o
ORDER BY c.customer_id, o.ordered_at DESC;

/*
  Logical execution order:
  1. Scan customers                          → 3 rows
  2. For each customer, read two rows from the start of its index interval  → stop at LIMIT
  3. Join the results for the three customers                          → 6 rows
  4. ORDER BY customer_id, ordered_at DESC  → final ordering
  */

Explanation (table transitions & key points) ▼

SELECT   c.customer_id, c.name,
         o.order_id, o.ordered_at, o.amount
FROM     customers c
CROSS JOIN LATERAL (
  SELECT order_id, ordered_at, amount
  FROM   orders
  WHERE  customer_id = c.customer_id
  ORDER BY ordered_at DESC
  LIMIT  2
) o
ORDER BY c.customer_id, o.ordered_at DESC;

LEGEND ▼

Rows read / loaded

1. Source tables — the requirement is “latest 2 per customer” ▼

FROM customers c / ordersA normal JOIN combines all matching rows, but this requirement is “only two per customer.” LATERAL + LIMIT can express this group-level stop. Assume orders has one million actual rows.

1 / 5

order_id	customer_id	ordered_at	amount
1	101	2026-06-01	1200
2	102	2026-06-02	800
3	101	2026-06-03	3000
4	103	2026-06-04	500
5	101	2026-06-05	900
6	102	2026-06-06	1500
7	103	2026-06-07	700
8	101	2026-06-08	2200
9	102	2026-06-09	600

9 orders (1 million actual rows)

LEARNING POINTS

LATERAL is a subquery that can take a row as an argument: A normal subquery in FROM cannot reference outer columns, but adding LATERAL makes outer values such as c.customer_id available inside. Because ORDER BY + LIMIT can run for each row, the essential value is that it expresses “stop per group” at the execution-plan level.

The performance prerequisite is a composite index (group column, sort column): LATERAL is not magic; without an index it repeats “Seq Scan + Sort per customer” in the worst possible form. Only together with the (customer_id, ordered_at DESC) Q2 “equality → ordered interval” design does it achieve the minimal scan of only LIMIT rows from the interval start.

JOIN LATERAL versus LEFT JOIN LATERAL: If customers with no orders must remain in the output, use LEFT JOIN LATERAL (...) o ON true (the order columns are filled with NULL). CROSS JOIN LATERAL means that a customer disappears when the subquery returns zero rows—it has INNER join semantics.

ANTI-PATTERNS

Turn it into N+1 queries with an application loop: Issuing one SELECT per customer in an application loop to get “the latest two per customer” is the classic N+1 problem, where each network round trip accumulates. LATERAL is the SQL-side solution that folds the same “per customer” logic into one query and one round trip.

Use MAX(ordered_at) in a self-join to get the latest one: A JOIN (SELECT customer_id, MAX(ordered_at) ...) approach can duplicate rows when multiple orders have the same timestamp, and it cannot extend to “latest two.” Even for one latest row, LATERAL + LIMIT 1 (or DISTINCT ON) is often more accurate and faster.

Practical note: DISTINCT ON, PostgreSQL’s shortcut

For “only the latest one per group,” PostgreSQL offers the dedicated syntax SELECT DISTINCT ON (customer_id) ... ORDER BY customer_id, ordered_at DESC, which runs quickly with the same composite index. The rule of thumb is: DISTINCT ON for one row, LATERAL + LIMIT for N rows, and window functions for a batch ranking of every group. All three tools solve the same “Top-N per group” problem, so choose based on the requirements (row count, number of target groups, and index availability). In Q4, we will look at the FILTER clause, which folds the aggregation itself into one pass.

Model answers and explanations are for members

Read every model answer, explanation and table-transition visualization across all 48 advanced sets (240 questions)
One-time purchase — no subscription. Future advanced sets are included
Background, problem and expected output stay free

Unlock for $20 USD

Payments are securely processed by Stripe — your card details never touch this site.

You are charged in US dollars. If your card is issued in another currency, your bank’s exchange rate and any foreign transaction fee apply.

Already purchased? Sign in on your account page.

QUESTION 4

FILTER Clause — Fold Multiple Conditional Aggregations into One Scan

FILTERConditional aggregationHAVINGOne-pass aggregation

Background ▼

“For each payment method, report the total count, success count, and sum of failed amounts.” This is a reporting requirement with several aggregations over different conditions. Scanning the table three times and joining the results is the worst approach. PostgreSQL’s FILTER clause lets each aggregate function select its own input rows within one scan.

-- ✗ Repeat scans and join one subquery per condition (3 passes)
SELECT ... FROM (success only) JOIN (failure only) ON ...

-- ✓ FILTER: select rows per aggregate inside one pass
COUNT(*) FILTER (WHERE status = 'ok')   -- Only this COUNT counts ok rows
SUM(amount) FILTER (WHERE status = 'ng') -- Only this SUM adds ng rows

Distinguish the three layers WHERE / FILTER / HAVING: WHERE narrows the rows entering all aggregates (before aggregation; an index can help). FILTER narrows the rows for one aggregate function only. HAVING narrows groups after aggregation. This adds a third, middle layer to the basic set’s Q4 distinction between WHERE and HAVING.

Problem ▼

From the payments table (8 million actual rows), aggregate by payment method in one scan: report the total count cnt, success count ok_cnt (status='ok'), and failed amount total ng_amount (sum of amount where status='ng'; use 0 when there are no matching rows). Return only methods with at least 2 rows, ordered by method ascending.

Source table

- payments

pay_id	method	status	amount
1	card	ok	1200
2	bank	ok	5000
3	card	ng	800
4	card	ok	2400
5	paypay	ok	600
6	bank	ng	3000
7	card	ng	500
8	bank	ok	7000

※ paypay has only one row, so it drops from the final output. The question is which layer (WHERE / FILTER / HAVING) should contain the at-least-two-rows condition.

Expected Output ▼

Expected output (method ascending):

method	cnt	ok_cnt	ng_amount
bank	3	2	3000
card	4	2	1300

The table is scanned once. Three aggregates run alongside one another in the same pass.

Model Answer ▼

SELECT   method,
         COUNT(*)                                  AS cnt,       -- All rows are included
         COUNT(*)    FILTER (WHERE status = 'ok') AS ok_cnt,    -- Count ok rows only
         COALESCE(
           SUM(amount) FILTER (WHERE status = 'ng'), 0
         )                                          AS ng_amount  -- NULL → 0 for a group with no ng rows
FROM     payments
GROUP BY method
HAVING   COUNT(*) >= 2                            -- Put post-aggregation group conditions in HAVING
ORDER BY method;

/*
  Logical execution order:
  1. FROM payments — scan 8 rows once
  2. GROUP BY method — classify into bank / card / paypay (3 groups)
  3. Run three aggregates in parallel inside each group:
     COUNT(*) counts all rows, FILTER(ok) only ok rows, and FILTER(ng) only ng rows
  4. HAVING COUNT(*) >= 2 — exclude paypay (1 row)
  5. SELECT — COALESCE converts NULL for a group with no ng rows to 0
  6. ORDER BY method — sort the two remaining groups
  → Result: 2 rows × 4 columns (one table pass only)
*/

Explanation (table transitions & key points) ▼

SELECT   method,
         COUNT(*) AS cnt,
         COUNT(*) FILTER (WHERE status = 'ok') AS ok_cnt,
         COALESCE(SUM(amount) FILTER (WHERE status = 'ng'), 0) AS ng_amount
FROM     payments
GROUP BY method
HAVING   COUNT(*) >= 2
ORDER BY method;

LEGEND ▼

Rows read / loaded

1. FROM payments — this is the only scan ▼

FROM paymentsThe requirement mixes three different input ranges: all rows, ok rows only, and ng rows only. Should an 8-million-row table be read three times, or once? This is where FILTER belongs.

1 / 5

pay_id	method	status	amount
1	card	ok	1200
2	bank	ok	5000
3	card	ng	800
4	card	ok	2400
5	paypay	ok	600
6	bank	ng	3000
7	card	ng	500
8	bank	ok	7000

8 rows (8 million actual rows)

LEARNING POINTS

FILTER is a private WHERE for one aggregate function: WHERE is a common filter for all aggregates, while FILTER applies to only the one aggregate function it is attached to. This lets one query and one pass contain aggregates over different input ranges. COUNT(*) FILTER (...) is more readable in intent and handles NULLs more naturally than SUM(CASE WHEN ... THEN 1 ELSE 0 END).

Promote conditions common to every aggregate into WHERE: If every aggregate needs only rows where status IN ('ok','ng'), write that condition once in WHERE instead of distributing it among FILTER clauses. WHERE reduces the row count before aggregation and can use an index, so conditions are cheaper when moved outward. Always ask which condition can be pushed to the outermost layer.

SUM over zero matching rows returns NULL, not 0: When FILTER finds no input rows for a group, SUM returns NULL (COUNT returns 0). In reports, the standard practice is to explicitly convert it with COALESCE(..., 0). Letting NULL flow into later calculations such as differences or ratios can silently turn the entire result into NULL.

ANTI-PATTERNS

Build one subquery per condition and join them: Making separate “success aggregate” and “failure aggregate” subqueries and joining them by method scans the same huge table once per condition. It also creates a correctness trap: an INNER JOIN can erase a method that exists on only one side. FILTER solves both with one pass while retaining every method.

Force a HAVING condition into WHERE, or vice versa: WHERE COUNT(*) >= 2 is an error because the aggregate value does not exist at the WHERE stage. Conversely, HAVING status = 'ok' is an error or ambiguous SQL unless status is added to GROUP BY. Classify conditions mechanically by asking whether a row or a group determines them.

Practical note: Pivot reports and FILTER

FILTER shows its value in pivot (crosstab) reports. A requirement such as a monthly × status count table can be laid out horizontally with COUNT(*) FILTER (WHERE status='ok') AS ok, COUNT(*) FILTER (WHERE status='ng') AS ng, ..., completing the table in one pass. In preprocessing SQL for BI tools and daily batch aggregation, make a habit of asking “Are we reading the same table repeatedly?” and you will find more and more places where FILTER can fold the work together. In Q5, we will study partial indexes, which encode on the index side the fact that aggregation and queue workloads often target only a tiny part of a table.

Model answers and explanations are for members

Read every model answer, explanation and table-transition visualization across all 48 advanced sets (240 questions)
One-time purchase — no subscription. Future advanced sets are included
Background, problem and expected output stay free

Unlock for $20 USD

Payments are securely processed by Stripe — your card details never touch this site.

You are charged in US dollars. If your card is issued in another currency, your bank’s exchange rate and any foreign transaction fee apply.

Already purchased? Sign in on your account page.

QUESTION 5

Partial Indexes — Index Only the “Unprocessed 1%” for a Small, Fast Structure

Partial indexIndex with WHERELIMITQueue processing

Background ▼

In tables such as job queues and order statuses, the search target can become a tiny “unprocessed (pending)” minority while 99% of rows are already processed. A normal index on every row keeps paying size and update costs for that 99%. A partial index, written as CREATE INDEX ... WHERE condition, indexes only the rows matching the condition and stays small.

-- Index only pending rows (1% of the total)
CREATE INDEX idx_tasks_pending
  ON tasks (created_at)
  WHERE status = 'pending';   -- ← This predicate defines the index’s coverage

When it is used: A partial index is chosen only when the query’s WHERE implies the index predicate. Writing WHERE status = 'pending' enables it, while WHERE status IN ('pending', 'done') does not. The planner checks whether “the index predicate ⊇ the query’s restriction” holds.

Problem ▼

For the tasks table (1 million actual rows, with about 1% having status = 'pending'): (1) create a partial index on pending rows ordered by created_at, and (2) use it to retrieve the 3 oldest pending tasks. Return task_id, created_at, title.

Source table

- tasks (about 1% pending out of 1 million rows)

task_id	status	created_at	title
1	done	2026-05-01	Create invoice
2	pending	2026-05-03	Inventory count
3	done	2026-05-04	Monthly report
4	pending	2026-05-06	Contract review
5	canceled	2026-05-08	Legacy site update
6	pending	2026-05-10	Prepare audit materials
7	done	2026-05-12	Schedule interviews
8	pending	2026-05-13	Price change notice

※ A normal index (status, created_at) also works, but consider the difference in size and update cost compared with a partial index.

Expected Output ▼

Expected output (created_at ascending, 3 rows):

task_id	created_at	title
2	2026-05-03	Inventory count
4	2026-05-06	Contract review
6	2026-05-10	Prepare audit materials

The index contains only about 10,000 pending rows, and LIMIT ends the scan after its first three entries.

Model Answer ▼

CREATE INDEX idx_tasks_pending  -- (1) Partial index containing pending rows only
  ON tasks (created_at)
  WHERE status = 'pending';     -- Only about 1% of all rows are indexed

-- (2) The 3 oldest pending tasks
SELECT   task_id, created_at, title
FROM     tasks
WHERE    status = 'pending'        -- Matches the index predicate → partial index is chosen
ORDER BY created_at                 -- Follows the index order
LIMIT    3;                         -- Stop scanning after the first 3 entries

/*
  Logical execution order:
  1. WHERE satisfies the index predicate  → choose idx_tasks_pending
  2. Scan from the start of the index     → oldest pending first
  3. LIMIT 3 ends the scan                → entries 4 and later are not scanned
  4. Fetch the selected columns from the table  → 3 rows
  */

Explanation (table transitions & key points) ▼

CREATE INDEX idx_tasks_pending
  ON tasks (created_at)
  WHERE status = 'pending';

SELECT   task_id, created_at, title
FROM     tasks
WHERE    status = 'pending'
ORDER BY created_at
LIMIT    3;

LEGEND ▼

Rows read / loaded

Excluded / hidden data

1. Source table — the search always targets only the pending 1% ▼

FROM tasks (1 million rows)This is the classic queue-table skew: rows accumulate, but the query almost always wants only the unprocessed subset. The 990,000 done or canceled rows are rarely search targets.

1 / 5

task_id	status	created_at	title
1	done	2026-05-01	Create invoice
2	pending	2026-05-03	Inventory count
3	done	2026-05-04	Monthly report
4	pending	2026-05-06	Contract review
5	canceled	2026-05-08	Legacy site update
6	pending	2026-05-10	Prepare audit materials
7	done	2026-05-12	Schedule interviews
8	pending	2026-05-13	Price change notice

8 rows (1 million actual rows / about 10,000 pending)

LEARNING POINTS

Selection depends on predicate implication: The planner mechanically checks whether every row satisfying the query WHERE must also satisfy the index predicate. status = 'pending' is OK, while status IN ('pending','done') or a bind variable status = $1 (unknown until execution) is not. Design the predicate around conditions that can be written with fixed literals.

Smallness directly becomes speed: The benefit of a partial index is not limited to scan speed. (1) Small size → almost always cached, (2) updates outside the predicate do not update the index → lighter writes, and (3) a shallower B-tree → shorter searches. Making both reads and writes lighter is unusual among index optimizations.

A partial UNIQUE index is another application: A partial index can be combined with a UNIQUE constraint. CREATE UNIQUE INDEX ... ON users (email) WHERE deleted_at IS NULL implements the business rule “email addresses are unique only among users who have not been logically deleted,” which an ordinary UNIQUE constraint cannot express. This is a representative practical use of partial indexes.

ANTI-PATTERNS

Let the predicate and query condition drift apart: If an application change expands the search from status = 'pending' to status IN ('pending','retrying'), the partial index quietly stops being used and degrades to a Seq Scan. Once you create a partial index, document its predicate as part of the query contract (comments or tests) and include it in regular EXPLAIN checks.

Create a partial index on a frequently changing condition: A predicate such as WHERE updated_at > '2026-01-01' has meaning that changes over time, so its coverage drifts from reality from the moment it is created. Restrict partial-index predicates to discrete, stable states such as status.

Practical note: Five advanced principles—align the “shape” with the index

The theme running through Q1–Q5 of the advanced set is aligning the “shape” of the query and data skew with the shape the index can read. 1. Pass period conditions as half-open intervals with the column raw (Q1); 2. design composite indexes in equality → range → sort column order (Q2); 3. for “N per group,” read only the interval start with LATERAL + LIMIT (Q3); 4. fold aggregates over different input ranges into one pass with FILTER (Q4); 5. embed fixed restrictions in a partial index (Q5). Together with the basic set’s five principles (align types / prefix matching / decompose OR / WHERE vs HAVING / covering indexes), the criteria for tuning SELECT statements become the process of looking at EXPLAIN and finding which principle is being violated. Complete the advanced set by building the habit of testing every hypothesis with EXPLAIN (ANALYZE, BUFFERS) on real data.

Model answers and explanations are for members

Read every model answer, explanation and table-transition visualization across all 48 advanced sets (240 questions)
One-time purchase — no subscription. Future advanced sets are included
Background, problem and expected output stay free

Unlock for $20 USD

Payments are securely processed by Stripe — your card details never touch this site.

You are charged in US dollars. If your card is issued in another currency, your bank’s exchange rate and any foreign transaction fee apply.

Already purchased? Sign in on your account page.