Try It Yourself - SyntaxStudy Editor

HTML / CSS / JS

-- Basic EXPLAIN — shows plan without executing
EXPLAIN SELECT * FROM orders WHERE user_id = 42;

-- EXPLAIN ANALYZE — executes and shows real timings
EXPLAIN ANALYZE
SELECT u.name, COUNT(o.id), SUM(o.total)
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.name;

-- Full options — most useful for diagnosis
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.id, p.name, oi.qty
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
JOIN products    p  ON p.id = oi.product_id
WHERE o.user_id = 42
  AND o.status = 'shipped';

-- Key plan nodes to recognise:
-- Seq Scan         — full table scan, no index used
-- Index Scan       — uses index, fetches heap rows
-- Index Only Scan  — uses covering index, no heap fetch (fastest)
-- Bitmap Heap Scan — combines multiple indexes, then fetches heap
-- Hash Join        — builds hash table of smaller side
-- Merge Join       — both sides sorted, efficient for large sets
-- Nested Loop      — for each outer row, scan inner side

-- Update table statistics (run after large data loads)
ANALYZE orders;
ANALYZE VERBOSE users;