1-- Basic explain
 2EXPLAIN SELECT * FROM users WHERE email = 'user@example.com';
 3
 4-- With actual execution stats
 5EXPLAIN ANALYZE
 6SELECT * FROM users WHERE email = 'user@example.com';
 7
 8-- Verbose output with more details
 9EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
10SELECT u.*, o.order_total
11FROM users u
12JOIN orders o ON u.id = o.user_id
13WHERE u.created_at > NOW() - INTERVAL '30 days';

 1-- Standard B-Tree index
 2CREATE INDEX idx_users_email ON users(email);
 3
 4-- Composite index (order matters!)
 5CREATE INDEX idx_orders_user_status ON orders(user_id, status);
 6
 7-- Partial index (index subset of rows)
 8CREATE INDEX idx_active_users ON users(email)
 9WHERE status = 'active';
10
11-- Expression index
12CREATE INDEX idx_users_lower_email ON users(LOWER(email));
13
14-- Covering index (include additional columns)
15CREATE INDEX idx_users_email_covering ON users(email)
16INCLUDE (name, created_at);
17
18-- Full-text search index
19CREATE INDEX idx_posts_search ON posts
20USING GIN(to_tsvector('english', title || ' ' || body));
21
22-- JSONB index
23CREATE INDEX idx_metadata ON events USING GIN(metadata);

1-- Bad: Fetches unnecessary columns
2SELECT * FROM users WHERE id = 123;
3
4-- Good: Fetch only what you need
5SELECT id, email, name FROM users WHERE id = 123;

 1-- Bad: Function prevents index usage
 2SELECT * FROM users WHERE LOWER(email) = 'user@example.com';
 3
 4-- Good: Create functional index or use exact match
 5CREATE INDEX idx_users_email_lower ON users(LOWER(email));
 6-- Then:
 7SELECT * FROM users WHERE LOWER(email) = 'user@example.com';
 8
 9-- Or store normalized data
10SELECT * FROM users WHERE email = 'user@example.com';

 1-- Bad: Cartesian product then filter
 2SELECT u.name, o.total
 3FROM users u, orders o
 4WHERE u.id = o.user_id AND u.created_at > '2024-01-01';
 5
 6-- Good: Filter before join
 7SELECT u.name, o.total
 8FROM users u
 9JOIN orders o ON u.id = o.user_id
10WHERE u.created_at > '2024-01-01';
11
12-- Better: Filter both tables
13SELECT u.name, o.total
14FROM (SELECT * FROM users WHERE created_at > '2024-01-01') u
15JOIN orders o ON u.id = o.user_id;

1# Bad: Executes N+1 queries
2users = db.query("SELECT * FROM users LIMIT 10")
3for user in users:
4    orders = db.query("SELECT * FROM orders WHERE user_id = ?", user.id)
5    # Process orders

 1-- Solution 1: JOIN
 2SELECT
 3    u.id, u.name,
 4    o.id as order_id, o.total
 5FROM users u
 6LEFT JOIN orders o ON u.id = o.user_id
 7WHERE u.id IN (1, 2, 3, 4, 5);
 8
 9-- Solution 2: Batch query
10SELECT * FROM orders
11WHERE user_id IN (1, 2, 3, 4, 5);

 1# Good: Single query with JOIN or batch load
 2# Using JOIN
 3results = db.query("""
 4    SELECT u.id, u.name, o.id as order_id, o.total
 5    FROM users u
 6    LEFT JOIN orders o ON u.id = o.user_id
 7    WHERE u.id IN (1, 2, 3, 4, 5)
 8""")
 9
10# Or batch load
11users = db.query("SELECT * FROM users LIMIT 10")
12user_ids = [u.id for u in users]
13orders = db.query(
14    "SELECT * FROM orders WHERE user_id IN (?)",
15    user_ids
16)
17# Group orders by user_id
18orders_by_user = {}
19for order in orders:
20    orders_by_user.setdefault(order.user_id, []).append(order)

