Indexes function like a double-edged scalpel in query optimization: precise and lifesaving when wielded skillfully, but capable of impeding a database’s performance if used carelessly. Any query can be sped up by adding the RIGHT indexes. You have a query with an execution time > 1s? Add an index for the columns in the query condition, and voilà! The execution time crashes to below 1ms. This is because an index is essentially a shortcut to the physical location of the data the query intends to retrieve.

The cost of indexes

1. Disk utilization: An index is essentially a subset of the main table structured differently. So it occupies its own space(duh). The size of an index will depend on the index type (inverted indexes, such as GIN, typically use more space than B+trees) and the number of columns that make up the index.
2. Memory buffer utilization: PostgreSQL uses a finite memory buffer to cache the pages of relations (tables, indexes, etc.) to reduce disk I/O costs. Relation data in disk and memory is segmented into pages (8 KB in size) and accessed in pages. During query execution, pages that contain the rows being scanned are first looked up in the buffer. If missing, they are loaded from disk and added to the buffer. If the buffer is full, a page replacement algorithm is used to replace pages that are rarely accessed with those that are accessed more recently. Index pages also occupy the memory buffer.
3. Write overhead: An index contains data from the main table and a pointer (not a C/C++ pointer by the way) to the exact physical location of the associated row in the main table. Because indexes need to be in sync with the main table, table inserts and updates result in an index update. INSERTs result in updates to all indexes in the table. For most UPDATEs, all indexes are also updated, including indexes that don’t contain the column(s) being updated. This is because an update in PostgreSQL results in the existing row being marked as dead, and an insertion of a new version of the row with the updated data in a new location. So all indexes need to be updated with the location of the latest version of the row. If a table has 100 indexes, for example, all 100 indexes will be updated when a new row is inserted or an existing one is updated. An exception to this is HOT (Heap Only Tuple) updates, which avoid index updates.
4. Query planner decision fatigue: An abundance of options often requires more effort to make a decision and increases the probability of making the wrong one. That’s true for many things in life and for the Postgres query planner. Postgres uses a cost-based planner to determine how to execute a query. The cost of all possible execution paths is estimated using arbitrary units and statistics it collects during VACUUM and ANALYZE, and the cheapest path is selected. One factor that contributes to the overall cost of a path is the scan types (seq scan, index scan, index only scan, bitmap scan, etc) and the specific indexes (for index scans). More indexes may result in longer planning times, as the planner must evaluate all relevant indexes for the query. Also, because cost estimation is based on often outdated single-column statistics, the query planner’s estimations are off sometimes. For example, by the query planner’s estimation, index A may have a slightly cheaper cost than index B, so it chooses index A, but in reality, index B offers better performance. The probability of this happening increases with the number of indices.

• Wasted disk space.
• Wasted occupancy of memory buffer, which may contribute to cache misses for pages of tables and actively used indexes, thus slowing down reads and writes.
• Unnecessary write overhead since Postgres updates all the indexes in a table during inserts and updates (except for HOT updates).

Factors that could contribute to redundant index accumulation and how to prevent them

• Premature indexing: Developers often blindly speculate on the present/future necessity of an index, so they create an index prematurely to speed up a query that isn’t slow. For new tables, (non-unique) indexes will likely be ignored by the query planner until the table grows to a certain size. And by the time the table reaches that size, the query that the index was intended to speed up may have been removed or changed in the application. A good rule of thumb is to have comprehensive monitoring in place for SQL queries and only consider creating indexes to speed up a query when there is a noticeable degradation in the performance of the query.
• Improper indexing: Lack of understanding of the index type, its structure, and how it’s traversed often leads to the creation of redundant indexes. From my experience, this happens mostly with multi-column indexes intended to speed up queries that filter by multiple columns. Indexing a single column is straightforward, but for multi-column indexes, the order of columns in the index is very important and can affect the usability of the index and the cost of using it. The ability to decide on the ideal order of the columns for a multi-column index is an important skill to have. The usetheindexluke book covers this.
• Excessive indexing: Developers sometimes introduce a new index without checking if an existing one could be modified to improve the query being optimized. Depending on the query, sometimes, simply adding a column to an existing single-column index or switching the order of an existing multi-column index could make an existing index useful for both existing queries and the new query you’re trying to optimize.
• Lack of query testing: SQL provides a great tool to analyze the execution plan and cost of a query, and that’s the EXPLAIN command. “Plan-reading is an art that requires some experience to master” — Postgres docs. The Postgres docs cover the basics of the EXPLAIN output. After creating an index to speed up a query, it’s important to run the EXPLAIN command (with ANALYZE for reads) against the query to verify that the index you created is indeed used. Remember to run VACUUM (without FULL) before testing.
• Removed/changed queries: Queries for which the indexes were introduced to speed up have been removed from the application or changed in a way that makes the indexes no longer usable for them.

How to identify redundant indexes in Postgres

SELECT i.relname, i.indexrelname, i.idx_scan
FROM pg_stat_user_indexes i
JOIN pg_index ix ON i.indexrelid = ix.indexrelid
WHERE i.idx_scan = 0
  AND ix.indisunique = false  -- Exclude unique indexes
ORDER BY i.relname;

      relname       |              indexrelname              | idx_scan 
--------------------+----------------------------------------+----------
 addresses          | addresses_vendor_id_index              |        0
 auth_access_tokens | auth_access_tokens_tokenable_id_index  |        0
 batches            | batches_product_id_index               |        0
 batches            | batches_vendor_id_index                |        0
 organizations      | organizations_owner_id_index           |        0
 products           | products_organization_id_index         |        0
 products           | products_branch_id_index               |        0

-- Indexes that have never been used at all or haven't been used in 30 days:
SELECT i.relname, i.indexrelname, i.idx_scan, last_idx_scan
FROM pg_stat_user_indexes i
JOIN pg_index ix ON i.indexrelid = ix.indexrelid
  AND ix.indisunique = false  -- Exclude unique indexes
AND(last_idx_scan IS NULL OR last_idx_scan < NOW() - INTERVAL '30 day')
ORDER BY i.relname;