A B-tree index is ordered lexically from the first character. Therefore, a prefix match such as LIKE 'KB-%' can be converted internally into the range sku >= 'KB-' AND sku < 'KB.' and use an Index Range Scan. By contrast, LIKE '%KB' (suffix match) and LIKE '%KB%' (substring match) have no known starting point, so the database must evaluate every row.

-- ✓ Prefix match: convertible to a range scan
WHERE sku LIKE 'KB-%'       -- sku >= 'KB-' AND sku < 'KB.'

-- ✗ Substring or suffix match: no starting point, so evaluate every row
WHERE name LIKE '%Keyboard%'  -- Seq Scan; consider pg_trgm

The sku column in the 500,000-row products table has an index named idx_products_sku. Retrieve keyboard products whose SKU begins with 'KB-' in a form that supports an Index Range Scan, ordered by sku ascending. Return product_id, sku, name.

Also compare the work required by name LIKE '%Keyboard%', which asks whether the name contains “Keyboard.”

Expected output (sku ascending):

A prefix match reads only one contiguous interval of the index. Because index order already matches ascending sku order, no additional sort is required.

SELECT   product_id, sku, name
FROM     products
WHERE    sku LIKE 'KB-%'      -- Prefix match becomes an index range
ORDER BY sku;                   -- Same order as the index; no extra sort

/*
  Execution order:
  1. Convert LIKE 'KB-%' into an internal range condition
  2. Binary-search the B-tree to reach the beginning of the interval
  3. Stop when the prefix changes, after scanning only the matching interval
  4. Return rows in index order without a separate sort
  */

An OR on the same column, such as WHERE a = 1 OR a = 2, can be folded into IN (1, 2) and handled by one index. The difficult case is an OR across different columns, such as WHERE a = 1 OR b = 2. A single index cannot determine one interval to scan. There are two remedies: let the planner combine two indexes with Bitmap Index Scan (BitmapOr), or explicitly split the predicate into two index-friendly queries with UNION.

-- Cross-column OR: one index cannot define the complete scan range
WHERE customer_id = 101 OR coupon_id = 7

-- UNION rewrite: each branch can use its own index
SELECT ... WHERE customer_id = 101
UNION                          -- Remove a row matched by both branches
SELECT ... WHERE coupon_id = 7

The one-million-row orders table has separate single-column indexes on customer_id and coupon_id. Retrieve orders for customer 101 or orders that used coupon 7 as a UNION in which each index remains usable. Sort by order_id ascending and return order_id, customer_id, coupon_id, amount.

order_id=4 satisfies both predicates. A straightforward UNION ALL would return it twice.

Expected output (order_id ascending, no duplicates):

SELECT order_id, customer_id, coupon_id, amount
FROM   orders
WHERE  customer_id = 101        -- Branch A: Index Scan on customer_id
UNION                            -- Remove order_id=4, which matches both predicates
SELECT order_id, customer_id, coupon_id, amount
FROM   orders
WHERE  coupon_id = 7             -- Branch B: Index Scan on coupon_id
ORDER BY order_id;               -- Sort the complete UNION result

/*
  Execution order:
  1. Branch A: use idx_customer_id to fetch customer_id = 101
  2. Branch B: use idx_coupon_id to fetch coupon_id = 7
  3. UNION the two sets and remove duplicate rows
  4. Sort the combined result by order_id
  */

SQL's logical execution order is FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY. WHERE filters individual rows before aggregation, can use an index, and reduces the number of rows entering the aggregate. HAVING filters groups after aggregation and is the place for conditions on SUM, COUNT, and other aggregate results. Putting a row-level condition in HAVING creates double work: aggregate all rows first, then discard groups that should never have been built.

-- Logical order and the location of each filter
FROM sales                    -- 1. Read data
WHERE category = 'food'      -- 2. Row filter before aggregation; indexable
GROUP BY store_id             -- 3. Form groups
HAVING SUM(amount) >= 3000   -- 4. Group filter after aggregation

From the five-million-row sales table, aggregate store totals using only sales in the 'food' category, retain stores whose total is at least 3000, and sort by total descending. Put the category filter and the total filter in their correct clauses. Return store_id, total.

Food totals are S1=3300, S2=3400, and S3=1800. Performance depends on whether the two drink rows are removed before aggregation.

Expected output (total descending):

SELECT   store_id,
         SUM(amount) AS total
FROM     sales
WHERE    category = 'food'     -- Remove rows through the index before aggregation
GROUP BY store_id              -- Group only the remaining rows
HAVING   SUM(amount) >= 3000   -- Keep only conditions requiring the aggregate result
ORDER BY total DESC;           -- Sort the aggregate output

/*
  Execution order:
  1. FROM sales                → identify the source table
  2. WHERE category = 'food'   → use the index to retain food rows
  3. GROUP BY store_id         → group by store and compute SUM
  4. HAVING SUM(amount) >= 3000 → remove groups below the threshold
  5. SELECT                    → return store_id and total
  6. ORDER BY total DESC       → sort the result
  */

A normal Index Scan has two stages: (1) locate row positions (TIDs) through the index, then (2) jump to the table heap to retrieve selected column values. The second stage performs random I/O for every matching row and can become surprisingly expensive. If the index itself contains every column required by the query, those heap visits disappear and PostgreSQL can use an Index Only Scan. Such an index is called a covering index because it covers the complete query.

-- PostgreSQL 11+: INCLUDE stores non-key payload columns in leaf entries
CREATE INDEX idx_orders_cust_amt
  ON orders (customer_id) INCLUDE (amount);
       -- search key             payload only; not part of sort order

SELECT customer_id, amount    -- Every required column is in the index
FROM orders WHERE customer_id = 101;   -- Index Only Scan

The one-million-row orders table has a large note text column, making each row wide. Design an index and SELECT statement for the frequent query “list order amounts for one customer, returning only customer_id and amount” so it can run as an Index Only Scan without reading the table heap.

customer_id=101 matches four rows. Even though the query does not use note, a normal Index Scan must visit heap pages containing those wide rows to obtain amount.

Expected output (customer_id = 101):

Success means EXPLAIN reports Index Only Scan using idx_orders_cust_amt and Heap Fetches: 0.

CREATE INDEX idx_orders_cust_amt             -- Cover the search key and returned payload in one index
  ON orders (customer_id) INCLUDE (amount);

SELECT   customer_id, amount  -- Every selected column is available in the index
FROM     orders
WHERE    customer_id = 101;   -- Search key is indexed, enabling Index Only Scan

/*
  Execution order:
  1. Binary-search idx_orders_cust_amt for customer_id = 101
  2. Read amount directly from the matching leaf entries
  3. Confirm visibility through the visibility map without visiting the heap
  */