Performance Optimization — Learn LATERAL Top-N, Expression Indexes, and Composite-Index Column Order in Practice

QUESTION 1

Top-N per Group — Eliminate “rows × sorting” with a LATERAL JOIN

LATERALTop-N per groupWindow functionIndex compatibility

Background ▼

Queries such as the latest 3 orders for each user are common in practice, but performance can differ by orders of magnitude depending on how they are written. The key is knowing how to choose among three representative forms based on data volume and index design.

-- [NG-1] Correlated subquery: ORDER BY + LIMIT runs for each outer row (an N+1 derivative)
SELECT u.user_id,
  (SELECT array_agg(o.order_id) FROM (
     SELECT order_id FROM orders
     WHERE user_id = u.user_id ORDER BY ordered_at DESC LIMIT 3) o)
FROM users u;

-- [NG-2] Window function: read all orders, sort by PARTITION, then filter with rn<=3
SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY ordered_at DESC) AS rn
  FROM orders) x WHERE rn <= 3;

-- ✓ LATERAL: read only the first 3 rows from index (user_id, ordered_at DESC) for each user
SELECT u.user_id, o.order_id, o.ordered_at
FROM users u
CROSS JOIN LATERAL (
  SELECT order_id, ordered_at FROM orders
  WHERE user_id = u.user_id ORDER BY ordered_at DESC LIMIT 3) o;

How to choose: When there are few users and orders is huge, use LATERAL (three rows per user from the head of the index); when a full scan is already needed for aggregation, use a window function. “Correlated = always bad” is false: evaluate the combination of the index’s physical layout and LIMIT.

Problem ▼

From users (100,000 rows) and orders (50 million rows, with index (user_id, ordered_at DESC)), retrieve the latest 3 orders for each user. Write it so that it does not scan all of orders and reads at most 3 rows per user. Return user_id, name, order_id, ordered_at (user_id ascending, and ordered_at descending within each user). Users with zero orders may be excluded.

Source tables

- users

user_id	name
1	Sato
2	Suzuki
3	Tanaka

- orders (index: user_id, ordered_at DESC)

order_id	user_id	ordered_at
101	1	2026-05-01
102	1	2026-05-03
103	1	2026-05-05
104	1	2026-05-07
201	2	2026-04-20
202	2	2026-05-02
301	3	2026-05-04
302	3	2026-05-06
303	3	2026-05-08
304	3	2026-05-09
305	3	2026-05-10

※ With the window-function version, scan and sort all 50 million orders. With LATERAL, 100,000 × 3 = 300,000 rows is enough.

Expected Output ▼

Expected output:

user_id	name	order_id	ordered_at
1	Sato	104	2026-05-07
1	Sato	103	2026-05-05
1	Sato	102	2026-05-03
2	Suzuki	202	2026-05-02
2	Suzuki	201	2026-04-20
3	Tanaka	305	2026-05-10
3	Tanaka	304	2026-05-09
3	Tanaka	303	2026-05-08

At most 3 rows per user. The orders read is limited to 3 entries per user.

Model Answer ▼

SELECT    u.user_id, u.name, o.order_id, o.ordered_at
FROM      users u
CROSS JOIN LATERAL (        -- Behaves like a correlated subquery visible from each row of u
  SELECT   order_id, ordered_at
  FROM     orders
  WHERE    user_id = u.user_id  -- Can reference the outer u.user_id (the essence of LATERAL)
  ORDER BY ordered_at DESC    -- Matches the physical order of index (user_id, ordered_at DESC)
  LIMIT    3
) o
ORDER BY u.user_id, o.ordered_at DESC;

/*
  Logical execution order:
  1. FROM users u       → take one outer row at a time
  2. LATERAL subquery   → seek the index by user_id and take at most 3 rows
  3. CROSS JOIN         → join u and o (users with no orders are excluded)
  4. ORDER BY           → user_id ascending, ordered_at descending within user
  */

Explanation (table transitions & key points) ▼

