JOIN is an operation that relates two tables, and the planner mainly chooses between two algorithms. Nested Loop searches the inner table once for each row of the driving table (outer table), and is fastest when the outer side is small and the inner side has an index. Hash Join builds a hash table from one side and scans the other side once, making it advantageous for large-scale equality joins.

-- A typical case where the planner chooses Nested Loop (customers small, orders large + indexed)
SELECT *
FROM   customers c      -- ← Driving table (outer: the loop runs once per filtered row)
JOIN   orders o         -- ← Inner table (inner: Index Scan runs for each outer row)
  ON c.customer_id = o.customer_id;

-- Without indexes between large tables, Hash Join is more likely to be selected
SELECT *
FROM   huge_table_A a   -- ← Build the hash table in memory from the smaller side
JOIN   huge_table_B b   -- ← Scan the larger side once and probe the hash table
  ON a.id = b.a_id;

Inner-join customers and orders on customer_id and retrieve a list of each order with its customer name. orders.customer_id has an index. The output columns must be order_id, customer_name, amount, ordered by order_id ascending.

※ Because customers is small and orders.customer_id is indexed, Nested Loop (with customers as the driving table) is the typical planner choice.

Expected output (order_id ascending):

SELECT   o.order_id,
         c.name AS customer_name,
         o.amount
FROM     customers c                     -- Put the smaller side first as the explicit driving table
INNER JOIN orders o                      -- Inner: indexed on customer_id → one jump per loop
       ON o.customer_id = c.customer_id  -- Equality join condition
ORDER BY o.order_id;                     -- Sort the output

/*
  Execution order (Nested Loop plan):
  1. Scan customers one row at a time          → outer = driving table
  2. Index Scan orders for each c              → join through the index
  3. Sort the joined result by order_id        → reorder the result
  */

A scalar subquery in the SELECT clause (a subquery that returns one row and one column) is convenient, but it may be re-evaluated for every outer row, causing the subquery to run N times for N outer rows (the SQL-level N+1 problem). If the engine groups first and then performs a LEFT JOIN, the aggregation is done only once.

-- ✗ Scalar subquery: 4 customers → SUM runs 4 times
SELECT c.customer_id, c.name,
       (SELECT SUM(amount) FROM orders
        WHERE  customer_id = c.customer_id) AS total
FROM   customers c;

-- ✓ Pre-aggregation: SUM runs once (one pass over orders), then LEFT JOIN combines the result

Produce a list containing every customer and that customer's total order amount (0 when the customer has no orders). Use the pre-aggregation + LEFT JOIN form and do not use a scalar subquery. The output columns must be customer_id, name, total, ordered by customer_id ascending.

※ Alice=2500, Carol=3500, while Bob and Dan have no orders → display 0.

Expected output (customer_id ascending):

WITH agg AS (                            -- [Pre-aggregation] calculate SUM once per customer_id
  SELECT   customer_id,
           SUM(amount) AS total
  FROM     orders
  GROUP BY customer_id                   -- Aggregate orders in one pass
)
SELECT   c.customer_id, c.name,
         COALESCE(a.total, 0) AS total   -- [NULL → 0] Fill customers with no orders with 0
FROM     customers c
LEFT JOIN agg a                          -- [LEFT JOIN] keep customers with no orders
       ON a.customer_id = c.customer_id
ORDER BY c.customer_id;

/*
  Execution order:
  1. CTE agg: scan orders in one pass and SUM by customer_id
  2. Read the 4 customer rows (driving table)
  3. LEFT JOIN each c to agg by customer_id
  4. Replace NULL with 0 using COALESCE
  5. Sort by customer_id ascending
  */

A normal ORDER BY ... LIMIT can retrieve only the “top K overall.” To retrieve the “top K per group,” use the window function ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...), number rows within each group, and then filter with WHERE rn <= K. Unlike aggregation, the defining feature of a window function is that it does not collapse rows.

-- Number rows independently within each group (= add a column without collapsing rows)
ROW_NUMBER() OVER (
  PARTITION BY category   -- Reset for each category
  ORDER BY     amount DESC   -- Order within the same category
) AS rn

Retrieve the top two sales orders per category from orders. The output columns must be category, order_id, amount, rn, ordered by category ascending and then rn ascending.

※ Books keeps the top two of its three rows, Toys also keeps its top two, and Food has only two rows, so all of them remain.

Expected output:

WITH ranked AS (                         -- [Numbering] add sales-descending rank as a column within each category
  SELECT category, order_id, amount,
         ROW_NUMBER() OVER (
           PARTITION BY category         -- Reset the row number for each category
           ORDER BY     amount DESC      -- Sales descending within the category
         ) AS rn
  FROM   orders
)
SELECT   category, order_id, amount, rn
FROM     ranked
WHERE    rn <= 2                         -- Keep only the top K=2 in each category
ORDER BY category, rn;                   -- Category ascending, rank ascending within category

/*
  Execution order (SQL logical evaluation order):
  1. FROM orders                  → scan
  2. Window evaluation            → assign numbers with PARTITION+ORDER+ROW_NUMBER
  3. Extract the top rows with WHERE rn  → keep the top two rows per category
  4. ORDER BY category, rn         → reorder for display
  */

The standard LIMIT ... OFFSET ... pagination pattern is fine for shallow pages, but it has the fatal property of becoming linearly slower as OFFSET grows. With OFFSET 10000, it reads and discards the first 10000 rows, then returns the next 20, so the cost grows with page depth. Keyset Pagination (the seek method) instead takes LIMIT rows starting after the last value from the previous page, so its time is constant regardless of page position.

-- ✗ OFFSET method: linearly slower on deep pages
SELECT ... FROM orders ORDER BY order_id LIMIT 20 OFFSET 10000;

-- ✓ Keyset method: fetch directly after the previous page's last order_id
SELECT ... FROM orders
WHERE  order_id > 10000          -- Last order_id on the previous page
ORDER BY order_id LIMIT 20;

Page through orders (1 million rows) in ascending order_id, 20 rows at a time. Assuming “the last order_id on the previous page is 10000”, retrieve the next 20 rows with Keyset Pagination. The output columns must be order_id, customer_id, amount.

※ The target is order_id=10001–10020, 20 rows. It returns the same result as OFFSET 10000, but with a vastly lower scan cost.

Expected output (order_id ascending, 20 rows; 6 shown here as an excerpt):

-- [Keyset Pagination] fetch directly through the index after the previous page's last order_id
SELECT   order_id, customer_id, amount
FROM     orders
WHERE    order_id > 10000              -- Last order_id on the previous page (retained by the client)
ORDER BY order_id                       -- Same order as the index → no extra sort
LIMIT    20;                            -- Stop after 20 rows

/*
  Execution order (planner behavior):
  1. Reach the leaf immediately after order_id with orders_pkey  → binary-search to the target
  2. Follow the leaves to the right and fetch the required rows                    → stop at the required count
  3. Fetch the SELECT columns from the table              → fetch the columns
  */