SQL index design — covering indexes and partial indexes

Adding indexes doesn't improve query performance. The database ignores the index and does a full table scan. Some queries are fast but adding a new column to SELECT makes them slow again.

(1) Column order matters: put equality columns first, then range columns. WHERE a = 1 AND b > 5 needs index on (a, b) not (b, a). (2) Covering index: include all columns the query needs so the database never reads the table: CREATE INDEX idx ON table(a, b) INCLUDE (c, d). (3) Partial index: index only rows you actually query: CREATE INDEX idx ON table(status) WHERE status = 'active'. Smaller, faster. (4) Don't index low-cardinality columns alone (boolean, status with 3 values) — the selectivity is too low. (5) Composite indexes satisfy queries on leading columns: (a, b, c) works for queries on (a), (a, b), (a, b, c) but not (b, c). (6) Monitor: pg_stat_user_indexes for unused indexes wasting write performance.

Indexes trade write performance for read performance. A poorly designed index is never used (wasting writes) or doesn't cover the query (requiring expensive table lookups after the index scan).