Query Performance and Tricky Queries

Reading a query plan, conceptually#

Every mainstream database can show its query plan — the strategy it intends to use to execute a query, before actually running it (EXPLAIN in MySQL and PostgreSQL, covered with vendor-specific detail on the indexing pages in this track). Reading one, at a conceptual level, comes down to three questions:

• What access path did it choose for each table? A full scan (reading every row) versus an index lookup (jumping directly to matching rows) is the single biggest thing to check — a full scan on a large table for a highly selective filter is the most common "why is this slow" answer.
• What order did it join tables in, and how? The database picks which table to start from and how to match rows against each subsequent table; a poor join order can mean building a huge intermediate result before it gets filtered down.
• How many rows does it expect at each step, versus how many actually come out? A plan that expects 10 rows after a filter but that filter actually passes 500,000 is a sign the database's statistics are stale, or the query is more expensive than it looks.

The practical discipline is the same across every database: don't guess at why a query is slow — read its plan, find the step doing more work than expected, and fix that step specifically (usually a missing index, or a filter that can't use the index that exists).

Puzzle 1: finding gaps in a sequence#

Problem: an invoice_numbers(id) table should be gapless, but rows were deleted over time. Find every missing id.

The technique: for each row, check whether "the next id up" exists at all — if it doesn't, a gap starts right after this row.

SELECT id + 1 AS gap_starts_at
FROM invoice_numbers t
WHERE NOT EXISTS (
    SELECT 1 FROM invoice_numbers t2 WHERE t2.id = t.id + 1
)
ORDER BY id;

An equivalent version using LEAD() (covered in depth on the window-functions page) instead reports the full extent of each gap in one row, rather than just its starting point:

SELECT id + 1 AS gap_start, next_id - 1 AS gap_end
FROM (
    SELECT id, LEAD(id) OVER (ORDER BY id) AS next_id
    FROM invoice_numbers
) t
WHERE next_id - id > 1;

LEAD(id) fetches the next row's id in order; whenever that's more than 1 greater than the current row's id, every value strictly between them is missing.

Puzzle 2: the second-highest value, without LIMIT#

Problem: find the second-highest product price — but LIMIT/OFFSET isn't allowed (a common interview constraint, meant to test whether the underlying logic is understood rather than just the pagination syntax).

SELECT MAX(price) AS second_highest_price
FROM products
WHERE price < (SELECT MAX(price) FROM products);

The inner query finds the single highest price; the outer query finds the highest price strictly below that. This handles ties correctly for free — if two products share the highest price, this still returns the next genuinely distinct price down, rather than returning the same top price twice the way a naive ORDER BY price DESC LIMIT 1 OFFSET 1 would. For "the Nth highest" more generally (not just the second), DENSE_RANK() from the window-functions page is the more flexible tool, exactly as covered there.

Puzzle 3: finding duplicate rows#

Problem: a newsletter_signups table has no unique constraint on email, and some addresses were accidentally recorded more than once. Find every email that appears more than once.

SELECT email, COUNT(*) AS occurrences
FROM newsletter_signups
GROUP BY email
HAVING COUNT(*) > 1
ORDER BY occurrences DESC;

This is the aggregation-and-grouping page's HAVING in its most common real-world use: group by the column that should have been unique, then keep only the groups that repeated. HAVING, not WHERE, is required here specifically because COUNT(*) doesn't exist until after GROUP BY has run.

Puzzle 4: hierarchical data with a self-join#

Problem: an employees table stores each employee's manager_id, self-referencing the same table. Produce a report showing each manager's name alongside their direct report count, and separately list anyone with no manager at all (the top of the org).

The joins page introduced the self-join mechanics for pairing an employee with their manager; here it's extended into an actual report, aggregating over that same self-join:

SELECT m.name AS manager, COUNT(e.id) AS direct_reports
FROM employees m
LEFT JOIN employees e ON e.manager_id = m.id
GROUP BY m.name
ORDER BY direct_reports DESC;

LEFT JOIN (not INNER JOIN) matters here — an INNER JOIN would silently drop any manager (or employee) with zero direct reports from the result entirely, since there'd be no matching row on the right side to join against.

-- The top of the org: anyone nobody manages upward from
SELECT name FROM employees WHERE manager_id IS NULL;

For walking the entire chain of command at arbitrary depth (not just one level of manager/report), the recursive CTE covered on the subqueries-and-ctes page is the right tool — this self-join only answers "who's this row's direct manager/report," one level at a time.

Common mistakes#

• Off-by-one errors in the gaps puzzle — reporting id instead of id + 1 as the gap's start, or forgetting that the very last row in the table has no "next" row to compare against at all (which is correctly not a gap, just the end of the data).
• Using ORDER BY price DESC LIMIT 1 OFFSET 1 for "second highest" without DISTINCT when ties are possible — two products tied for the highest price makes this return the same top value twice instead of the real second-highest.
• Filtering with WHERE COUNT(*) > 1 instead of HAVING COUNT(*) > 1 when hunting for duplicates — WHERE runs before grouping, so the aggregate doesn't exist yet and the database rejects the query.
• Using INNER JOIN instead of LEFT JOIN for the manager/report count, silently dropping managers with no direct reports (or employees with no manager) from the output instead of showing them with a count of zero or a NULL manager.