┌─────────────────────────────────────────────────────────────────────────┐
│                    QUERY OPTIMIZATION WORKFLOW                          │
├─────────────────────────────────────────────────────────────────────────┤
│                                                                         │
│  1. MEASURE                                                             │
│     └──▶ Identify slow queries (pg_stat_statements, logs)              │
│          Get baseline execution time                                    │
│                                                                         │
│  2. EXPLAIN ANALYZE                                                     │
│     └──▶ Get execution plan with actual timings                        │
│          Identify the slowest operations                               │
│                                                                         │
│  3. DIAGNOSE                                                            │
│     └──▶ Sequential scans on large tables? → Add index                 │
│          Poor row estimates? → Run ANALYZE                             │
│          Memory spills to disk? → Increase work_mem                    │
│          Too many rows processed? → Rewrite query                      │
│                                                                         │
│  4. APPLY FIX                                                           │
│     └──▶ Add/modify index                                              │
│          Rewrite query                                                 │
│          Adjust configuration                                          │
│          Restructure schema                                            │
│                                                                         │
│  5. VERIFY                                                              │
│     └──▶ EXPLAIN ANALYZE again                                         │
│          Measure improvement                                           │
│          Test with production-like data                                │
│                                                                         │
└─────────────────────────────────────────────────────────────────────────┘

-- Enable the extension (requires server restart or shared_preload_libraries)
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Top 10 slowest queries by total time
SELECT 
    substring(query, 1, 100) AS query_preview,
    calls,
    round(total_exec_time::numeric, 2) AS total_time_ms,
    round(mean_exec_time::numeric, 2) AS avg_time_ms,
    round(stddev_exec_time::numeric, 2) AS stddev_ms,
    rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- Queries with highest average time
SELECT 
    substring(query, 1, 100) AS query_preview,
    calls,
    round(mean_exec_time::numeric, 2) AS avg_time_ms,
    round((100 * total_exec_time / sum(total_exec_time) OVER ())::numeric, 2) AS pct_total
FROM pg_stat_statements
WHERE calls > 10  -- Filter out rare queries
ORDER BY mean_exec_time DESC
LIMIT 20;

-- Queries with high variance (inconsistent performance)
SELECT 
    substring(query, 1, 100) AS query_preview,
    calls,
    round(mean_exec_time::numeric, 2) AS avg_ms,
    round(min_exec_time::numeric, 2) AS min_ms,
    round(max_exec_time::numeric, 2) AS max_ms
FROM pg_stat_statements
WHERE calls > 100 
AND stddev_exec_time > mean_exec_time  -- High variance
ORDER BY stddev_exec_time DESC
LIMIT 10;

-- Log queries slower than 1 second
ALTER SYSTEM SET log_min_duration_statement = '1000';  -- milliseconds
SELECT pg_reload_conf();

-- Log all queries (careful in production - lots of logs!)
ALTER SYSTEM SET log_statement = 'all';

-- View recent slow queries from logs
-- (Use your log viewer or tail the log files)

-- Basic plan (estimates only -- does NOT run the query)
-- Use this when you want to see the plan without side effects (e.g., DELETE/UPDATE)
EXPLAIN SELECT * FROM orders WHERE user_id = 123;

-- With actual execution (RUNS the query! Be careful with INSERT/UPDATE/DELETE)
-- Wrap destructive queries in a transaction and ROLLBACK:
--   BEGIN; EXPLAIN ANALYZE DELETE FROM orders WHERE ...; ROLLBACK;
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 123;

-- Full analysis with buffer stats (the gold standard for production debugging)
-- BUFFERS shows how many 8KB pages were read from cache vs disk
-- This is often more useful than timing for diagnosing I/O problems
EXPLAIN (ANALYZE, BUFFERS, TIMING, FORMAT TEXT)
SELECT * FROM orders WHERE user_id = 123;

-- JSON format for programmatic analysis (useful for tools like pganalyze, dalibo)
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT * FROM orders WHERE user_id = 123;

