Indexes speed lookups and joins. Postgres supports B-tree (default), Hash, GiST, GIN, BRIN—pick the access method matching query patterns.
B-tree indexes
CREATE INDEX idx_orders_customer ON orders (customer_id);
CREATE INDEX idx_orders_created ON orders (created_at DESC);
EXPLAIN SELECT * FROM orders WHERE customer_id = 42;Practice: Apply DDL in a throwaway practice database. Use \d table in psql to verify constraints and indexes.
Composite indexes
CREATE INDEX idx_orders_customer_created
ON orders (customer_id, created_at DESC);Leftmost prefix rule: index helps when filter includes leading column(s).
Index-only scans and maintenance
Covering indexes (INCLUDE columns) reduce heap fetches. Indexes slow writes—create for proven predicates in EXPLAIN, not speculatively on every column.
Important interview questions and answers
- Q: Primary key index?
A: PRIMARY KEY creates a unique B-tree index automatically. - Q: Too many indexes?
A: Each index adds write amplification and storage—measure with EXPLAIN ANALYZE.
Self-check
- Which index type is default?
- Why index foreign key columns?
Tip: Index foreign keys and frequent WHERE columns before micro-optimizing SELECT lists.
Interview prep
- Default index type?
B-tree for most equality and range queries.
- Index trade-off?
Faster reads, slower writes, more storage.