SELECT             u.user_id, u.name, o.order_id, o.ordered_at
FROM               users u
CROSS JOIN LATERAL (
  SELECT   order_id, ordered_at
  FROM     orders
  WHERE    user_id = u.user_id
  ORDER BY ordered_at DESC
  LIMIT    3
) o
ORDER BY u.user_id, o.ordered_at DESC;

LEGEND ▼

Rows read / loaded

1. Target tables — users × orders (index: user_id, ordered_at DESC) ▼

FROM users u, orders oorders is grouped by user_id and has a B-tree index ordered by ordered_at descending within each user_id. This is the key to LATERAL: the “latest N per user” already exists in the physical layout.

1 / 5

user_id	name	(orders count)
1	Sato	4 rows
2	Suzuki	2 rows
3	Tanaka	5 rows

3 users / 11 orders in the sample (actual data: 100,000 × 50 million)

LEARNING POINTS

LATERAL is a “JOIN that permits correlation”: A normal JOIN subquery cannot reference outer columns, but adding LATERAL makes it evaluate independently for each outer row. This lets us write a per-row Top-N such as “LIMIT 3 per user” in JOIN syntax. The cost of reevaluating the subquery for each row is covered by the index, making this a leading solution for Top-N per group.

The break-even point between “rows × N” and “all rows + sort”: When the outer row count is small and the inner population is large (100,000 users versus 50 million orders here), LATERAL wins decisively. Conversely, a window function can be faster when orders also needs aggregation or complex analysis. Compare Index Scan rows × loops with Sort memory/disk usage in EXPLAIN to decide.

Index column order lets you “ride along” with the sort: The core of this question is the composite index column order (user_id, ordered_at DESC). The principle of seeking by equality on the first column and handling a range or sort on the next is covered in detail in advanced Q5, but LATERAL also requires a physical match between the index and ORDER BY before “read 3 and stop” is possible.

ANTI-PATTERNS

Applying a window function “just because”: If you jump to ROW_NUMBER whenever you hear “Top-N per group,” you may scan a huge population and discard it afterward as in this question. The sort volume tends to explode when you cannot filter earlier with WHERE. Make a habit of estimating the ratio between population size and output size first.

Using LATERAL as a “magic word”: If the WHERE / ORDER BY inside the LATERAL subquery is not indexable (wrapped in a function, missing a descending index, or scattered across multiple columns), a Seq Scan can run for every outer row—the worst possible N+1 with 100,000 full scans. Always use EXPLAIN ANALYZE to confirm Index Scan + loops = outer row count.

Practical note: A map for choosing among three forms

For “Top-N per group,” choose the form based on the outer population size / the inner selectivity / the complexity of the analysis required. (A) Correlated subquery: when you only need one simple aggregate value (sum, maximum); the planner may hash it for you. (B) Window function: when you need ranking, cumulative values, or moving averages for the whole group, or when the inner population is much smaller than the outer and reading everything is cheap. (C) LATERAL: when the outer side is small, the inner side is huge, and the index matches ORDER BY for Top-N. The series-wide principle of “one set operation” can be reframed as “use a set operation when one pass is enough; use LATERAL when per-row index locality pays off.” Advanced Q2 moves to a trap in set operations: three-valued logic with NOT IN and NULL.

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

The NOT IN and NULL Trap — One row in a subquery can erase every result

NOT INNULLNOT EXISTSAnti Join

Background ▼

The most dangerous manifestation of the three-valued logic covered in the basic set is a subquery with NOT IN and NULL. x NOT IN (a, b, NULL) is internally x <> a AND x <> b AND x <> NULL. The final comparison is UNKNOWN, so the entire AND expression can never be TRUE—the result is always empty. The query succeeds, and the log says nothing.

-- ✗ If cancellations.user_id contains even one NULL, the result is always empty
SELECT u.* FROM users u
WHERE u.user_id NOT IN (SELECT user_id FROM cancellations);

-- ✓ NOT EXISTS: correlated check for “no matching row”; NULL-safe
SELECT u.* FROM users u
WHERE NOT EXISTS (
  SELECT 1 FROM cancellations c WHERE c.user_id = u.user_id);

