Advanced query optimization techniques

-- Partition pruning
EXPLAIN ANALYZE
SELECT * FROM measurements
WHERE measured_at >= '2024-01-01'
  AND measured_at < '2024-02-01';
-- Only scans measurements_2024_01 partition

-- Index scan vs bitmap scan vs sequential scan
SET enable_seqscan = OFF;  -- Force index usage (testing only)
SET enable_seqscan = ON;   -- Re-enable

-- Parallel query execution
SET max_parallel_workers_per_gather = 4;

EXPLAIN ANALYZE
SELECT COUNT(*) FROM large_table;
-- May use parallel sequential scan

-- Disable parallel for small queries
SET parallel_tuple_cost = 1000;

-- Prepared statements (plan caching)
PREPARE user_lookup (INT) AS
  SELECT username, email FROM users WHERE id = $1;

EXECUTE user_lookup(123);
EXECUTE user_lookup(456);

DEALLOCATE user_lookup;

-- Query hints (PostgreSQL doesn't support, but plan can be influenced)
-- Force nested loop join
SET enable_hashjoin = OFF;
SET enable_mergejoin = OFF;
-- Run query
-- Reset
SET enable_hashjoin = ON;
SET enable_mergejoin = ON;

-- Work_mem tuning (per-query memory)
-- Affects sorts, hash joins
SHOW work_mem;  -- Default: 4MB

-- Increase for specific query
SET work_mem = '256MB';
SELECT * FROM large_table ORDER BY created_at DESC LIMIT 1000;
RESET work_mem;

-- Effective_cache_size (planner hint)
SET effective_cache_size = '8GB';
-- Tells planner how much data likely cached

-- Materialized CTE (force execution before join)
WITH expensive_calc AS MATERIALIZED (
  SELECT user_id, SUM(total) AS lifetime_value
  FROM orders
  GROUP BY user_id
)
SELECT u.username, ec.lifetime_value
FROM users u
JOIN expensive_calc ec ON u.id = ec.user_id
WHERE ec.lifetime_value > 1000;

-- Index condition vs filter condition
EXPLAIN ANALYZE
SELECT * FROM orders
WHERE user_id = 123        -- Index condition
  AND status = 'completed'  -- Filter condition (if not in index)
  AND total > 100;          -- Filter condition

-- Add to index to push more to index condition
CREATE INDEX idx_orders_user_status_total
  ON orders (user_id, status, total);

-- Now all conditions are index conditions

-- Avoiding function calls in WHERE (prevents index usage)
-- Bad:
SELECT * FROM users WHERE EXTRACT(YEAR FROM created_at) = 2024;

-- Good:
SELECT * FROM users
WHERE created_at >= '2024-01-01'
  AND created_at < '2025-01-01';

-- OR to UNION transformation
-- Bad (may not use indexes efficiently):
SELECT * FROM products
WHERE category_id = 5 OR featured = true;

-- Better:
SELECT * FROM products WHERE category_id = 5
UNION
SELECT * FROM products WHERE featured = true;

-- Avoid OFFSET for pagination (slow on large offsets)
-- Bad:
SELECT * FROM products ORDER BY id LIMIT 10 OFFSET 100000;

-- Good (keyset pagination):
SELECT * FROM products
WHERE id > 100000
ORDER BY id
LIMIT 10;

-- Cardinality estimation
SELECT
  tablename,
  attname,
  n_distinct,
  correlation
FROM pg_stats
WHERE tablename = 'orders'
  AND schemaname = 'public';

-- If n_distinct is wrong, update statistics
ALTER TABLE orders ALTER COLUMN status SET STATISTICS 1000;
ANALYZE orders;

-- Join removal (unused join)
-- Planner may remove this join if only users.id is used
SELECT u.id
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

-- Multi-column statistics (correlated columns)
CREATE STATISTICS orders_user_status_stats (dependencies)
  ON user_id, status FROM orders;

ANALYZE orders;

-- Now planner understands correlation between user_id and status

-- Query result caching (application level)
/*
const cacheKey = `user:${userId}:orders`;
let orders = await cache.get(cacheKey);

if (!orders) {
  orders = await db.query('SELECT * FROM orders WHERE user_id = $1', [userId]);
  await cache.set(cacheKey, orders, 300); // 5 min TTL
}
*/

-- Statement timeout (prevent runaway queries)
SET statement_timeout = '30s';

-- Query locks table
LOCK TABLE products IN ACCESS SHARE MODE;

-- Monitoring slow queries
SELECT
  query,
  mean_exec_time,
  calls,
  total_exec_time
FROM pg_stat_statements
WHERE mean_exec_time > 1000  -- > 1 second
ORDER BY mean_exec_time DESC
LIMIT 20;