Note: Look for: large discrepancy between estimated and actual rows, Seq Scan on big tables with selective filter, Sort spilling to disk, Rows Removed by Filter very high.
10. Using Index-Only Scans
CREATE INDEX orders_cover ON orders(customer_id) INCLUDE (status);VACUUM orders; -- keep visibility map freshEXPLAIN SELECT customer_id, status FROM orders WHERE customer_id = 42;
11. Understanding Bitmap Heap Scans
-- Bitmap Index Scan builds bitmap of TIDs-- Bitmap Heap Scan then fetches heap pages in physical order (less seek)
12. Using Parallel Query Execution
SET max_parallel_workers_per_gather = 4;EXPLAIN SELECT count(*) FROM big_fact; -- Gather → Parallel Seq Scan
13. Analyzing Join Order
SET join_collapse_limit = 8; -- default; raise for many tablesSET from_collapse_limit = 8;
14. Identifying Missing Indexes
SELECT relname, seq_scan, seq_tup_read, idx_scan FROM pg_stat_user_tables WHERE seq_scan > 1000 AND seq_tup_read / seq_scan > 10000 ORDER BY seq_tup_read DESC;