-- ✓ Anti Join: LEFT JOIN + IS NULL (often equivalent to NOT EXISTS after planner optimization)
SELECT u.* FROM users u
LEFT JOIN cancellations c ON c.user_id = u.user_id
WHERE c.user_id IS NULL;

Performance too: PostgreSQL converts NOT EXISTS and LEFT JOIN ... IS NULL to an Anti Join. By contrast, NOT IN needs a NULL check for every row to remain NULL-safe, and tends to offer the optimizer fewer opportunities. Correctness and speed point in the same direction.

Problem ▼

Using a cancellation-record table cancellations (some rows may have a NULL user_id), retrieve users who have not cancelled. The query must remain correct when cancellations.user_id contains NULL and must be written in a form that can become an Anti Join. Return user_id, name (user_id ascending).

Source tables

- users

user_id	name
1	Sato
2	Suzuki
3	Tanaka
4	Ito
5	Kato

- cancellations

cancel_id	user_id
91	2
92	4
93	NULL

※ If you write NOT IN, the NULL on cancel_id=93 makes the result 0 rows.

Expected Output ▼

Expected output (user_id ascending):

user_id	name
1	Sato
3	Tanaka
5	Kato

The cancellation record with NULL user_id is ignored as “a cancellation whose owner is unknown.” Among real users, 1, 3, and 5 have no cancellation record.

Model Answer ▼

SELECT    u.user_id, u.name
FROM      users u
WHERE     NOT EXISTS (           -- Set-theoretic expression of “no matching row exists”
  SELECT  1                      -- EXISTS checks only whether a row exists, not its value
  FROM    cancellations c
  WHERE   c.user_id = u.user_id  -- NULL does not equal NULL → the NULL row is naturally ignored
)
ORDER BY u.user_id;

/*
  Logical execution order:
  1. FROM users u        → scan each user
  2. Evaluate NOT EXISTS → exclude a user if a correlated match is found
  3. WHERE passes         → retain users without a cancellation
  4. SELECT               → finalize user_id and name
  5. ORDER BY             → sort by user_id ascending
  */

Explanation (table transitions & key points) ▼

SELECT     u.user_id, u.name
FROM       users u
WHERE      NOT EXISTS (
  SELECT  1
  FROM    cancellations c
  WHERE   c.user_id = u.user_id
)
ORDER BY   u.user_id;

LEGEND ▼

Rows read / loaded

Excluded / hidden data

1. Target tables — users and cancellations (one NULL row) ▼

FROM users u, cancellations ccancellations.user_id is a foreign key to the cancelled user, but NULL often slips into real systems through data-import defects or system integrations. The presence of this one row silently breaks NOT IN.

1 / 4

users.user_id	name	→ cancellations.user_id
1	Sato	—
2	Suzuki	2
3	Tanaka	—
4	Ito	4
5	Kato	—
—	—	NULL (cancel_id=93)

5 users / 3 cancellations (one has user_id=NULL)

LEARNING POINTS

Always suspect NULL in a NOT IN subquery: NOT IN is safe when the column definition has a NOT NULL constraint, but foreign-key targets, history tables, and analysis tables often allow NULL. “There are no NULLs today” can break with tomorrow’s schema change. Using the NULL-safe NOT EXISTS by default reduces incidents.

The three forms are logically equivalent, but the planner treats them differently: NOT IN, NOT EXISTS, and LEFT JOIN ... IS NULL are logically equivalent when NULL is not involved, but their optimization opportunities differ. PostgreSQL readily converts NOT EXISTS and LEFT JOIN ... IS NULL to a Hash Anti Join, while NOT IN often remains a SubPlan because of NULL correction—an advantage in performance as well as correctness.

EXISTS / NOT EXISTS asks only whether a row exists: The result is the same with SELECT 1, SELECT *, or SELECT NULL—the target is row existence, not the value. The planner stops as soon as it finds the first row, which is the essence of a Semi/Anti Join. Avoid forms such as COUNT(*) > 0 that count every row before comparing.

ANTI-PATTERNS

