Optimizes PostgreSQL queries using proven patterns for indexing, joins, CTEs, and pagination. Use when someone has a slow query, needs to read an EXPLAIN plan, wants index strategy advice, is choosing between CTE and subquery, or needs keyset pagination. Covers B-tree/GIN/GiST index selection, join optimization (nested loop vs hash vs merge), CTE vs subquery trade-offs, offset vs keyset pagination, and common SQL anti-patterns with fixes.
Practical patterns for writing efficient PostgreSQL queries and diagnosing performance issues.
Always analyze before optimizing:
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT ... FROM ... WHERE ...;
-- B-tree (default, most common)
CREATE INDEX idx_name ON table(column);
-- Partial index (only index rows matching condition)
CREATE INDEX idx_active ON users(email) WHERE is_active = TRUE;
-- Composite index (multi-column, order matters)
CREATE INDEX idx_comp ON orders(customer_id, created_at DESC);
-- GIN index (for JSONB, arrays, full-text)
CREATE INDEX idx_json ON products USING GIN(metadata);
-- GiST index (for geometric, range, full-text)
CREATE INDEX idx_geo ON locations USING GiST(coordinates);
-- Prefer this (JOIN)
SELECT o.id, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.id;
-- Over this (subquery)
SELECT o.id, (SELECT name FROM customers WHERE id = o.customer_id)
FROM orders o;
-- CTE for complex, multi-reference queries
WITH monthly_revenue AS (
SELECT DATE_TRUNC('month', created_at) AS month,
SUM(amount) AS revenue
FROM orders
WHERE created_at >= NOW() - INTERVAL '1 year'
GROUP BY month
)
SELECT month, revenue,
LAG(revenue) OVER (ORDER BY month) AS prev_revenue,
revenue - LAG(revenue) OVER (ORDER BY month) AS growth
FROM monthly_revenue;
-- Subquery for single-use filters
SELECT * FROM products
WHERE category_id IN (SELECT id FROM categories WHERE is_featured = TRUE);
SELECT * FROM products ORDER BY id LIMIT 20 OFFSET 100;
-- Problem: DB scans 120 rows, discards 100
SELECT * FROM products
WHERE id > :last_seen_id
ORDER BY id LIMIT 20;
-- Always fast: index seek directly to starting point
| Anti-Pattern | Fix |
|---|---|
SELECT * | Select only needed columns |
WHERE col LIKE '%term%' | Use full-text search (tsvector) or trigram index |
WHERE function(col) = value | Create expression index or restructure |
COUNT(*) on large tables | Use pg_class.reltuples for estimates |
NOT IN (subquery) with NULLs | Use NOT EXISTS instead |
| Repeated identical subqueries | Extract to CTE |
| Missing LIMIT on unbounded queries | Always LIMIT, especially in APIs |