PostgreSQL: indexes that actually work

An index is a trade-off

Any index speeds up reads but slows down writes and takes up space. That is why indexes are added not just in case but for specific queries confirmed by the execution plan.

Reading the query plan

EXPLAIN ANALYZE shows exactly how the database executes a query: whether it uses an index, whether it does a sequential scan, how many rows it discards. This is the main tool for diagnosing slow queries.

What to look for in the plan

• A Seq Scan on a large table where an index was expected.
• A large difference between the estimate and the actual number of rows — outdated statistics.
• An expensive sort that an index could have covered.

Choosing the index type

B-tree is suitable for equality and ranges and covers most cases. GIN is indispensable for JSONB and full-text search. Partial indexes save space by indexing only the needed subset of rows, while covering indexes let you answer a query without accessing the table.

Common mistakes

Duplicate indexes, indexes on columns with low selectivity and forgotten statistics maintenance are typical reasons why indexes do not help. Regular analysis and review of indexes against real queries restore their usefulness.

Conclusion

Effective indexing is a discipline of measurement: look at the plan, add a targeted index, check the result and remove what brings no benefit.