Assuming “0 rows from NOT IN = nobody qualifies”: The worst case is mistaking a zero-row result for a correct “no matches” result when three people should be present. A report can go blank unnoticed and lead to bad business decisions. If you use NOT IN, add WHERE user_id IS NOT NULL on the subquery side, or guarantee a NOT NULL constraint on the column.

Checking existence with COUNT(*): Writing (SELECT COUNT(*) FROM cancellations WHERE user_id = u.user_id) = 0 cannot stop until it has counted all matching rows. EXISTS stops at the first match—typically many orders of magnitude faster. If you only ask “does one exist?”, remember EXISTS.

Practical note: Designing NULL-permitting columns and defensive SQL

This question is also about the partnership between schema design and query defense: adding a NOT NULL constraint to a foreign-key column removes the NOT IN trap, but real business tables often need NULL to represent “undecided” or “unknown.” The principle is “if a column permits NULL, choose operators with that in mind”—use IS DISTINCT FROM for comparisons, NOT EXISTS for existence checks, and LEFT JOIN ... IS NULL for joins. Design query-side defense and schema-side constraints as complementary layers of defense. Advanced Q3 moves to expressions that are correct but cannot use an index: Sargability.

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

Sargability and Expression Indexes — Keep functions on columns from slowing searches

SargableExpression indexFunctional indexIndex Scan

Background ▼

Sargable (Search ARGument ABLE) means that a WHERE condition can be used as an argument for an index scan. The rule is simple: “wrap a column in a function and the index cannot be used.” The moment you write WHERE LOWER(email) = 'a@x.com', the ordinary email index is disabled—because the index is sorted by raw email values, not by LOWERed values.

-- ✗ Wrap the column in a function → ordinary (email) index cannot be used, so Seq Scan
WHERE LOWER(email) = 'user@example.com'

-- ✗ Implicit type conversion also wraps the column (a CAST is inserted behind the scenes)
WHERE created_at::date = '2026-05-01'
WHERE phone_number = 8012345678   -- When phone_number is VARCHAR

-- [OK-A] Expression index: index the expression itself (make the query expression match)
CREATE INDEX idx_users_email_lower ON users (LOWER(email));

-- [OK-B] Rewrite the query so the column remains bare (for example, a half-open interval)
WHERE created_at >= '2026-05-01' AND created_at < '2026-05-02'

How to judge: The query’s WHERE expression and the index expression must be literally the same shape for the planner to use the index. The LOWER(email) expression index works for LOWER(email) = ?, but not for email ILIKE ?—Sargability requires matching expression shapes.

Problem ▼

Implement a case-insensitive email-address search for user registration. The stored email column is VARCHAR and may contain mixed-case values. There are 1 million users and 100 searches per second. Provide both the query and the index; the search query must work for input in any casing, such as email = 'User@Example.COM'. Return user_id, email, name.

Source table

- users (1 million rows, index under consideration)

user_id	email	name
1	sato@example.com	Sato
2	Suzuki@Example.com	Suzuki
3	TANAKA@example.com	Tanaka
4	ito@example.com	Ito
...	... (1 million rows)	...

※ Example search: 'Suzuki@example.COM' → must return user_id=2.

Expected Output ▼

Output when searching for example 'Suzuki@Example.com':

user_id	email	name
2	Suzuki@Example.com	Suzuki

Rows read: 1 (a direct hit through an Index Scan). No full scan of 1 million rows occurs.

Model Answer ▼

CREATE INDEX idx_users_email_lower  -- 1. Expression index: index the expression itself, LOWER(email)
ON users (LOWER(email));

-- 2. Query: make the expression shape match the index and apply LOWER to the input too
SELECT user_id, email, name
FROM   users
WHERE  LOWER(email) = LOWER('Suzuki@Example.com');

/*
  Logical execution order:
  1. Parse             → determine whether the WHERE expression matches the expression index
  2. Choose a plan     → select Index Scan for the equality condition
  3. Search the index  → calculate the input once and binary-search the B-tree
  4. Heap access       → retrieve email / name from the matching row
  5. SELECT            → return the raw email value
  */

Explanation (table transitions & key points) ▼

CREATE INDEX idx_users_email_lower
ON users (LOWER(email));

