PostgreSQL Indexing Basics

Without an index, PostgreSQL answers a query with a sequential scan — reading every single row in the table to check if it matches. An index lets it jump straight to matching rows instead, the same trade-off MongoDB makes with its own indexes.

Creating a Basic Index

CREATE INDEX idx_employees_department ON employees (department);

Every table also gets an automatic index on its PRIMARY KEY, and PostgreSQL creates one automatically for UNIQUE constraints too.

Seeing the Difference: EXPLAIN

EXPLAIN SELECT * FROM employees WHERE department = 'Engineering';

Before an index exists:

Seq Scan on employees  (cost=0.00..25.88 rows=6 width=64)
  Filter: (department = 'Engineering'::text)

After creating the index:

Index Scan using idx_employees_department on employees  (cost=0.28..8.45 rows=6 width=64)
  Index Cond: (department = 'Engineering'::text)

Seq Scan → Index Scan is exactly the improvement an index provides (see the EXPLAIN lesson for a deeper dive into reading this output).

Composite (Multi-Column) Indexes

CREATE INDEX idx_dept_salary ON employees (department, salary);

Speeds up queries that filter on department alone, or department and salary together — but not salary alone, since column order in the index matters.

Dropping an Index

DROP INDEX idx_employees_department;

The Trade-Off: Indexes Aren't Free

• Indexes speed up reads (SELECT), but slightly slow down writes (INSERT/UPDATE/DELETE), since every index also has to be updated.
• Indexes take up disk space of their own.
• Only index columns you actually filter, join, or sort by often — indexing everything "just in case" hurts write performance for no benefit.

Common Errors & Troubleshooting

• Creating an index but EXPLAIN still shows Seq Scan — the planner sometimes decides a sequential scan is genuinely faster on a small table, or when the query would match most of the rows anyway — this isn't always a bug; try filtering to fewer rows to see the index kick in, or trust the planner on small tables.
• Statistics out of date after a big bulk load — run ANALYZE tablename; after loading a large amount of new data so the planner's row estimates are accurate (see the Query Optimization lesson).
• Adding an index and write performance getting noticeably worse — every index has to be updated on every insert/update/delete; if a table is write-heavy and rarely queried on a given column, that index may be costing more than it's worth.

Key Takeaways

• Without an index, Postgres falls back to scanning every row — EXPLAIN is how you confirm whether an index is actually being used.
• A composite index's column order matters: it helps the leading column(s) most.
• Indexes trade faster reads for slightly slower writes — index deliberately, not everything.