As discussed in the basic set, LIMIT 20 OFFSET 1000000 reads and discards one million rows before returning 20, so it gets linearly slower on deeper pages. The advanced solution is Keyset Pagination (cursor-based pagination): remember the key of the last row on the previous page and use a WHERE clause to specify the rows after that position directly. Every page can be retrieved with a single Index Scan, keeping the cost constant.

-- ✗ OFFSET: discarded reads increase with page depth O(offset)
SELECT ... ORDER BY created_at DESC, order_id DESC
LIMIT 3 OFFSET 3;

-- ✓ Keyset: continue directly from the previous page’s last key O(limit)
WHERE (created_at, order_id) < ('2024-06-05', 8)
ORDER BY created_at DESC, order_id DESC LIMIT 3;

Retrieve the second page of a feed ordered by newest first (created_at DESC, order_id DESC, 3 rows per page). The last row on page 1 is (created_at, order_id) = ('2024-06-05', 8). Do not use OFFSET; write it with Keyset Pagination using tuple comparison. Return order_id, created_at, amount.

※ id=7 and id=8 share created_at (2024-06-05). Page 1 is id=10, 9, 8. The tie makes the order_id tie-breaker and tuple comparison essential.

Expected output (page 2 · 3 rows):

id=7 has the same created_at as the cursor, but order_id 7 < 8, so it passes the tuple comparison and is first on page 2.

The basic Top-N question selected the top K rows overall, but in real work the much more common case is “top K rows in each group” (Top-N per Group). A correlated subquery tends to repeat a scan once per group, whereas the window function ROW_NUMBER() needs only one table pass plus sorting within each partition. Window functions cannot be used directly in WHERE, so the standard pattern is to assign ranks in a CTE (or subquery) and filter in the outer query.

-- Standard pattern: rank in a CTE → filter rn <= K outside
WITH ranked AS (
  SELECT *,
         ROW_NUMBER() OVER (
           PARTITION BY category
           ORDER BY amount DESC) AS rn
  FROM orders)
SELECT ... WHERE rn <= 2;

From the orders table, retrieve the top 2 orders by sales amount in each category. When amounts tie, prefer the smaller order_id. Return category, order_id, amount, rn, ordered by category ascending and then rn ascending.

※ Games has a tie: id=7 and 8 both have amount 2500. Without order_id as a tie-breaker in ORDER BY, the ranks can swap between executions and the result is nondeterministic.

Expected output (category ascending · 2 rows per category):

9 rows → 6 rows. Even as the number of groups grows, this can be processed in one pass and avoids the repeated scans of a correlated subquery.

When a one-to-many table is joined, the parent row fans out once per child row. Aggregating after that expansion not only increases the amount of work; joining multiple child tables at once also creates a double-counting bug. The advanced standard pattern is to aggregate the child table in a CTE first, then JOIN the smaller result to the parent. To retain parents with zero children, combine LEFT JOIN with COALESCE.

-- ✗ Aggregate after joining: fold only after rows have fanned out (heavy and error-prone)
SELECT c.name, COUNT(o.order_id), SUM(o.amount)
FROM customers c LEFT JOIN orders o ON ... GROUP BY c.name;

-- ✓ Aggregate before joining: fold the child first, then JOIN the small result
WITH agg AS (SELECT customer_id, COUNT(*) cnt, SUM(amount) total
            FROM orders GROUP BY customer_id)
SELECT ... FROM customers c LEFT JOIN agg ON ...;

From customers and orders, retrieve the order count and total amount for every customer. Aggregate orders in a CTE before joining, and output customers with no orders (Bob) as 0 / 0. Return customer_id, name, order_count, total_amount, ordered by customer_id ascending.

※ Alice = 3 orders / 4500, Carol = 3 orders / 8500, Bob = 0 orders. With INNER JOIN Bob disappears, and COUNT(*) turns Bob into “1 order”, a classic trap.

Expected output (customer_id ascending):

The orders side contributes only 2 rows after aggregation. Even with one million orders, JOIN cost is kept close to “customer count × 1”.

Use WITH RECURSIVE to follow parent-child relationships to arbitrary depths in category hierarchies, organization charts, bills of materials (BOMs), and similar data. The process is: the non-recursive term (seed) creates the initial rows, the recursive term repeatedly runs against the immediately preceding result (the working table), and stops when no new rows appear. Performance management has two key points: use a depth limit (depth guard) to contain tree explosion, and prevent infinite loops with cycle protection.

WITH RECURSIVE tree AS (
  -- Non-recursive term (seed): the one starting row
  SELECT id, name, parent_id, 0 AS depth FROM categories WHERE id = 1
  UNION ALL
  -- Recursive term: find only the children of rows t from the immediately preceding step
  SELECT c.id, c.name, c.parent_id, t.depth + 1
  FROM categories c JOIN tree t ON c.parent_id = t.id
  WHERE t.depth < 2          -- Depth-limit guard
)
SELECT * FROM tree;

From the categories table, retrieve all categories beneath id=1 (Electronics) with their depth. For safety, add a depth guard of depth < 2 (maximum depth 2). Return id, name, depth, ordered by depth ascending and then id ascending.

※ Tree structure: 1 (Electronics) ─ 2 (Computers) ─ 3 (Laptops), and 1 ─ 4 (Audio) ─ 5 (Headphones). id=6 (Books) is a separate tree and is not included.

Expected output (depth ascending → id ascending):

Depth-2 rows are generated, but the guard (t.depth < 2) prevents the next search from using a depth-2 row as its parent, so recursion stops there.