SELECT user_id, email, name
FROM   users
WHERE  LOWER(email) = LOWER('Suzuki@Example.com');

LEGEND ▼

Rows read / loaded

1. Target table — users.email is stored with mixed casing ▼

FROM usersThe email column stores the original casing entered by the user. We want to preserve that casing for display while making the search case-insensitive—this requirement calls for expression normalization.

1 / 5

user_id	email (raw value)	LOWER(email) (normalized)
1	sato@example.com	sato@example.com
2	Suzuki@Example.com	suzuki@example.com
3	TANAKA@example.com	tanaka@example.com
4	ito@example.com	ito@example.com

4 rows shown out of 1 million

LEARNING POINTS

The core of Sargability is matching expression shapes: The planner does not use magic; it compares expressions at the text level. An expression index on LOWER(email) works with LOWER(email) = ?, but not with email ILIKE ? or email = ? COLLATE "C". Make a habit of using EXPLAIN to confirm that index key expression = query WHERE expression.

Implicit type conversion quietly destroys Sargability: If you write WHERE phone_number = 8012345678 when the column is VARCHAR, PostgreSQL may interpret it as phone_number::numeric = 8012345678, producing the same result as wrapping the column in a function. created_at::date = '2026-05-01' has the same issue. Match the literal to the column type, or rewrite to a half-open interval.

An expression index is not the only option: In PostgreSQL, changing the column to CITEXT (a case-insensitive string type) makes ordinary = searches case-insensitive automatically. Another design is to store a normalized form in a separate materialized column and index that normally. An expression index is useful because it works without changing the existing schema, but remember that the expression is evaluated on writes.

ANTI-PATTERNS

Assuming “the index is there, so we are safe”: Even if you hear “there is an index on email,” a query with LOWER(email) = ... will not use that index at all. When adding an index, always use EXPLAIN (ANALYZE, BUFFERS) to confirm Index Scan / Index Only Scan and inspect Buffers Hit/Read. Completion means proof of use, not proof that the index exists.

Letting the query and index expression shapes drift apart: If production code changes only the query to TRIM(LOWER(email)) or LOWER(email || domain), the expression index quietly stops being used. Design and change the expression index and application query as a pair, ideally with an EXPLAIN regression test in CI.

Practical note: Sargability is a principle, not a spell

Sargability reduces to the simple principle of leaving the column bare and transforming the literal side. LOWER is only the tip of the iceberg: DATE_TRUNC for dates, SUBSTRING for strings, and ROUND for numbers create the same trap. The three rules are: (1) do not apply functions to columns, (2) avoid implicit casts, and (3) when an expression is unavoidable, make an expression index exactly match the query. Add the habit of checking proof of use with EXPLAIN, and you can move beyond the common “the index exists but does not work” problem. Advanced Q4 covers a representative case where an index still does not deliver performance: OFFSET pagination.

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

The OFFSET Trap and Keyset Pagination — Make Page 100 as Fast as Page 1

OFFSETKeyset paginationSeek methodComposite tuple comparison

Background ▼

Writing pagination as OFFSET n LIMIT k is intuitive, but OFFSET means “read and discard.” To retrieve page 100 (OFFSET 9900), the database reads 9,900 qualifying rows, discards them, and finally returns the next 100 rows. The deeper the page, the slower it becomes linearly—this is the Deep Pagination problem.

-- ✗ OFFSET is not “skip”; it is “read and discard”
SELECT * FROM articles
ORDER BY created_at DESC, id DESC
OFFSET 9900 LIMIT 100;
-- Execution cost: read 9,900 + 100 = 10,000 rows (after ordering)

-- ✓ Keyset (seek method): use the index to seek to rows below the previous page’s last key
SELECT * FROM articles
WHERE (created_at, id) < ('2026-05-04 10:00', 501)  -- ← tuple comparison expresses “the next page”
ORDER BY created_at DESC, id DESC
LIMIT 100;
-- Execution cost: 100 rows on every page (only seek from the index head)

What tuple comparison means: (a, b) < (X, Y) is equivalent to a < X OR (a = X AND b < Y). Even when multiple rows have the same created_at, the lexicographic order of (created_at, id) determines one unique next position—this is the core of Keyset.

