SQL Performance Tuning — Applied LAG/LEAD and Keyset Paging

QUESTION 6

Expression Indexes — Make “wrapping a column in a function” Sargable again

Expression indexFunctional indexSargableCase-insensitive

Background ▼

In the basic set, we learned to match the literal side to the column's type. In practice, however, some requirements cannot be expressed without wrapping a column in a function. A representative example is case-insensitive search: we want to match the same customer whether the application sends Alice@Example.COM or alice@example.com.
In this situation, a normal idx(email) is powerless against LOWER(email) = ? (the moment the column is wrapped in a function, it violates Sargability). The solution is an “expression index” that indexes the expression itself. If we create an index that preorders the value of the expression LOWER(email), a search using the same expression becomes an equality lookup again.

-- ✗ A normal index dies when LOWER is applied — full table scan
CREATE INDEX idx_users_email ON users (email);
WHERE LOWER(email) = 'alice@example.com'

-- ✓ Index the expression itself → a search using the same expression is Sargable again
CREATE INDEX idx_users_email_lower ON users (LOWER(email));
WHERE LOWER(email) = 'alice@example.com'   -- The expression exactly matches the index

Core idea: “An index cannot be used when a column is wrapped in a function” is true for a normal index. Indexing the wrapped expression preserves the principle while allowing function-wrapped code — Sargability means a form usable for equality or range lookup, and an expression index expands the target of that form to the value of an expression.

Problem ▼

From the users table (1 million actual rows), retrieve members whose email matches 'Alice@Example.COM' case-insensitively. Application input varies in capitalization, and the table data also contains mixed capitalization for historical reasons. Keep the function-wrapped predicate, but make it an index equality lookup (show the required index as well). Return user_id, email (user_id ascending).

Source table

- users (email is varchar, a normal index exists, capitalization varies)

user_id	email
1	alice@example.com
2	bob@example.com
3	ALICE@example.com
4	charlie@example.com
5	aLiCe@Example.Com

Expected Output ▼

user_id	email
1	alice@example.com
3	ALICE@example.com
5	aLiCe@Example.Com

Model Answer ▼

CREATE INDEX idx_users_email_lower ON users (LOWER(email));  -- Expression index: indexes the “value of the expression” LOWER(email)

SELECT   user_id, email
FROM     users
WHERE    LOWER(email) = LOWER('Alice@Example.COM')  -- LOWER on both sides → exactly matches the expression index
ORDER BY user_id;

-- ✗ With CREATE INDEX ON users (email), LOWER(email) = ? normally cannot use the index
-- [Alternative] PostgreSQL also offers CITEXT / ICU collation for case-insensitive comparison itself

/*
  Logical execution order:
  1. FROM: target users
  2. WHERE: LOWER(email) = 'alice@example.com' (the literal side is evaluated once at planning time)
     → retrieve matching rows from the index with an equality lookup on idx_users_email_lower
     → access the heap for only the matching 3 rows
  3. SELECT: user_id, email
  4. ORDER BY: user_id ascending (effectively no cost for 3 rows)
  → Result: 3 rows × 2 columns (no LOWER evaluation for all rows; one index equality lookup)
*/

Explanation (table transitions & key points) ▼

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

SELECT   user_id, email
FROM     users
WHERE    LOWER(email) = LOWER('Alice@Example.COM')
ORDER BY user_id;

LEGEND ▼

Rows read / loaded

1. Target table — email has mixed capitalization, and a normal index cannot save it ▼

FROM users (email varchar, normal idx(email) exists)The application requires case-insensitive email search, while the data has historically inconsistent capitalization. A single email = 'Alice@Example.COM' does not hit all 3 rows, so the conditions would multiply with the number of capitalization variants — we want to normalize with a function, but a normal index cannot read a wrapped column.

1 / 5

user_id	email	LOWER(email) value
1	alice@example.com	alice@example.com
2	bob@example.com	bob@example.com
3	ALICE@example.com	alice@example.com
4	charlie@example.com	charlie@example.com
5	aLiCe@Example.Com	alice@example.com

5 rows (actual table: 1 million rows; normal idx(email) exists)

LEARNING POINTS

Sargability looks at the “shape of the expression”: The essence of Sargability is whether the left side of WHERE matches the index's storage form. A normal index stores the raw column value, so LOWER(email) is rejected as a different shape. An expression index stores the expression value, so LOWER(email) matches and is selected. “Sargable = do not wrap a column in a function” is an approximation; precisely, the WHERE expression and the index expression must have the same shape.

Also write LOWER on the right-hand literal (an IMMUTABLE function): When the query writes LOWER('Alice@Example.COM') on the right, the planner knows that LOWER produces the same output for the same input (IMMUTABLE) and folds it to 'alice@example.com' at planning time. Runtime cost is zero. In contrast, NOW() and locale-dependent functions are not IMMUTABLE, so the planner cannot use them inside an expression index. Functions used in an expression index must be IMMUTABLE.

Expression indexes also work for prefix matching and JSON: By the same principle, an operator class such as (email text_pattern_ops) can index LIKE 'alice%', and an expression index such as ((data->>'status')) can index a JSONB property. Expression indexes are not limited to “wrapping a column in a function”; they are a general tool for every situation where you want to search normalized, extracted, or calculated values.

