PostgreSQL Query Optimization

-- Run with EXPLAIN ANALYZE to get actual execution times
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT p.name, c.name as category, p.price
FROM products p
JOIN categories c ON p.category_id = c.id
WHERE p.price BETWEEN 100 AND 500
  AND c.name = 'Electronics'
ORDER BY p.created_at DESC
LIMIT 20;

-- Check if indexes are being used
SELECT schemaname, tablename, indexname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
WHERE schemaname = 'public'
ORDER BY idx_scan ASC;

-- Composite index for filtered queries
CREATE INDEX idx_products_category_price 
ON products(category_id, price) 
WHERE price IS NOT NULL;

-- Partial index for active records only
CREATE INDEX idx_products_active 
ON products(created_at) 
WHERE status = 'active';

-- GIN index for full-text search
CREATE INDEX idx_products_search 
ON products USING GIN(to_tsvector('english', name || ' ' || description));

-- BRIN index for time-series data
CREATE INDEX idx_events_timestamp 
ON events USING BRIN(created_at);

-- Covering index (index-only scan)
CREATE INDEX idx_products_category_include 
ON products(category_id) 
INCLUDE (name, price);

-- Enable pg_stat_statements first
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Find slowest queries by total time
SELECT 
  query,
  calls,
  total_exec_time,
  mean_exec_time,
  max_exec_time,
  stddev_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- Find queries with high I/O
SELECT 
  query,
  calls,
  shared_blks_hit,
  shared_blks_read,
  (shared_blks_hit::float / NULLIF(shared_blks_hit + shared_blks_read, 0)) * 100 AS cache_hit_ratio
FROM pg_stat_statements
WHERE shared_blks_read > 0
ORDER BY shared_blks_read DESC
LIMIT 10;

-- OLTP Workload Configuration (postgresql.conf)
-- For 32GB RAM, 8 cores, NVMe SSD

-- Memory Settings
shared_buffers = 8GB                    -- 25% of RAM
effective_cache_size = 24GB             -- 75% of RAM
work_mem = 64MB                         -- Per operation
maintenance_work_mem = 2GB              -- For VACUUM, CREATE INDEX

-- Write Performance
wal_buffers = 16MB
checkpoint_completion_target = 0.9
max_wal_size = 4GB
min_wal_size = 1GB

-- Query Planner
random_page_cost = 1.1                  -- For SSD
effective_io_concurrency = 200          -- For SSD

-- Connections
max_connections = 200

-- Parallelism
max_worker_processes = 8
max_parallel_workers_per_gather = 4
max_parallel_workers = 8

-- Autovacuum (high-write workload)
autovacuum_max_workers = 4
autovacuum_naptime = 10s
autovacuum_vacuum_scale_factor = 0.05
autovacuum_analyze_scale_factor = 0.02