Problem ▼

articles has 1 million rows and a composite index (created_at DESC, id DESC). Page through it in newest-first order. If the last article on the previous page was (created_at='2026-05-04 10:00', id=501), write the query to retrieve the next 100 rows. It must not use OFFSET and must have the same performance on every page. Return id, title, created_at.

Source table

- articles (1 million rows, index: created_at DESC, id DESC)

id	title	created_at
510	New A	2026-05-04 15:00
505	New B	2026-05-04 12:00
501	Article X	2026-05-04 10:00
499	Article Y	2026-05-04 10:00
498	Article Z	2026-05-04 09:00
490	Older A	2026-05-03 18:00
...	... (1 million rows)	...

※ The article with id=499 has the same created_at but a smaller id than id=501, so it must also be included on the next page.

Expected Output ▼

Expected output (first few rows of the next page):

id	title	created_at
499	Article Y	2026-05-04 10:00
498	Article Z	2026-05-04 09:00
490	Older A	2026-05-03 18:00
...	... (up to 100 rows)	...

Rows read are always at most 100. Page 100 and page 10,000 have the same cost.

Model Answer ▼

SELECT   id, title, created_at
FROM     articles
WHERE    (created_at, id) < ('2026-05-04 10:00', 501)
ORDER BY created_at DESC, id DESC                      -- ↑ Composite tuple comparison: express “the next position” lexicographically
LIMIT    100;                                          -- ↑ Matches the physical order of index (created_at DESC, id DESC)

/*
  Logical execution order:
  1. Decompose the WHERE tuple comparison → convert to a lexicographic condition
  2. Choose Index Scan               → seek on the index
  3. Scan                            → read in physical index order (no sort)
  4. Heap access                     → retrieve id, title, created_at
  5. LIMIT                           → stop (no OFFSET)
  */

Explanation (table transitions & key points) ▼

SELECT   id, title, created_at
FROM     articles
WHERE    (created_at, id) < ('2026-05-04 10:00', 501)
ORDER BY created_at DESC, id DESC
LIMIT    100;

LEGEND ▼

Rows read / loaded

1. Target table — articles and composite index (created_at DESC, id DESC) ▼

FROM articlesThe physical index order is created_at descending, then id descending for ties. It is exactly a list ordered by newest first. Pagination reduces to deciding where to start reading this list.

1 / 5

Index physical order	id	title	created_at
1 (first)	510	New A	2026-05-04 15:00
2	505	New B	2026-05-04 12:00
3	501	Article X (last row of previous page)	2026-05-04 10:00
4	499	Article Y	2026-05-04 10:00
5	498	Article Z	2026-05-04 09:00
6	490	Older A	2026-05-03 18:00

1 million articles (physically ordered through the index)

LEARNING POINTS

OFFSET discards; Keyset seeks: OFFSET n literally reads n + LIMIT qualifying rows and discards the first n. The cost therefore grows linearly with page depth, even when an index exists. Keyset directly expresses the next position in WHERE, so every page costs O(log N) + LIMIT and is unaffected by depth.

Tuple comparison is the language of multi-column lexicographic order: (created_at, id) < (X, Y) writes a verbose OR/AND combination in one line and is easy for the planner to map directly to a composite index’s column order. Match the sort-column tuple to the index column order and use that same tuple in the comparison—once all three align, pagination reduces to “read N rows from the start position.”

The cursor is state: design it with the UI/API: Keyset requires the client to retain the next-page cursor (the previous page’s last key). Infinite scroll and cursor-based APIs (GitHub, Stripe, Twitter, and others) use this approach. Keyset is less suitable when the business UI requires jumping directly to page N or showing a total page count; compromise by limiting OFFSET to shallow pages or storing materialized page numbers.

ANTI-PATTERNS

Writing Keyset with only ORDER BY created_at: If multiple rows share a created_at value, WHERE created_at < ? alone can skip or duplicate rows within the same second. Add keys until the tuple is unique, usually (sort column, primary key). A non-unique pagination tuple only appears to work by chance.

