Database Query Cost Analyzer

The main culprits: 1) Full table scans — no index on filtered columns. 2) N+1 queries — fetching related records one at a time in a loop. 3) SELECT * — fetching unnecessary columns, especially with large TEXT/BLOB columns. 4) Missing index on JOIN columns. 5) Functions on indexed columns (WHERE LOWER(email) = '...' bypasses the index). 6) LIKE '%term' — leading wildcard prevents index use.

Key fields: 'Seq Scan' = full table scan (bad for large tables). 'Index Scan' = uses index. 'Bitmap Heap Scan' = index + heap fetch. 'Hash Join' / 'Merge Join' = common for large joins. 'cost=X..Y' means estimated rows X to Y. 'rows=' is estimated row count. 'actual time=' (in EXPLAIN ANALYZE) shows real execution time. Look for high cost nodes and missing indexes.

Index scan: PostgreSQL uses the index to find row locations, then fetches the actual heap (table) rows to get full column data. Index-only scan: all needed columns are in the index itself — no heap fetch needed. Much faster. To get index-only scans, include all SELECT columns in the index using INCLUDE (PostgreSQL 11+) or a covering index.

N+1 happens when you fetch a list of N items, then for each item run 1 more query to fetch related data. Example: fetch 100 users (1 query), then for each user fetch their posts (100 queries) = 101 total queries. Fix: use a JOIN to fetch both in one query, or use eager loading (with the ORM) with IN clauses.