Indexing and Query Planning

Why Indexes Speed Things Up

Without an index, PostgreSQL scans the whole table to find rows. An index is an ordered structure that lets it jump straight to the matches.

Creating Indexes

CREATE INDEX idx_customers_email ON customers(email);
CREATE UNIQUE INDEX idx_customers_phone ON customers(phone);
CREATE INDEX idx_orders_customer ON orders(customer_id);

Special Index Types

• GIN: best for arrays and jsonb.
• GiST: for geometry, ranges and full-text.
• BRIN: compact index for huge, naturally ordered tables (e.g. time-series).

Explaining Query Plans

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM customers WHERE email = 'priya@example.com';

Read the plan to see whether an index is used (Index Scan) or the whole table is walked (Seq Scan).

Key Points

• Index the columns used in WHERE and JOIN.
• GIN, GiST and BRIN answer specific data shapes.
• EXPLAIN ANALYZE reveals actual query behaviour.
• Indexes cost write speed and disk; index with intent.