Query Optimization

-- Index only unshipped orders (smaller, faster)
CREATE INDEX idx_orders_pending
    ON orders (customer_id, created_at)
    WHERE status = 'pending';

-- Index only active users
CREATE INDEX idx_users_active_email
    ON users (email)
    WHERE is_active = TRUE;

-- Index for common filter combination
CREATE INDEX idx_orders_high_value
    ON orders (customer_id)
    WHERE total > 10000;

-- This query will use the partial index:
SELECT * FROM orders
WHERE status = 'pending' AND customer_id = 42
ORDER BY created_at;

-- This query will NOT use the partial index (status filter missing):
SELECT * FROM orders
WHERE customer_id = 42
ORDER BY created_at;

-- Case-insensitive email lookup
CREATE INDEX idx_users_lower_email ON users (LOWER(email));
SELECT * FROM users WHERE LOWER(email) = 'alice@example.com';

-- JSONB field extraction
CREATE INDEX idx_events_type ON events ((payload ->> 'event_type'));
SELECT * FROM events WHERE payload ->> 'event_type' = 'purchase';

-- Common computation
CREATE INDEX idx_orders_monthly ON orders (DATE_TRUNC('month', order_date));
SELECT * FROM orders WHERE DATE_TRUNC('month', order_date) = '2024-01-01';

-- View current autovacuum settings
SELECT name, setting, unit FROM pg_settings WHERE name LIKE 'autovacuum%';

-- Per-table autovacuum tuning
ALTER TABLE orders SET (
    autovacuum_vacuum_scale_factor = 0.05,     -- vacuum after 5% of rows change (default 20%)
    autovacuum_analyze_scale_factor = 0.02,    -- analyze after 2% of rows change (default 10%)
    autovacuum_vacuum_cost_delay = 10,         -- sleep 10ms between cost batches (default 2ms)
    autovacuum_vacuum_cost_limit = 500         -- cost limit per vacuum run (default 200)
);

-- For append-only tables (no updates/deletes):
ALTER TABLE measurements SET (
    autovacuum_vacuum_scale_factor = 0.0,      -- disable vacuum-based trigger
    autovacuum_analyze_scale_factor = 0.01     -- analyze after 1% of rows inserted
);

-- For high-write tables:
ALTER TABLE events SET (
    autovacuum_vacuum_scale_factor = 0.01,
    autovacuum_analyze_scale_factor = 0.005,
    autovacuum_vacuum_cost_delay = 5,
    autovacuum_max_workers = 3
);

-- Tables most needing vacuum (by dead tuple count)
SELECT relname, n_live_tup, n_dead_tup,
       ROUND(n_dead_tup::NUMERIC / NULLIF(n_live_tup, 0) * 100, 2) AS dead_pct,
       last_autovacuum, last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

-- Currently running autovacuum workers
SELECT pid, relname, phase,
       EXTRACT(EPOCH FROM (now() - query_start)) AS elapsed_seconds
FROM pg_stat_progress_vacuum v
JOIN pg_stat_activity a ON a.pid = v.pid;

-- Enable (add to shared_preload_libraries and restart)
-- shared_preload_libraries = 'pg_stat_statements'
-- pg_stat_statements.track = all

-- Top 10 queries by total execution time
SELECT query, calls, total_exec_time / 1000 AS total_ms,
       mean_exec_time / 1000 AS avg_ms,
       rows, shared_blks_hit, shared_blks_read
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- Top 10 queries by average execution time
SELECT query, calls, mean_exec_time / 1000 AS avg_ms
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

-- Top 10 queries by rows (potential N+1)
SELECT query, calls, rows,
       rows::FLOAT / NULLIF(calls, 0) AS avg_rows
FROM pg_stat_statements
ORDER BY rows DESC
LIMIT 10;

-- Reset statistics
SELECT pg_stat_statements_reset();

-- Anti-pattern: N+1 queries (one query per order to get customer)
SELECT * FROM orders;  -- 1 query
-- For each order: SELECT * FROM customers WHERE id = ?  -- N queries

-- Fix: single JOIN query
SELECT o.*, c.name, c.email
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id;

-- Queries with high calls but low avg rows (likely N+1)
SELECT query, calls, rows, rows::FLOAT / calls AS avg_rows
FROM pg_stat_statements
WHERE calls > 100 AND rows::FLOAT / calls < 5
ORDER BY calls DESC;

-- Anti-pattern: joining tables you don't need
SELECT o.order_id, o.total
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id  -- not used
JOIN products p ON o.product_id = p.product_id     -- not used
WHERE o.status = 'pending';

