SQL Performance¶

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.id, o.status, c.name
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id
WHERE o.status = 'PENDING'
  AND o.created_at >= NOW() - INTERVAL '7 days';

-- B-tree (default): equality and range queries
CREATE INDEX CONCURRENTLY idx_orders_customer_status
    ON orders (customer_id, status)
    WHERE status != 'COMPLETED';  -- partial index excludes majority of rows

-- For LIKE prefix searches
CREATE INDEX idx_products_name_text_pattern
    ON products (name text_pattern_ops);

-- Covering index: avoids heap fetch if all needed columns are in the index
CREATE INDEX idx_orders_covering
    ON orders (customer_id, created_at DESC)
    INCLUDE (status, total);

-- ❌ Cannot use index on created_at — wrapping in LOWER prevents index use
SELECT * FROM orders WHERE LOWER(customer_email) = '[email protected]';

-- ✅ Use a functional index or store the email normalised
CREATE INDEX idx_orders_email_lower ON orders (LOWER(customer_email));

-- ❌ Fetches unnecessary columns, prevents index-only scans
SELECT * FROM orders WHERE customer_id = $1;

-- ✅ Select only what you need
SELECT id, status, total FROM orders WHERE customer_id = $1;

-- ❌ Must scan and discard all preceding rows
SELECT * FROM orders ORDER BY created_at DESC OFFSET 10000 LIMIT 20;

-- ✅ Keyset (cursor) pagination
SELECT * FROM orders
WHERE created_at < :last_seen_cursor
ORDER BY created_at DESC
LIMIT 20;

-- ❌ customer_id is UUID — passing a varchar forces a cast and may skip the index
SELECT * FROM orders WHERE customer_id = '123e4567-e89b-...';

-- ✅ Use the correct parameter type in your application driver
-- (pgx, JDBC, psycopg will handle this correctly when you use typed parameters)