Learning PostgreSQL 11 - Third Edition

Best practices for indexes

It is often useful to index columns that are used with predicates and foreign keys. This enables PostgreSQL to use an index scan instead of a sequential scan. The benefits of indexes are not limited to the SELECT statements; DELETE and UPDATE statements can also benefit from them. There are some cases where an index is not used, and this is often due to small table size. In big tables, we should plan the space capacity carefully, since the index size might be very big. Also, note that indexes have a negative impact on INSERT statements, since amending the index comes with a cost.

There are several catalog tables and functions that help to maintain indexes, such as pg_stat_all_indexes, which gives statistics about ...