A condition that can use an index is called Sargable (Search ARGument-able). Conversely, wrapping the column on the left side in a function or expression disables the index and falls back to Seq Scan. For LIKE, only a prefix match such as ('abc%') can use an index; a wildcard at both ends ('%abc%') cannot.

-- ✗ Non-Sargable: apply a function to the column → index disabled
WHERE EXTRACT(YEAR FROM created_at) = 2024

-- ✓ Sargable: rewrite as a range condition → Index Range Scan
WHERE created_at >= '2024-01-01'
  AND created_at <  '2025-01-01'

The created_at column in the orders table has a B-tree index. Write a Sargable SQL query that uses the index to retrieve orders created during 2024.

※ The 2024 rows are ids 2–5, four rows. The standard boundary is >= '2024-01-01' AND < '2025-01-01', expressed as “before New Year's Day of the following year.”

Expected output:

The same four rows can come from either an Index Range Scan or a Seq Scan depending on how the condition is written.

SELECT order_id, created_at, amount
FROM   orders
WHERE  created_at >= '2024-01-01'    -- Keep the column bare and compare it with a constant (Sargable)
  AND  created_at <  '2025-01-01'    -- Express the boundary as “before New Year's Day” (half-open range)
ORDER BY order_id;

/*
  Execution order:
  1. Binary-search the index for the start of the range
  2. Follow the leaves to scan continuously until the end boundary
  3. Read the selected columns from the table (or use Index Only Scan)
  4. Sort by order_id
  */

Query cost is proportional to “columns read × rows read.” SELECT * reads every column, increasing disk I/O and network transfer, and it prevents a covering index or Index Only Scan from helping. LIMIT returns only the required number of rows; with an index that already supplies the requested order, it can finish early. Combined with ORDER BY, even when the input must be fully examined, it enables the efficient specialized algorithm called Top-N sort.

-- ✗ Every column and every row: unnecessary I/O and sorting
SELECT * FROM orders ORDER BY amount DESC;

-- ✓ Required columns only + LIMIT: less I/O + Top-N sort
SELECT order_id, amount FROM orders
ORDER BY amount DESC LIMIT 3;

Retrieve the top three orders by amount from orders, returning order_id and amount. Minimize the columns read and limit the number of rows with LIMIT.

※ The top three by descending amount are id=3 (5000), 6 (4200), and 4 (3000). In production, note is assumed to be long TEXT.

Expected output (amount descending, top 3):

Reducing the result to 2 columns × 3 rows makes the transferred result about (2/6 columns) × (3/6 rows) = 1/6 of the original. ORDER BY + LIMIT also enables Top-N sort.

SELECT order_id, amount  -- Explicit columns: only the required 2; preserve I/O, network, and Index Only Scan opportunities
FROM   orders
ORDER BY amount DESC     -- Sort by amount descending
LIMIT    3;              -- Top-N: keep only the top 3; use a heap rather than retaining a full sort

/*
  Execution order (logical SQL evaluation order):
  1. FROM orders              → scan 6 rows
  2. ORDER BY amount DESC     → Top-N sort (keep only the leading rows)
  3. LIMIT 3                  → finalize the retained top 3 (an ordered index could allow early termination)
  4. SELECT order_id, amount  → return only the required 2 columns

  Plan comparison (conceptual):
  ✗ SELECT *  ORDER BY amount DESC;
       → read all 6 columns → fully sort 6 rows → return all 6 rows
  ✓ SELECT order_id, amount ORDER BY amount DESC LIMIT 3;
       → read 2 columns → Top-N sort with heap size 3 → return 3 rows
*/

IN and EXISTS are predicates for checking whether something exists in another table. EXISTS stops evaluating as soon as it finds one match, while IN can be understood as collecting the subquery result and then comparing against it (the optimizer may transform either form in practice). Also, if a subquery contains NULL, NOT IN has the famous trap of making every row evaluate to FALSE/UNKNOWN; NOT EXISTS is safer.

-- ✓ EXISTS: stop after finding one match; safe around NULL
SELECT c.*
FROM   customers c
WHERE  EXISTS (
  SELECT 1 FROM orders o
  WHERE  o.customer_id = c.customer_id
);

From customers and orders, retrieve customers who have placed at least one order. Use a correlated subquery with EXISTS in a NULL-safe form. Return customer_id, name, ordered by customer_id ascending.

Customers with orders are 101 (Alice) and 103 (Carol). 102 and 104 have no orders. Order id=4 has a NULL customer_id.

Expected output (customer_id ascending):

Trying to find customers without orders with NOT IN would return zero rows because of the NULL (the trap is explained below).

SELECT   c.customer_id, c.name
FROM     customers c
WHERE    EXISTS (                       -- Existence check: TRUE if even one matching orders row exists
  SELECT 1                              -- The contents do not matter; conventionally use “1” (minimal)
  FROM   orders o
  WHERE  o.customer_id = c.customer_id  -- Correlation: match the outer c.customer_id (NULL does not match with =)
)
ORDER BY c.customer_id;                 -- Sort by customer_id ascending

/*
  Execution order (logical SQL evaluation order):
  1. FROM customers c      → scan 4 outer rows
  2. Evaluate EXISTS for each c → stop when a match is found
  3. ORDER BY customer_id   → sort ascending

  Why NOT IN is unsafe:
    WHERE customer_id NOT IN (SELECT customer_id FROM orders)
    → the subquery result is {101,101,103,NULL,103}
    → when NOT IN contains NULL, every row becomes UNKNOWN (not TRUE), so the result is 0 rows.
    → with NOT EXISTS, NULL does not match the correlated condition and is safe.
*/

This final quiz in the performance fundamentals set combines aggregation with Top-N, a common production pattern. Build the grain with GROUP BY, then take only the top results with ORDER BY ... LIMIT—this two-stage approach is standard for reporting queries, and its exact form changes I/O, memory, and execution time. We will also organize the differences among DISTINCT and GROUP BY, and COUNT(*) and COUNT(column).

-- Standard pattern: aggregate with GROUP BY, then take the top rows with ORDER BY + LIMIT
SELECT   category, SUM(amount) AS total
FROM     orders
GROUP BY category
ORDER BY total DESC
LIMIT    3;

From orders, retrieve the top 3 categories by total sales and the number of orders in each category. Return category, order_count, total_amount, sorted by total_amount descending and then category ascending for ties.

※ Return the top 3 of the 5 categories by total. Even if the real table has 1 million rows, Top-N sort speed after aggregation depends on the number of categories, which is small.

Expected output (total_amount descending, top 3):

Food (500) and Music (700) are excluded. After aggregation, a Top-3 heap keeps only the leading results.

SELECT   category,
         COUNT(*)    AS order_count,  -- All rows in the group (including NULLs)
         SUM(amount) AS total_amount  -- Total amount in the group
FROM     orders
GROUP BY category                     -- Aggregate at category grain
ORDER BY total_amount DESC, category  -- Total descending; category ascending breaks ties
LIMIT    3;                           -- Top-N: keep only the top 3 (a full sort is unnecessary)

/*
  Execution order (logical SQL evaluation order):
  1. FROM orders          → read 8 rows
  2. GROUP BY category    → aggregate by category
  3. ORDER BY total DESC  → Top-N sort (keep only the leading rows)
  4. LIMIT 3              → finalize the retained top 3

  COUNT variants (reference):
    COUNT(*)                       … all rows (including NULL; often the fastest)
    COUNT(amount)                  … rows where amount is not NULL
    COUNT(DISTINCT customer_id)    … unique customers (requires hash deduplication)
*/