Using different column orders in WHERE, ORDER BY, and the index: If WHERE (a, b) < (X, Y) is paired with ORDER BY b DESC, a DESC and index (a, c), even one mismatch makes seek ineffective and causes a fallback to Sort + Filter. Keyset requires the three-way identity tuple comparison order = ORDER BY order = index order.

Practical note: A decision flow for pagination design

“OFFSET or Keyset?” is a technical choice determined by UI requirements. (1) Infinite scroll / one-way browsing (social timelines, search results) → Keyset: there is no depth limit and the user only moves forward. (2) Page-number UI / arbitrary page jumps (admin screens, the first several dozen search-result pages) → OFFSET is acceptable, but impose a depth limit such as “at most 100 pages.” (3) Hybrid (OFFSET for the first few pages, Keyset deeper down) is also effective. The important point is to design pagination around the user experience constraint, not the database’s convenience. Advanced Q5 returns to the composite-index column order assumed here and goes further, eliminating even heap access with Index-Only Scan.

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

Composite-Index Column Order and INCLUDE — Do not read the heap with Index-Only Scan

Composite indexColumn orderINCLUDEIndex-Only Scan

Background ▼

Composite-index design has three golden rules: (1) put equality-filtered columns first, (2) put range and sort columns next, and (3) put output-only columns in INCLUDE. When these align, the planner can construct the result from the index alone without reading the heap (the table itself)—this is Index-Only Scan. In practice, the I/O reduction can be dramatic.

-- Assumed query: “list titles for pending tasks on or after a date, newest first, 100 rows”
SELECT title FROM tasks
WHERE status = 'pending' AND created_at >= '2026-05-01'
ORDER BY created_at DESC LIMIT 100;

-- [NG-A] Reverse column order: with (created_at, status), status filtering is delayed
CREATE INDEX ... ON tasks (created_at, status);

-- [NG-B] Do not include title: filtering works, but the heap is visited for every row (only Index Scan)
CREATE INDEX ... ON tasks (status, created_at);

-- ✓ Equality → range/sort column order + INCLUDE to keep the output column in the index
CREATE INDEX ... ON tasks (status, created_at DESC) INCLUDE (title);

Difference between INCLUDE and key columns: An INCLUDE column is not used as an index key (it is not a search or sort target), but its value is stored alongside the leaf entry. It is the feature for adding a column that is needed in the output but not for filtering (PostgreSQL 11+).

Problem ▼

For a huge tasks table (50 million rows, with about 1% of status values equal to 'pending'), design one composite index only that lets the following query run as an Index-Only Scan. The query cannot be changed. Aim for EXPLAIN to show Index Only Scan with Heap Fetches: 0.

SELECT title FROM tasks
WHERE  status = 'pending' AND created_at >= '2026-05-01'
ORDER BY created_at DESC
LIMIT 100;

Source table

- tasks (50 million rows / status distribution: 'pending'≈1%, 'done'≈99%)

task_id	status	created_at	title	assignee
1	done	2026-04-01	...	...
2	pending	2026-05-03	Invoice review	Sato
3	pending	2026-05-02	Inventory count	Suzuki
4	done	2026-05-01	...	...
5	pending	2026-05-05	A/B test aggregation	Tanaka

※ Evaluation points: ① column order, ② whether DESC must be specified, ③ use of INCLUDE, and ④ why title is included but not made an ORDER BY key.

Expected Output ▼

Expected EXPLAIN (key points):

Limit  (cost=...) (actual rows=100 loops=1)
  -> Index Only Scan using idx_tasks_pending_covering on tasks
        Index Cond: ((status = 'pending') AND (created_at >= '2026-05-01'))
        Heap Fetches: 0          ← zero heap access
Buffers: shared hit=N read=M (index pages only)

Return 100 rows from only the small index pages, without reading the 50-million-row table heap at all.

Model Answer ▼

CREATE INDEX idx_tasks_pending_covering  -- Design: status (equality) → created_at DESC (range + ORDER BY) → title (included)
ON tasks (status, created_at DESC)
INCLUDE (title);