ANTI-PATTERNS

Keep postponing “fix the type” or “split the column”: The root cause here is that email capitalization was not normalized in the schema. Applying LOWER at insertion time, or adopting the CITEXT type (PostgreSQL) or a case-insensitive collation (SQL Server / Oracle), eliminates the need even for an expression index. Expression indexes are excellent rescue tools for legacy tables that cannot be fixed, but relying on one in a new design borrows against future maintenance.

Create an expression index but use a different expression in the query: Against CREATE INDEX ... (LOWER(email)), WHERE lower( email ) = ? (spacing difference) or WHERE LOWER(TRIM(email)) = ? (an added function) is treated as a different expression and does not use the index. If EXPLAIN does not choose the expression index, first check whether the query expression and index definition are aligned at the expression level. Applying LOWER to the literal side is part of that alignment.

Practical note: Choosing among column casts, function wrappers, and expression indexes

Combining Basic Q6 (move the literal side) with this question (index the expression itself) reveals a Sargability strategy map: (1) when the types only need to match → move the literal side to the column's type; (2) when you want to search a calculated result → add an expression index on the column and write the same expression; (3) when the shape of the stored data is fundamentally wrong → add generated or normalized columns in the schema. The design skill is weighing which layer should solve the problem against the costs of maintenance, writes, and reads. Q7 advances to another standard window-function use case: calculating differences from preceding and following rows with LAG / LEAD.

QUESTION 7

LAG / LEAD and Difference Calculations — One pass with a window instead of a self JOIN

LAG / LEADWindow functionsDay-over-day changeOne pass

Background ▼

In the basic set, we used ROW_NUMBER to obtain “the rank within a group.” In the advanced set, LAG / LEAD lets us reference the value in the preceding or following row within the same partition on the spot. Day-over-day changes, differences from the immediately previous event, and intervals between consecutive events all reduce to this pattern: order rows and look at their neighbors.

Writing the same requirement with a self JOIN or correlated subquery means re-searching for “the previous row” for every row, creating fertile ground for N+1 behavior and fan-out. A window function can retrieve “the previous row” with one table scan — rows flow in partition-key order, while the implementation simply remembers the preceding value.

-- ✗ Find the previous day with a self LEFT JOIN + correlated MAX → per-row search + fan-out risk
SELECT p1.price - p2.price AS diff
FROM price_history p1
LEFT JOIN price_history p2
  ON p2.product_id = p1.product_id
 AND p2.recorded_at = (SELECT MAX(recorded_at) FROM price_history x
                          WHERE x.product_id = p1.product_id AND x.recorded_at < p1.recorded_at)

-- ✓ One pass with LAG; rows remain intact and each row receives its difference
price - LAG(price) OVER (PARTITION BY product_id ORDER BY recorded_at) AS diff

Core idea: LAG(col) returns the value of col in the immediately preceding row in the same partition, according to ORDER BY (the first row of a partition is NULL, or use LAG(col, 1, 0) to specify a default). It does not reference across partition boundaries — this is the part that guarantees correctness at the syntax level, where a self JOIN would be difficult to express.

Problem ▼

From price_history, retrieve each product's daily price with the “difference from the previous day”. The first day for each product may have a NULL difference. Requirements:

Do not use a self JOIN or correlated subquery (do not trigger a search for every row)
Scan the table only once (one pass with a window function)
Do not calculate a difference across products (do not subtract product 2's first day from product 1's last day)

Return product_id, recorded_at, price, diff (product_id, recorded_at ascending).

Source table

- price_history (index on (product_id, recorded_at))

product_id	recorded_at	price
1	2026-06-01	1000
1	2026-06-02	1100
1	2026-06-03	1050
2	2026-06-01	500
2	2026-06-02	520
2	2026-06-03	510

Expected Output ▼

product_id	recorded_at	price	diff
1	2026-06-01	1000	NULL
1	2026-06-02	1100	100
1	2026-06-03	1050	-50
2	2026-06-01	500	NULL
2	2026-06-02	520	20
2	2026-06-03	510	-10

Model Answer ▼

SELECT   product_id,
         recorded_at,
         price,
         price - LAG(price) OVER (PARTITION BY product_id        -- Partition by product
                                  ORDER BY recorded_at) AS diff  -- Retrieve the preceding price
FROM     price_history
ORDER BY product_id, recorded_at;

-- ✗ Find the previous day with a self LEFT JOIN + correlated MAX(recorded_at < ...)
--      → One MAX search per row + fan-out risk from the JOIN + bloated code