Limit  (cost=0.43..8.45 rows=10 width=152) (actual time=0.025..0.042 rows=10 loops=1)
  ^^^^   ^^^^^^^^^^^^     ^^^^   ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^   ^^^^^^^^
    |         |             |              |                               |
    |         |             |              |                               |
  Node      Cost          Rows          Actual execution                 Loops
  Type    estimate       estimate          stats

  cost=startup..total   - Estimated cost units (not milliseconds!)
  rows=10               - Estimated rows returned
  actual time=0.025     - Real time for first row (ms)
  actual time=..0.042   - Real time for all rows (ms)
  rows=10               - Actual rows returned
  loops=1               - How many times this node executed

-- Symptom: Seq Scan on large table for selective query
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 12345;

--    Seq Scan on orders (cost=0.00..25000.00 rows=100 width=100) 
--                       (actual time=150.5..1523.2 rows=95 loops=1)
--      Filter: (user_id = 12345)
--      Rows Removed by Filter: 999905

-- Fix: Add index
CREATE INDEX idx_orders_user ON orders(user_id);

-- After:
--    Index Scan using idx_orders_user on orders 
--      (cost=0.42..95.25 rows=100 width=100) 
--      (actual time=0.03..0.52 rows=95 loops=1)

-- Symptom: Estimated rows wildly different from actual
EXPLAIN ANALYZE SELECT * FROM users WHERE status = 'vip';

--    Seq Scan on users (cost=0.00..1000.00 rows=50000 width=100) 
--                      (actual time=0.02..5.23 rows=50 loops=1)
--                                     ^^^^^^^            ^^
--                                   Estimated 50000, actual 50!

-- Fix: Update statistics
ANALYZE users;

-- Or for specific column:
ALTER TABLE users ALTER COLUMN status SET STATISTICS 1000;
ANALYZE users;

-- PostgreSQL usually picks good join order, but sometimes...
-- Symptom: Small table scanned many times in Nested Loop

EXPLAIN ANALYZE
SELECT * FROM orders o
JOIN users u ON o.user_id = u.id
JOIN products p ON o.product_id = p.id
WHERE u.country = 'US';

-- If planner gets it wrong, force join order:
SET join_collapse_limit = 1;  -- Respect explicit join order
SELECT * FROM 
    (SELECT * FROM users WHERE country = 'US') u  -- Filter first
    JOIN orders o ON o.user_id = u.id
    JOIN products p ON o.product_id = p.id;

-- Symptom: Sort or Hash spills to disk
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders ORDER BY created_at DESC;

--    Sort (cost=50000.00..52500.00 rows=1000000 width=100)
--      Sort Method: external merge  Disk: 120000kB  ← Problem!
--                   ^^^^^^^^^^^^^^
--    Better: Sort Method: quicksort  Memory: 25kB

-- Fix 1: Increase work_mem for this session
SET work_mem = '256MB';

-- Fix 2: Add index to avoid sort
CREATE INDEX idx_orders_created_desc ON orders(created_at DESC);

-- Symptom: Same query pattern repeated many times in logs
-- Application does:
-- 1. SELECT * FROM orders WHERE status = 'pending'
-- 2. For each order: SELECT * FROM users WHERE id = <user_id>

-- Fix: Single query with JOIN
SELECT o.*, u.name, u.email
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = 'pending';

-- Or batch lookup
SELECT * FROM users WHERE id IN (1, 2, 3, 4, 5, ...);

-- Subquery in WHERE (often inefficient)
SELECT * FROM products
WHERE id IN (
    SELECT product_id FROM order_items
    WHERE order_id IN (
        SELECT id FROM orders WHERE user_id = 123
    )
);

-- Rewritten with JOINs (usually faster)
SELECT DISTINCT p.*
FROM products p
JOIN order_items oi ON p.id = oi.product_id
JOIN orders o ON oi.order_id = o.id
WHERE o.user_id = 123;

-- Find users who have placed orders

-- Method 1: IN (subquery executes first, can be slow with large results)
SELECT * FROM users
WHERE id IN (SELECT user_id FROM orders);

-- Method 2: EXISTS (often faster, stops at first match)
SELECT * FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

-- Method 3: JOIN (depends on data, may return duplicates)
SELECT DISTINCT u.*
FROM users u
JOIN orders o ON u.id = o.user_id;

-- Method 4: Semi-join with LATERAL (PostgreSQL-specific, very flexible)
SELECT u.*
FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.user_id = u.id LIMIT 1
);

-- OFFSET-based (slow for large offsets)
SELECT * FROM orders ORDER BY id LIMIT 20 OFFSET 10000;
-- Must scan and discard 10000 rows!