1-- Slow for large offsets
2SELECT * FROM users
3ORDER BY created_at DESC
4LIMIT 20 OFFSET 100000;  -- Very slow!

 1-- Much faster: Use cursor (last seen ID)
 2SELECT * FROM users
 3WHERE created_at < '2024-01-15 10:30:00'  -- Last cursor
 4ORDER BY created_at DESC
 5LIMIT 20;
 6
 7-- With composite sorting
 8SELECT * FROM users
 9WHERE (created_at, id) < ('2024-01-15 10:30:00', 12345)
10ORDER BY created_at DESC, id DESC
11LIMIT 20;
12
13-- Requires index
14CREATE INDEX idx_users_cursor ON users(created_at DESC, id DESC);

 1-- Bad: Counts all rows
 2SELECT COUNT(*) FROM orders;  -- Slow on large tables
 3
 4-- Good: Use estimates for approximate counts
 5SELECT reltuples::bigint AS estimate
 6FROM pg_class
 7WHERE relname = 'orders';
 8
 9-- Good: Filter before counting
10SELECT COUNT(*) FROM orders
11WHERE created_at > NOW() - INTERVAL '7 days';
12
13-- Better: Use index-only scan
14CREATE INDEX idx_orders_created ON orders(created_at);
15SELECT COUNT(*) FROM orders
16WHERE created_at > NOW() - INTERVAL '7 days';

 1-- Bad: Group by then filter
 2SELECT user_id, COUNT(*) as order_count
 3FROM orders
 4GROUP BY user_id
 5HAVING COUNT(*) > 10;
 6
 7-- Better: Filter first, then group (if possible)
 8SELECT user_id, COUNT(*) as order_count
 9FROM orders
10WHERE status = 'completed'
11GROUP BY user_id
12HAVING COUNT(*) > 10;
13
14-- Best: Use covering index
15CREATE INDEX idx_orders_user_status ON orders(user_id, status);

 1-- Bad: Correlated subquery (runs for each row)
 2SELECT u.name, u.email,
 3    (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) as order_count
 4FROM users u;
 5
 6-- Good: JOIN with aggregation
 7SELECT u.name, u.email, COUNT(o.id) as order_count
 8FROM users u
 9LEFT JOIN orders o ON o.user_id = u.id
10GROUP BY u.id, u.name, u.email;
11
12-- Better: Use window functions
13SELECT DISTINCT ON (u.id)
14    u.name, u.email,
15    COUNT(o.id) OVER (PARTITION BY u.id) as order_count
16FROM users u
17LEFT JOIN orders o ON o.user_id = u.id;

 1-- Using Common Table Expressions
 2WITH recent_users AS (
 3    SELECT id, name, email
 4    FROM users
 5    WHERE created_at > NOW() - INTERVAL '30 days'
 6),
 7user_order_counts AS (
 8    SELECT user_id, COUNT(*) as order_count
 9    FROM orders
10    WHERE created_at > NOW() - INTERVAL '30 days'
11    GROUP BY user_id
12)
13SELECT ru.name, ru.email, COALESCE(uoc.order_count, 0) as orders
14FROM recent_users ru
15LEFT JOIN user_order_counts uoc ON ru.id = uoc.user_id;

 1-- Bad: Multiple individual inserts
 2INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');
 3INSERT INTO users (name, email) VALUES ('Bob', 'bob@example.com');
 4INSERT INTO users (name, email) VALUES ('Carol', 'carol@example.com');
 5
 6-- Good: Batch insert
 7INSERT INTO users (name, email) VALUES
 8    ('Alice', 'alice@example.com'),
 9    ('Bob', 'bob@example.com'),
10    ('Carol', 'carol@example.com');
11
12-- Better: Use COPY for bulk inserts (PostgreSQL)
13COPY users (name, email) FROM '/tmp/users.csv' CSV HEADER;

 1-- Bad: Update in loop
 2UPDATE users SET status = 'active' WHERE id = 1;
 3UPDATE users SET status = 'active' WHERE id = 2;
 4-- ... repeat for many IDs
 5
 6-- Good: Single UPDATE with IN clause
 7UPDATE users
 8SET status = 'active'
 9WHERE id IN (1, 2, 3, 4, 5, ...);