-- Note: If you need a moving average, use the same mechanism as follows
-- AVG(price) OVER (PARTITION BY product_id ORDER BY recorded_at
--                  ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS ma7

/*
  Logical execution order:
  1. FROM: scan price_history once
  2. Window: partition by product_id and order by recorded_at
     → matches the order of index (product_id, recorded_at) → no sort step
     → for each row, LAG(price) = price in the immediately preceding row of the same partition (NULL at the partition start)
  3. SELECT: calculate the difference with price - LAG(price)
  4. ORDER BY: output order (already ordered, so no additional cost)
  → Result: same row count with differences (one scan, zero searches)
*/

Explanation (table transitions & key points) ▼

SELECT   product_id,
         recorded_at,
         price,
         price - LAG(price) OVER (PARTITION BY product_id
                                  ORDER BY recorded_at) AS diff
FROM     price_history
ORDER BY product_id, recorded_at;

LEGEND ▼

Rows read / loaded

1. Target table — daily prices held as a time series per product ▼

FROM price_history (index on (product_id, recorded_at))The table contains daily price snapshots for each product. We want to attach the difference from the previous day's price for the same product to every row. Never cross products — that is the role of PARTITION BY.

1 / 5

product_id	recorded_at	price
1	2026-06-01	1000
1	2026-06-02	1100
1	2026-06-03	1050
2	2026-06-01	500
2	2026-06-02	520
2	2026-06-03	510

6 rows (actual table: millions of rows; daily snapshots × product count)

LEARNING POINTS

“Order rows and look at their neighbors” is the specialty of window functions: SQL is fundamentally set-based and normally has no row order, but window functions are an exception that defines an order within a partition and calculates neighboring, preceding/following, and cumulative values in that order. LAG/LEAD (preceding/following values), SUM OVER (cumulative sums), AVG OVER ... ROWS BETWEEN (moving averages), and FIRST/LAST_VALUE (partition edges) can all be written with a self JOIN or correlated subquery, but doing so almost certainly invites N+1 and fan-out.

Align the index order with the window to eliminate Sort: With a composite index (product_id, recorded_at), writing PARTITION BY product_id ORDER BY recorded_at lets the planner use the index order directly as the window's partition and row order — no separate sort step is needed (the same principle as the Sort-zero part of Basic “three zeros”). The intuition that “windows are expensive” applies when the query requires a sort; matching the index order can reduce the cost substantially.

NULL at partition boundaries guarantees “do not cross products”: LAG/LEAD never references across partitions. At the start of a partition it only returns NULL (or a default value). This directly matches the business requirement that the first row has no previous day. The JOIN conditions and WHERE conditions needed in a self JOIN — “same product?” and “older than this row?” — are all folded into the syntax, which makes the power of window functions apparent.

ANTI-PATTERNS

Fetch “previous value, current value, and difference” with three correlated subqueries: If SELECT contains (SELECT price FROM ... < today) AS prev_price, (SELECT MAX(...)) AS prev_date, price - (SELECT ...) AS diff, three correlated subqueries run for every row. That repeatedly searches for the same “immediately preceding row”; LAG establishes the previous value once, after which the difference is an ordinary subtraction.

Omit ORDER BY from the window or forget the tie-breaker: With LAG(price) OVER (PARTITION BY product_id) (no ORDER BY), which row counts as “the preceding row” within a partition is undefined (behavior differs by DBMS and is storage-dependent, so it is not reproducible). If multiple rows can share a date, include a unique key in a tie-breaker such as ORDER BY recorded_at, price_id — the same principle as ROW_NUMBER in Basic Q7 applies directly.

Practical note: Do not push a window-function calculation into ETL

When a business report needs “day-over-day change, month-over-month change, moving average, or cumulative total,” it is tempting to build a derived table in a batch job: populate a diff column daily, calculate cumulative totals monthly, and so on. In many cases, these are calculations that a window function in the query can retrieve quickly and correctly. A derived table is justified only when there is evidence that the same calculation is read so often that precomputation is cheaper; ETL without evidence merely adds stale copies while paying the trade-off in operational load and data freshness. Q8 moves to an advanced form of keyset pagination: paging with a mixed ASC/DESC composite sort.

QUESTION 8

Mixed DESC/ASC Keyset Pagination — Expand to OR when row-value comparison cannot be used

Composite keysetMixed sort directionsOR expansionComposite index

Background ▼

In the basic set, we represented a cursor with row-value (tuple) comparison (a, b) < (x, y) for a sort where every direction was the same, such as ORDER BY published_at DESC, article_id DESC.
Real-world sorts often mix directions: “priority DESC, then due date ASC for ties, then id DESC for remaining ties.” A simple row-value comparison cannot be used here (the lexicographic order of tuple comparison assumes the same direction for every column; once ASC is mixed in, its meaning no longer matches ORDER BY).
The solution is OR expansion: decompose the condition for the “next row” after the cursor column by column, like carrying digits in addition. If the index is ordered as (priority DESC, due_date ASC, task_id DESC), including the directions, the keyset achieves zero sort steps and one seek.

-- ORDER BY priority DESC, due_date ASC, task_id DESC
-- Last row on the previous page: (priority=2, due_date='2026-06-07', task_id=4)

-- ✓ OR expansion: list the per-column conditions in carry order
WHERE priority < 2                                              -- ① Priority decreases
   OR (priority = 2 AND due_date > DATE '2026-06-07')              -- ② Same priority; later date (ASC means >)
   OR (priority = 2 AND due_date = DATE '2026-06-07' AND task_id < 4)   -- ③ If all equal, smaller id (DESC means <)

Core idea: Each OR branch represents the column that determined the ordering position. For a DESC column, select values smaller than the cursor; for an ASC column, select values larger than the cursor — the direction of each ORDER BY column determines the direction of its inequality. When the composite index uses the same directions, the index's “next key” is exactly the head of the next page.

Problem ▼

Display a task list in pages of 2 using ORDER BY priority DESC, due_date ASC, task_id DESC. The last key on the previous page is (priority, due_date, task_id) = (2, '2026-06-07', 4). For this mixed-direction order, use neither OFFSET nor a simple row-value comparison; retrieve the next 2 rows. Include the index definition. Return task_id, title, priority, due_date.

Source table

- tasks (1 million actual rows)

task_id	title	priority	due_date
1	Urgent response A	3	2026-06-10
2	Urgent response B	3	2026-06-15
3	Normal response A	2	2026-06-05
4	Normal response B	2	2026-06-07
5	Normal response C	2	2026-06-07
6	Normal response D	2	2026-06-09
7	Normal response E	2	2026-06-12
8	Low-priority A	1	2026-06-20

Expected Output ▼

task_id	title	priority	due_date
6	Normal response D	2	2026-06-09
7	Normal response E	2	2026-06-12

Model Answer ▼

CREATE INDEX idx_tasks_pri_due_id                        -- Match the directions in the index to eliminate the ORDER BY sort
  ON tasks (priority DESC, due_date ASC, task_id DESC);

SELECT   task_id, title, priority, due_date
FROM     tasks
WHERE    priority < 2                                                     -- ① Priority decreases
   OR    (priority = 2 AND due_date > DATE '2026-06-07')                  -- ② Same priority; later date
   OR    (priority = 2 AND due_date = DATE '2026-06-07' AND task_id < 4)  -- ③ Same priority and date; smaller id
ORDER BY priority DESC, due_date ASC, task_id DESC                        -- Exactly matches the index order
LIMIT    2;

-- ✗ Row-value comparison (priority, due_date, task_id) < (2, '2026-06-07', 4)
--      → Lexicographic order assumes the same direction for every column, so it does not match an ORDER BY that mixes ASC

/*
  Logical execution order:
  1. FROM: target tasks
  2. WHERE: evaluate the OR expansion as index range conditions
  3. ORDER BY priority DESC, due_date ASC, task_id DESC → no additional sort
  4. LIMIT: stop after reading 2 rows (immediate Top-N termination)
  */

Explanation (table transitions & key points) ▼

CREATE INDEX idx_tasks_pri_due_id
  ON tasks (priority DESC, due_date ASC, task_id DESC);

SELECT   task_id, title, priority, due_date
FROM     tasks
WHERE    priority < 2
   OR (priority = 2 AND due_date > DATE '2026-06-07')
   OR (priority = 2 AND due_date = DATE '2026-06-07' AND task_id < 4)
ORDER BY priority DESC, due_date ASC, task_id DESC
LIMIT    2;

LEGEND ▼

Rows read / loaded

1. Target table — priority DESC + due_date ASC + task_id DESC ▼

FROM tasks (three columns with mixed sort directions)Sort by higher priority first, then earlier due date for ties, then larger id for remaining ties. The directions DESC + ASC + DESC are mixed, which is the key to this question. Unlike Basic same-direction sort, a simple row-value comparison cannot be used.

1 / 6

task_id	title	priority	due_date
1	Urgent response A	3	2026-06-10
2	Urgent response B	3	2026-06-15
3	Normal response A	2	2026-06-05
4	Normal response B	2	2026-06-07
5	Normal response C	2	2026-06-07
6	Normal response D	2	2026-06-09
7	Normal response E	2	2026-06-12
8	Low-priority A	1	2026-06-20

8 rows (actual table: 1 million rows) — order is 1,2,3,5,4,6,7,8

LEARNING POINTS

Each OR branch represents the column that determined the position: Read the ORDER BY columns from top to bottom and create one branch for the condition behind the cursor if that column determined the position. An N-column sort produces N branches; only the final branch has “all preceding columns equal + a position on the last column.” The direction is mechanical: DESC means <, ASC means >;, so there is no need to guess.

Matching the directions in a composite index eliminates Sort: The index (priority DESC, due_date ASC, task_id DESC) is stored in that order in a B-tree (supported by PostgreSQL, MySQL, and Oracle). Because it exactly matches ORDER BY, the retrieved rows are already sorted and the planner does not need a sort step. “Indexes only use one direction” is an old assumption; modern RDBMSs routinely use indexes whose directions are explicitly specified.

For the planner, OR expansion is a union of index ranges: The three expanded branches correspond to three continuous key ranges on the index. The planner may use BitmapOr or concatenate three Index Scans; either way, the Top-N behavior is “seek each range, read it continuously, and stop when the total reaches LIMIT.” Three OR branches do not mean three full-table scans — the essence is three seeks plus the required reads.

ANTI-PATTERNS

Use a simple row-value comparison for an ORDER BY with mixed directions: (priority, due_date, task_id) < (2, '06-07', 4) is syntactically valid, but its meaning does not match ORDER BY (tuple comparison is lexicographic in the “<” direction for every column, while this ORDER BY contains ASC). The result silently drifts and appears in production as missing or duplicated pages. Adopt the mechanical rule: mixed directions mean no row-value comparison; write the OR expansion.

Cause a Sort by failing to match index directions: With CREATE INDEX ON tasks (priority, due_date, task_id) (omitted directions mean all ASC) and an ORDER BY beginning with priority DESC ..., reverse scanning may only partly help, or an additional Sort step may appear. MySQL behavior varies with reverse-scan efficiency and mixed-direction ORDER BY, so the safe practice is to create an index whose directions match ORDER BY exactly. Specifying directions is not merely for future optimization; it is necessary to eliminate the sort now.

Practical note: When sort requirements are complex, give each UI its own index

“Different business workflows need different sort orders” and “the priority screen and updated-at screen use different SQL” are natural, not design failures. The correct choice is to maintain separate composite indexes for the separate sort orders; making every screen slow to save a few indexes causes a larger business loss (additional indexes slightly increase write cost, but can produce differences of hundreds of times on reads). Conversely, it is important to have the courage to remove an ORDER BY index that is no longer needed. Add unused-index metrics to monitoring and review them quarterly — that is the operation of a mature team. Q9 advances EXISTS into directly seeking the top N rows of each group with LATERAL JOIN.

QUESTION 9

LATERAL JOIN — Seek each group's top N rows directly in the index

LATERALTop N per groupSemi Join extensionIndex seek

Background ▼

In the basic set, we learned to use EXISTS to decide whether something exists. The advanced form is LATERAL JOIN: a join that executes the right-hand subquery for each left-hand row using that row's value. Its essence becomes clear if you think of it as rewriting a correlated subquery in JOIN form.
Its strength appears in “top N per group.” Basic ROW_NUMBER + WHERE rn <= N assigns a number to every row before filtering, which is disadvantageous when groups contain huge numbers of rows. LATERAL needs only N index seeks per group — its power is dramatic when the number of groups is much smaller than the number of rows within each group.

-- △ Assign row numbers to every row before taking the top 3: 10 million rn assignments
WITH ranked AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY sold_at DESC) AS rn
  FROM products
) SELECT * FROM ranked WHERE rn <= 3;