-- Fix: only join what you need
SELECT o.order_id, o.total
FROM orders o
WHERE o.status = 'pending';

-- Anti-pattern: OFFSET 1000000 is slow
SELECT * FROM orders ORDER BY created_at DESC LIMIT 20 OFFSET 1000000;

-- Fix: keyset (cursor) pagination
SELECT * FROM orders
WHERE created_at < '2024-01-15T10:30:00Z'  -- last value from previous page
ORDER BY created_at DESC
LIMIT 20;

-- Anti-pattern: function wrapping indexed column prevents index use
SELECT * FROM orders WHERE DATE(created_at) = '2024-01-15';

-- Fix: use range condition on the raw column
SELECT * FROM orders
WHERE created_at >= '2024-01-15' AND created_at < '2024-01-16';

-- Or use an expression index
CREATE INDEX idx_orders_created_date ON orders ((DATE(created_at)));

ANALYZE VERBOSE orders;
-- Or set autovacuum to trigger more aggressively
ALTER TABLE orders SET (autovacuum_analyze_scale_factor = 0.01);

-- Per-session
SET work_mem = '256MB';

-- Per-role (for reporting users)
ALTER ROLE reporting_app SET work_mem = '256MB';

-- Check for spills in EXPLAIN ANALYZE output
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;

-- WRONG: blocks all access
CREATE INDEX idx_orders_date ON orders(order_date);

-- RIGHT: allows reads and writes during creation
CREATE INDEX CONCURRENTLY idx_orders_date ON orders(order_date);

-- Find unused indexes
SELECT schemaname, relname, indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexrelname NOT LIKE '%_pkey'
ORDER BY pg_relation_size(indexrelid) DESC;

-- Examine MCV and histogram for a column
SELECT attname, n_distinct,
       array_length(most_common_vals, 1) AS mcv_count,
       array_length(histogram_bounds, 1) AS histogram_bucket_count
FROM pg_stats
WHERE tablename = 'orders' AND attname = 'status';

-- Example output:
-- attname | n_distinct | mcv_count | histogram_bucket_count
-- status  | 8          | 5         | 20
-- This means: 5 most common values captured, remaining distribution in 20 buckets

-- Increase statistics for skewed distributions
ALTER TABLE orders ALTER COLUMN customer_id SET STATISTICS 500;
ANALYZE orders;

-- Check the impact on estimates
EXPLAIN (ANALYZE) SELECT * FROM orders WHERE customer_id = 12345;
-- Compare "rows" (estimated) vs actual rows returned

-- Check correlation for frequently queried columns
SELECT attname, correlation
FROM pg_stats
WHERE tablename = 'orders'
ORDER BY correlation;

-- Low correlation (e.g., < 0.1): random I/O pattern for index scans
-- High correlation (e.g., > 0.9): sequential I/O pattern, much faster

-- Fix low correlation with CLUSTER (rewrites table in index order)
CLUSTER orders USING idx_orders_customer_date;

-- CLUSTER acquires ACCESS EXCLUSIVE lock. For production:
-- 1. CLUSTER on a replica
-- 2. Promote the replica
-- Or use pg_repack for online reorganization

-- GIN trigram index: stores all trigrams, good for substring search
CREATE INDEX idx_docs_body_gin ON documents USING GIN (body gin_trgm_ops);

-- Fast for: LIKE '%pattern%', similarity search
-- Index size: large (stores every trigram)
-- Write cost: high (many index entries updated per row change)

-- GiST trigram index: stores trigram signatures, compact
CREATE INDEX idx_docs_body_gist ON documents USING GiST (body gist_trgm_ops);

-- Fast for: exact LIKE, equality, proximity
-- Index size: small (signature-based)
-- Write cost: low (compact index entries)
-- Less selective for short patterns

-- Partition pruning in action
EXPLAIN (ANALYZE)
SELECT * FROM orders
WHERE created_at >= '2024-01-01' AND created_at < '2024-04-01';

-- Output should show:
-- -> Seq Scan on orders_2024_q1
-- Only orders_2024_q1 is scanned; other partitions are pruned

-- Pruning does NOT work with:
-- 1. Non-immutable functions: WHERE created_at >= NOW()  (NOW() is stable, not immutable)
-- 2. Parameters from prepared statements (in some cases)
-- 3. Expressions the planner cannot evaluate at plan time

-- Enable partition-wise joins
SET enable_partitionwise_join = on;
SET enable_partitionwise_aggregate = on;

-- Verify partition-wise join in EXPLAIN output
EXPLAIN (ANALYZE)
SELECT o.*, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.created_at >= '2024-01-01';

-- Look for: "Append" node containing "Join" nodes per partition pair