-- Keyset/Cursor pagination (fast, O(log n))
SELECT * FROM orders 
WHERE id > 12345  -- Last seen ID
ORDER BY id 
LIMIT 20;

-- For complex sorts, use composite cursor
SELECT * FROM orders
WHERE (created_at, id) < ('2024-01-15 10:00:00', 54321)
ORDER BY created_at DESC, id DESC
LIMIT 20;

-- Counting with filters (scans filtered rows)
SELECT COUNT(*) FROM orders WHERE status = 'pending';

-- If you need approximate count, use table stats (instant!)
SELECT reltuples::bigint AS approximate_count
FROM pg_class WHERE relname = 'orders';

-- Pre-aggregated summary tables for dashboards
CREATE MATERIALIZED VIEW order_stats AS
SELECT 
    date_trunc('day', created_at) AS day,
    status,
    COUNT(*) AS count,
    SUM(total) AS revenue
FROM orders
GROUP BY 1, 2;

-- Refresh periodically
REFRESH MATERIALIZED VIEW CONCURRENTLY order_stats;

-- Memory settings
shared_buffers = '4GB'          -- 25% of RAM for dedicated server
effective_cache_size = '12GB'   -- 75% of RAM (includes OS cache)
work_mem = '256MB'              -- Memory per operation (careful!)
maintenance_work_mem = '1GB'    -- For VACUUM, CREATE INDEX

-- Planner settings
random_page_cost = 1.1          -- Lower for SSD (default 4.0 for HDD)
effective_io_concurrency = 200  -- Higher for SSD
default_statistics_target = 100 -- Increase for better estimates

-- Parallelism
max_parallel_workers_per_gather = 4
max_parallel_workers = 8
parallel_tuple_cost = 0.01
parallel_setup_cost = 100

-- Change for current session only
SET work_mem = '512MB';

-- Change for current transaction
SET LOCAL work_mem = '512MB';

-- Change system-wide (requires reload or restart)
ALTER SYSTEM SET shared_buffers = '8GB';
SELECT pg_reload_conf();

-- View current settings
SHOW work_mem;
SHOW ALL;

-- View non-default settings
SELECT name, setting, unit, source
FROM pg_settings
WHERE source != 'default';

-- Generic plan (cached, parameter agnostic)
PREPARE get_user(int) AS SELECT * FROM users WHERE id = $1;
EXECUTE get_user(123);

-- PostgreSQL uses generic plan after 5 executions
-- or earlier if custom plan cost is similar

-- Force custom plan (uses actual parameter values for planning)
SET plan_cache_mode = 'force_custom_plan';

-- View cached plans
SELECT * FROM pg_prepared_statements;

-- Problem: Same query, different plans based on statistics

-- Check if your query gets different plans
EXPLAIN ANALYZE SELECT * FROM orders WHERE status = 'pending';
-- Run multiple times, compare plans

-- If plans are unstable, consider:
-- 1. More frequent ANALYZE
-- 2. Increase statistics target
ALTER TABLE orders ALTER COLUMN status SET STATISTICS 1000;
ANALYZE orders;

-- 3. Use pg_hint_plan extension to force specific plan

-- Current query takes 30 seconds
SELECT 
    COUNT(*) FILTER (WHERE status = 'pending') AS pending_count,
    COUNT(*) FILTER (WHERE status = 'processing') AS processing_count,
    COUNT(*) FILTER (WHERE status = 'shipped') AS shipped_count,
    SUM(total) FILTER (WHERE status = 'completed') AS revenue
FROM orders
WHERE created_at > CURRENT_DATE - INTERVAL '30 days';

-- Takes 5+ seconds
SELECT p.*, c.name as category_name, 
       COUNT(r.id) as review_count, 
       AVG(r.rating) as avg_rating
FROM products p
LEFT JOIN categories c ON p.category_id = c.id
LEFT JOIN reviews r ON r.product_id = p.id
WHERE lower(p.name) LIKE '%wireless%'
  AND p.price BETWEEN 50 AND 200
  AND p.status = 'active'
GROUP BY p.id, c.name
HAVING AVG(r.rating) >= 4.0 OR COUNT(r.id) = 0
ORDER BY avg_rating DESC NULLS LAST, review_count DESC
LIMIT 20;