-- ✓ Only the number of categories × 3 index seeks is needed
SELECT c.*, p.*
FROM   categories c
JOIN LATERAL (
  SELECT ... FROM products WHERE category_id = c.cat_id    -- Reference c's value
  ORDER BY sold_at DESC LIMIT 3
) p ON TRUE;

Core idea: LATERAL is a JOIN that can use a left row's value in the right subquery. The right-hand LIMIT 3 is evaluated independently for each row; with an index on the group key plus sort key, the work ends after a seek and 3 reads. Think of it as extending Basic EXISTS (stop after 1 row = Semi Join) to “read up to N rows.”

Problem ▼

In an e-commerce administration screen, display the “3 latest-sold products in each category.” categories has 10 categories, products has 10 million rows, and an index exists on (category_id, sold_at DESC, product_id DESC). Do not assign row numbers to every row with ROW_NUMBER; write the form that performs only 3 index seeks per category. Return cat_id, category, product_id, product_name, sold_at (cat_id ascending, then sold_at descending within each category).

Source tables

- categories (10 rows; 3 are excerpted here)

cat_id	name
1	Electronics
2	Books
3	Apparel

- products (10 million actual rows; 4 rows each from categories 1 and 2, and 2 from category 3 are excerpted)

product_id	category_id	name	sold_at
101	1	Earbuds	2026-06-10
102	1	Speaker	2026-06-09
103	1	Cable	2026-06-08
104	1	Charger	2026-06-01
201	2	SQL book	2026-06-12
202	2	Novel A	2026-06-11
203	2	Illustrated guide	2026-06-05
204	2	Magazine	2026-06-02
301	3	T-shirt	2026-06-07
302	3	Hat	2026-06-03