10
11-- Better: Use temporary table for large batches
12CREATE TEMP TABLE temp_user_updates (id INT, new_status VARCHAR);
13INSERT INTO temp_user_updates VALUES (1, 'active'), (2, 'active'), ...;
14
15UPDATE users u
16SET status = t.new_status
17FROM temp_user_updates t
18WHERE u.id = t.id;

 1-- Create materialized view
 2CREATE MATERIALIZED VIEW user_order_summary AS
 3SELECT
 4    u.id,
 5    u.name,
 6    COUNT(o.id) as total_orders,
 7    SUM(o.total) as total_spent,
 8    MAX(o.created_at) as last_order_date
 9FROM users u
10LEFT JOIN orders o ON u.id = o.user_id
11GROUP BY u.id, u.name;
12
13-- Add index to materialized view
14CREATE INDEX idx_user_summary_spent ON user_order_summary(total_spent DESC);
15
16-- Refresh materialized view
17REFRESH MATERIALIZED VIEW user_order_summary;
18
19-- Concurrent refresh (PostgreSQL)
20REFRESH MATERIALIZED VIEW CONCURRENTLY user_order_summary;
21
22-- Query materialized view (very fast)
23SELECT * FROM user_order_summary
24WHERE total_spent > 1000
25ORDER BY total_spent DESC;

 1-- Range partitioning by date (PostgreSQL)
 2CREATE TABLE orders (
 3    id SERIAL,
 4    user_id INT,
 5    total DECIMAL,
 6    created_at TIMESTAMP
 7) PARTITION BY RANGE (created_at);
 8
 9-- Create partitions
10CREATE TABLE orders_2024_q1 PARTITION OF orders
11    FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');
12
13CREATE TABLE orders_2024_q2 PARTITION OF orders
14    FOR VALUES FROM ('2024-04-01') TO ('2024-07-01');
15
16-- Queries automatically use appropriate partition
17SELECT * FROM orders
18WHERE created_at BETWEEN '2024-02-01' AND '2024-02-28';
19-- Only scans orders_2024_q1 partition

 1-- Force index usage (MySQL)
 2SELECT * FROM users
 3USE INDEX (idx_users_email)
 4WHERE email = 'user@example.com';
 5
 6-- Parallel query (PostgreSQL)
 7SET max_parallel_workers_per_gather = 4;
 8SELECT * FROM large_table WHERE condition;
 9
10-- Join hints (PostgreSQL)
11SET enable_nestloop = OFF;  -- Force hash or merge join

 1-- Update statistics
 2ANALYZE users;
 3ANALYZE VERBOSE orders;
 4
 5-- Vacuum (PostgreSQL)
 6VACUUM ANALYZE users;
 7VACUUM FULL users;  -- Reclaim space (locks table)
 8
 9-- Reindex
10REINDEX INDEX idx_users_email;
11REINDEX TABLE users;

 1-- Find slow queries (PostgreSQL)
 2SELECT query, calls, total_time, mean_time
 3FROM pg_stat_statements
 4ORDER BY mean_time DESC
 5LIMIT 10;
 6
 7-- Find missing indexes (PostgreSQL)
 8SELECT
 9    schemaname,
10    tablename,
11    seq_scan,
12    seq_tup_read,
13    idx_scan,
14    seq_tup_read / seq_scan AS avg_seq_tup_read
15FROM pg_stat_user_tables
16WHERE seq_scan > 0
17ORDER BY seq_tup_read DESC
18LIMIT 10;
19
20-- Find unused indexes (PostgreSQL)
21SELECT
22    schemaname,
23    tablename,
24    indexname,
25    idx_scan,
26    idx_tup_read,
27    idx_tup_fetch
28FROM pg_stat_user_indexes
29WHERE idx_scan = 0
30ORDER BY pg_relation_size(indexrelid) DESC;