-- Query (unchanged; shown for reference):
SELECT title FROM tasks
WHERE  status = 'pending' AND created_at >= '2026-05-01'
ORDER BY created_at DESC
LIMIT  100;

/*
  Logical execution order (planner strategy):
  1. Filter equality status='pending' with the leading index column
  2. Within that interval, created_at DESC is already in physical order
  3. ORDER BY created_at DESC is exactly the index order  → no sort
  4. INCLUDE (title) stores title with each index entry
  5. LIMIT 100 stops the scan
  */

Explanation (table transitions & key points) ▼

CREATE INDEX idx_tasks_pending_covering
ON tasks (status, created_at DESC)
INCLUDE (title);

SELECT   title
FROM     tasks
WHERE    status = 'pending' AND created_at >= '2026-05-01'
ORDER BY created_at DESC
LIMIT    100;

LEGEND ▼

Rows read / loaded

Excluded / hidden data

1. Target table — 50 million tasks (status 'pending'≈1%) ▼

FROM tasksThe heap (table body) is in an arbitrary order such as task_id or insertion order. The query needs the latest 100 pending titles—the entire design is about satisfying that requirement with as little I/O as possible.

1 / 5

task_id	status	created_at	title	assignee
1	done	2026-04-01	...	...
2	pending	2026-05-03	Invoice review	Sato
3	pending	2026-05-02	Inventory count	Suzuki
4	done	2026-05-01	...	...
5	pending	2026-05-05	A/B test aggregation	Tanaka

50 million heap rows — about 500,000 are pending

LEARNING POINTS

Golden rule for column order: equality → range/sort → INCLUDE: Composite indexes follow the leftmost-prefix rule (they can only be used continuously from the first column). In principle: (1) put equality-filtered columns first to maximize selectivity, (2) put range and ORDER BY columns next to process them together, and (3) put only output-required columns in INCLUDE. Break this sequence and you get “the index exists but is not useful.”

INCLUDE is not a key column—that is its value: INCLUDE columns are not sorted as B-tree keys. This means (a) the index does not become as wide as a key-heavy design, (b) frequently updated values do not change the index structure, and (c) additional output columns can be carried while preserving a UNIQUE constraint. INCLUDE is the dedicated place for a column that is not used to filter but is needed in the output.

Index-Only Scan requires a fresh Visibility Map: PostgreSQL uses the Visibility Map to decide whether every transaction can see a row. Recently updated rows cannot be certified and require a heap visit, increasing Heap Fetches. VACUUM (especially VACUUM ANALYZE) updates the Visibility Map, so tuning autovacuum matters more on frequently updated tables.

ANTI-PATTERNS

Making a huge composite index by putting everything in key columns: The more key columns you add, the wider the B-tree, higher the write cost, and lower the cache efficiency. The leftmost-prefix rule also means queries that omit the first column cannot use it. Put output-only columns in INCLUDE and leave out columns not used for filtering or ordering. Designing the smallest index per query is usually lighter than packing everything into one.

Expecting reverse order without explicitly considering DESC: PostgreSQL can scan a B-tree in either direction, so an ASC index often supports DESC retrieval. However, with mixed directions across columns, such as ORDER BY a ASC, b DESC, the index must specify (a, b DESC) to avoid a sort. Match the ORDER BY directions and index definition literally when safety matters.

Practical note: Advanced Set 3 in review — from “design an index” to “design it with the query”

The common thread across Q1–Q5 is that the query, data, and index are one design problem. Q1 (LATERAL): physical match between query form and index column order; Q2 (NOT EXISTS): choose an operator that fits data containing NULL; Q3 (expression index): lexical match between query and index expressions; Q4 (Keyset): match tuple comparison, ORDER BY, and index column order; Q5 (INCLUDE): separate the roles of filtering, sorting, and output columns. None is solved by the query or index alone. Together with Basic Set 3’s principles (Sargability, half-open intervals, Semi Join, partial indexes), this gives you the foundation to read EXPLAIN and talk with the planner. The next set moves inside the planner with execution-plan reading, statistics, parallel processing, and partitioning—let’s look at the database engine’s behavior beyond the code.

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.