Expected Output ▼

cat_id	category	product_id	product_name	sold_at
1	Electronics	101	Earbuds	2026-06-10
1	Electronics	102	Speaker	2026-06-09
1	Electronics	103	Cable	2026-06-08
2	Books	201	SQL book	2026-06-12
2	Books	202	Novel A	2026-06-11
2	Books	203	Illustrated guide	2026-06-05
3	Apparel	301	T-shirt	2026-06-07
3	Apparel	302	Hat	2026-06-03

Model Answer ▼

SELECT   c.cat_id, c.name AS category,
         p.product_id, p.product_name, p.sold_at
FROM     categories c
JOIN LATERAL (
  SELECT   product_id, name AS product_name, sold_at
  FROM     products
  WHERE    category_id = c.cat_id                     -- ① Reference c.cat_id from the left side (correlation)
  ORDER BY sold_at DESC, product_id DESC             -- ② Same order as the index (with tie-breaker)
  LIMIT    3                                          -- ③ Stop after 3 rows in each category
) p ON TRUE                                           -- LATERAL evaluates the right side independently for each left row
ORDER BY c.cat_id, p.sold_at DESC;

-- △ ROW_NUMBER version (scans all 10 million products and assigns row numbers):
--   WITH ranked AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY category_id
--                   ORDER BY sold_at DESC) AS rn FROM products)
--   SELECT ... FROM categories JOIN ranked ON ... WHERE rn <= 3;

