Query Execution Plans

Detailed answer

Logic: Optimizer chooses join order, access paths (seek vs scan), and algorithms (hash vs merge join). EXPLAIN/EXPLAIN ANALYZE shows estimated vs actual rows — key for tuning. Complexity notes included in code section when present. Pros: Visual plan reveals missing indexes and bad estimates. Cons: Plans vary by engine; hints are last resort. Core: Optimizer chooses join order, access paths (seek vs scan), and algorithms (hash vs merge join). EXPLAIN/EXPLAIN ANALYZE shows estimated vs actual rows — key for tuning. Real-time example: Sudden seq scan on million-row table traced to outdated stats after bulk load. Pros: Visual plan reveals missing indexes and bad estimates. Cons: Plans vary by engine; hints are last resort. Common mistakes: Tuning without measuring; ignoring rows mismatch (estimate 10, actual 1M). Best practices: ANALYZE/UPDATE STATISTICS after bulk changes; compare before/after cost. Audience level: Mid-level.

Full explanation

Optimizer chooses join order, access paths (seek vs scan), and algorithms (hash vs merge join). EXPLAIN/EXPLAIN ANALYZE shows estimated vs actual rows — key for tuning.

Code example

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT u.name, COUNT(o.id) AS order_count
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE u.created_at >= DATE '2025-01-01'
GROUP BY u.name
HAVING COUNT(o.id) > 5;

Practice code · sql (view only · no execution)

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT u.name, COUNT(o.id) AS order_count
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE u.created_at >= DATE '2025-01-01'
GROUP BY u.name
HAVING COUNT(o.id) > 5;