/*
  Logical execution order:
  1. FROM: scan categories (10 rows)
  2. JOIN LATERAL: for each c row, evaluate the right subquery with c.cat_id supplied
  3. Join: combine c × up to 3 rows for each category (10 categories × up to 3 = up to 30 rows)
  4. ORDER BY: output order (c.cat_id ascending × sold_at descending)
  */

Explanation (table transitions & key points) ▼

SELECT   c.cat_id, c.name AS category,
         p.product_id, p.product_name, p.sold_at
FROM     categories c
JOIN LATERAL (
  SELECT   product_id, name AS product_name, sold_at
  FROM     products
  WHERE    category_id = c.cat_id
  ORDER BY sold_at DESC, product_id DESC
  LIMIT    3
) p ON TRUE
ORDER BY c.cat_id, p.sold_at DESC;

LEGEND ▼

Rows read / loaded

1. Target tables — retrieve the latest 3 products in each category ▼

FROM categories c (10 rows) + products (10 million rows)The number of categories is small (10), while the number of products is huge (10 million). “Top N per group” can be written with ROW_NUMBER, but numbering every product is excessive. If an index can be probed directly per category, that is the shortest path.

1 / 5

cat_id	name	Example products (10 million actual rows)
1	Electronics	Earbuds / Speaker / Cable / Charger ...
2	Books	SQL book / Novel A / Illustrated guide / Magazine ...
3	Apparel	T-shirt / Hat ...

3 rows (actual categories: 10; products: 10 million)

LEARNING POINTS

LATERAL is the JOIN form of a correlated subquery: Correlated subqueries are written in SELECT or WHERE, but returning multiple columns or rows is unnatural. LATERAL places the same relationship — “use the left value on the right” — in JOIN syntax, where multiple columns, multiple rows, and LIMIT are natural. Remember it as a tool for expressing complex correlations that are awkward as correlated subqueries.

Choose between ROW_NUMBER and LATERAL based on data distribution: The key is the number of groups × the average rows per group. (a) Small total row count / many groups → ROW_NUMBER (one pass over all rows); (b) groups ≪ rows within one group (category:product, user:order, company:employee) → LATERAL (seek × N) can be orders of magnitude faster; (c) one row only in PostgreSQL → DISTINCT ON is the most concise. The decision tree is simple: inspect data skew with EXPLAIN and choose.

Keep categories with no matches using LEFT JOIN LATERAL: In this question, JOIN LATERAL ... ON TRUE means that a category whose right side returns 0 rows disappears from the result. To show categories with no matching products, rewrite it as LEFT JOIN LATERAL ... ON TRUE — the semantics of an outer join apply to LATERAL unchanged. Organizing this as EXISTS (filter the left side) versus LATERAL (keep the left side while retrieving rows from the right) completes the toolbox for existence-related queries.

ANTI-PATTERNS

Use ROW_NUMBER as the “default top-N per group”: In a case such as this, where each group contains a huge number of rows, choosing ROW_NUMBER means numbering and then discarding many times more rows than the final result. ROW_NUMBER is a powerful tool, but it is not a universal remedy. Consider group size when choosing the tool; that perspective can guide the choice before looking at EXPLAIN.

Use an order on the right side of LATERAL that does not match the index: With JOIN LATERAL (SELECT ... WHERE category_id = c.cat_id ORDER BY price DESC LIMIT 3) p ON TRUE and an index that remains (category_id, sold_at DESC), each category requires a sort by price (read every row in the category + sort + LIMIT). LATERAL shows its real value through seek + sequential reads, so always confirm that the WHERE equality columns + ORDER BY columns + directions match the index.

Practical note: LATERAL is a versatile tool for summary screens

Requirements such as “the latest N items for each parent object” in administration screens, dashboards, and notification centers are almost all cleanly expressed with LATERAL: the latest 5 notifications per user, the latest comment per project, the latest 10 metrics per server, or the latest status change per order. The shared pattern is few parent rows × latest N rows per parent, which is where LATERAL + a suitable composite index excels. LEFT JOIN LATERAL naturally keeps parents with no recent events visible. Q10 is the culmination of the advanced set: a support-ticket management screen that combines expression indexes, composite keyset pagination, LATERAL, and partial indexes in one query.

QUESTION 10

Comprehensive Problem — Put four advanced techniques into one support-management dashboard query

ComprehensiveExpression indexLATERALComposite keysetPartial index

Background ▼

As the capstone of the advanced set, we will build a common real-world support-ticket management dashboard: “retrieve open tickets for a specific customer in priority order, with the latest reply for each ticket, using a cursor.” The four advanced techniques plus the partial index from the basic set divide the work cleanly.

① A partial index indexes only the minority with status='open'; ② an expression index makes email search case-insensitive and Sargable; ③ mixed DESC/ASC keyset pagination expands the cursor into OR branches; ④ LATERAL JOIN seeks the latest reply for each ticket directly in the index. The spirit (do not push “order rows and look at the neighbor” processing into ETL; finish it in the query) also connects directly to this LATERAL structure.

-- Overall shape (pseudocode)
SELECT ..., lr.*
FROM   tickets t
JOIN LATERAL (...) lr ON TRUE                          -- ④ Latest reply for each ticket
WHERE  t.status = 'open'                                    -- ① Partial-index implication
  AND  LOWER(t.customer_email) = LOWER(?)                  -- ② Case-insensitive with an expression index
  AND  (priority < ? OR ...)                                -- ③ OR expansion for mixed DESC/ASC cursor
ORDER BY priority DESC, due_date ASC, ticket_id DESC LIMIT ?;

Goal: The four techniques are powerful individually, but real queries reveal their value where multiple concerns overlap. The same table is filtered (expression index), ordered (composite keyset), and enriched with a summary from a related table (LATERAL) — the goal is to develop the design sense to combine these three layers in one query.

Problem ▼

Build a SaaS customer-support screen showing “open tickets for a specific customer (priority order + latest reply)”. Requirements:

Only tickets with customer_email matching 'Alice@Example.com' (case-insensitive) and status = 'open'
Order by priority DESC, due_date ASC, ticket_id DESC (mixed directions)
Join and display the latest reply for each ticket (replied_at DESC, reply_id DESC)
Starting after the previous-page cursor (priority, due_date, ticket_id) = (2, '2026-06-09', 104), retrieve the next page of 2 rows
Do not use OFFSET; show the necessary indexes as well (partial + expression + ordering indexes)

Source tables

- tickets (1 million actual rows; 'open' is about 3%)

ticket_id	customer_email	subject	priority	due_date	status
101	alice@example.com	Order not received	3	2026-06-05	open
102	Alice@Example.com	Refund request	2	2026-06-08	open
103	alice@example.com	Password reset	2	2026-06-08	open
104	alice@example.com	Cancellation request	2	2026-06-09	open
105	alice@example.com	Delivery delay	1	2026-06-15	open
106	bob@example.com	Wrong item	3	2026-06-06	open
107	ALICE@example.com	Damaged item	2	2026-06-11	open
108	alice@example.com	(completed)	2	2026-06-04	closed

- replies

reply_id	ticket_id	replied_at	body
1	101	2026-06-06 09:00	Waiting for a response from logistics
2	102	2026-06-08 14:00	Refund processing will begin
3	103	2026-06-09 10:00	Reset link sent
4	104	2026-06-10 11:00	Cancellation accepted
5	105	2026-06-15 09:00	Delivery scheduled: 06-16
6	107	2026-06-11 16:00	Replacement process started

Expected Output ▼

ticket_id	subject	priority	due_date	last_replied_at	last_message
107	Damaged item	2	2026-06-11	2026-06-11 16:00	Replacement process started
105	Delivery delay	1	2026-06-15	2026-06-15 09:00	Delivery scheduled: 06-16

Model Answer ▼

CREATE INDEX idx_tickets_open_sort                          -- ① Partial index: index only the minority of 'open' rows, including their sort order (Basic Q5 + Advanced Q8)
  ON tickets (priority DESC, due_date ASC, ticket_id DESC)
  WHERE status = 'open';
CREATE INDEX idx_tickets_open_email_lower                   -- ② Partial + expression index: case-insensitive, Sargable email search (Basic Q5 + Advanced Q6)
  ON tickets (LOWER(customer_email))
  WHERE status = 'open';
CREATE INDEX idx_replies_ticket_recent                      -- ③ For retrieving the latest reply from replies (enables Advanced LATERAL)
  ON replies (ticket_id, replied_at DESC, reply_id DESC);

SELECT   t.ticket_id, t.subject, t.priority, t.due_date,
         lr.last_replied_at, lr.last_message
FROM     tickets t
JOIN LATERAL (                                                              -- ④ Latest reply for each ticket (Advanced Q9)
  SELECT   replied_at AS last_replied_at, body AS last_message
  FROM     replies
  WHERE    ticket_id = t.ticket_id
  ORDER BY replied_at DESC, reply_id DESC
  LIMIT    1
) lr ON TRUE
WHERE    t.status = 'open'                                                  -- ① Partial-index implication (string-to-string, Basic Q6)
  AND    LOWER(t.customer_email) = LOWER('Alice@Example.com')                -- ② Sargable through the expression index (Advanced Q6)
  AND    (                                                                    -- ③ Mixed DESC/ASC keyset (Advanced Q8)
            t.priority < 2
         OR (t.priority = 2 AND t.due_date > DATE '2026-06-09')
         OR (t.priority = 2 AND t.due_date = DATE '2026-06-09' AND t.ticket_id < 104)
         )
ORDER BY t.priority DESC, t.due_date ASC, t.ticket_id DESC                    -- Exactly matches the ordering index → no Sort
LIMIT    2;

/*
  Logical execution order:
  1. FROM tickets   → partial index with status='open'
  2. Filter         → equality lookup on LOWER(email) with the expression index
  3. Keyset (OR expansion) → narrow to next-page candidates
  4. ORDER BY       → matches the ordering index, so no sort
  5. LATERAL        → seek the latest reply once per ticket
  6. LIMIT 2        → stop after 2 rows

  “Three zeros” signals for the planner:
    - Zero Seq Scan: partial + expression + keyset indexes protect three layers
    - Zero Sort: the ordering index matches ORDER BY including directions
    - Zero large-loop SubPlan: LATERAL performs one index probe × 2 rows
*/

Explanation (table transitions & key points) ▼

CREATE INDEX idx_tickets_open_sort
  ON tickets (priority DESC, due_date ASC, ticket_id DESC)
  WHERE status = 'open';

CREATE INDEX idx_tickets_open_email_lower
  ON tickets (LOWER(customer_email))
  WHERE status = 'open';

SELECT   t.ticket_id, t.subject, t.priority, t.due_date,
         lr.last_replied_at, lr.last_message
FROM     tickets t
JOIN LATERAL (
  SELECT   replied_at AS last_replied_at, body AS last_message
  FROM     replies
  WHERE    ticket_id = t.ticket_id
  ORDER BY replied_at DESC, reply_id DESC
  LIMIT    1
) lr ON TRUE
WHERE    t.status = 'open'
  AND    LOWER(t.customer_email) = LOWER('Alice@Example.com')
  AND    (
         t.priority < 2
      OR (t.priority = 2 AND t.due_date > DATE '2026-06-09')
      OR (t.priority = 2 AND t.due_date = DATE '2026-06-09' AND t.ticket_id < 104)
       )
ORDER BY t.priority DESC, t.due_date ASC, t.ticket_id DESC
LIMIT    2;

LEGEND ▼

Rows read / loaded

Excluded / hidden data

1. Raw data — 1 million tickets ('open' is about 3%) + replies ▼

FROM tickets (protected by partial + expression + ordering indexes)Of 1 million tickets, about 3% are open. The target is further limited to customers matching alice@example.com case-insensitively — the challenge is keeping every filter Sargable. replies is a many-to-one related table.

1 / 6

ticket_id	customer_email	subject	priority	due_date	status
101	alice@example.com	Order not received	3	2026-06-05	open
102	Alice@Example.com	Refund request	2	2026-06-08	open
103	alice@example.com	Password reset	2	2026-06-08	open
104	alice@example.com	Cancellation request	2	2026-06-09	open
105	alice@example.com	Delivery delay	1	2026-06-15	open
106	bob@example.com	Wrong item	3	2026-06-06	open
107	ALICE@example.com	Damaged item	2	2026-06-11	open
108	alice@example.com	(completed)	2	2026-06-04	closed

8 rows (actual table: 1 million rows; 6 Alice-related open tickets; tie-breakers included)

LEARNING POINTS

The four advanced techniques operate on different layers: An expression index keeps a predicate Sargable while the code wraps a function (language); LAG/LEAD and LATERAL look at neighbors / retrieve N rows from each group (set operations); mixed-direction keyset pagination chooses inequalities by direction and cuts a page (API contract); and a partial index physically reduces the rows read (storage). Because each works at a separate layer, combining them does not create conflict; it makes the query faster through multiplication. This is the advanced version of Basic structure.

Do not fear multiple indexes on the same table: This question places two partial indexes on tickets (one for ordering and one for the expression). In a modern RDBMS, the correct approach is to maintain the best index separately for each query requirement, letting the planner choose (if the email condition is highly selective, the expression index leads; if filtering is weak, the ordering index may lead). Even when multiple indexes look redundant, they are different tools for different read scenarios and can return more value than their write cost.

Reconfirm the “three zeros” with EXPLAIN: The comprehensive query also maintains Basic (1) zero Seq Scan, (2) zero Sort nodes, and (3) zero large-loop SubPlans. For the added requirements — case-insensitive matching, mixed-direction sorting, and summary joins — the corresponding answers are an expression index, a direction-aware composite index, and LATERAL plus a supporting index. This provides a practical checklist for EXPLAIN ANALYZE.

ANTI-PATTERNS

Make the query look clean but give up on the indexes: Even if you apply the advanced techniques in SQL, the query returns to full scans when the indexes do not reflect the requirements. “LOWER without an expression index,” “a composite index with mismatched directions,” and “no supporting index for the right side of LATERAL” are all failures the planner cannot fix by looking at SQL style alone. Treat the query and indexes as a paired design artifact and review both together.

Start with “ROW_NUMBER by default” or “DISTINCT by default”: Frequent requirements such as “latest one,” “deduplicate,” and “top N” have multiple tools (ROW_NUMBER / LATERAL / DISTINCT ON / EXISTS / Anti Join), and the best choice depends on data distribution. “Write it with ROW_NUMBER first and fix it later if it is slow” creates expensive rework. Inspect data skew before writing the query; estimate group count, rows per group, and the expected rows after filtering, then choose the tool.

Practical note: Advanced Set 3 complete — a v2 map of SQL that is fast, correct, and readable

Across Advanced Set 3, we acquired four new tools: make function wrappers Sargable with expression indexes (Q6), look at neighbors with LAG/LEAD (Q7), expand mixed-direction keysets into OR branches (Q8), seek each group's Top-N with LATERAL (Q9), and design how to combine them in one production query (Q10). The underlying principles are index the expression itself when a column must be wrapped, look at neighbors without collapsing rows, match the index to the ORDER BY directions, and make per-group correlation stop through LATERAL + an index. Combined with the basic principles — communicate intent, do not wrap columns in functions, use one set operation, and stop Top-N early — these techniques preserve the “three zeros” even under more complex requirements.
The next skills are reading real EXPLAIN ANALYZE output and understanding storage and execution-plan topics such as partitioning, materialized views, and parallel execution; those belong to the intermediate set. Well done completing the advanced set.