Find Unused and Duplicate Indexes

Every index is paid for on every write, and it takes space on disk and in memory. Indexes added "just in case", and the same index created twice by two people, are common on older tables. PostgreSQL keeps counters for each index, so you can find the ones that do nothing. This page builds a table with both problems, reads the counters, finds the identical pair, and drops the useless ones without blocking writes.

Step 1: a table with too many indexes

The table has a primary key, a total index nobody uses, two identical indexes on customer_id, and a two-column index that starts with the same column.

CREATE TABLE big_orders AS
SELECT g AS order_id,
       (g % 8) + 1 AS customer_id,
       DATE '2026-01-01' + (g % 365) AS order_date,
       ((g % 97) + 0.5)::numeric(8,2) AS total
FROM generate_series(1, 20000) AS g;
ALTER TABLE big_orders ADD PRIMARY KEY (order_id);
CREATE INDEX orders_customer_idx      ON big_orders (customer_id);
CREATE INDEX orders_customer_idx2     ON big_orders (customer_id);
CREATE INDEX orders_customer_date_idx ON big_orders (customer_id, order_date);
CREATE INDEX orders_total_idx         ON big_orders (total);
ANALYZE big_orders;

SELECT 20000
ALTER TABLE
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
ANALYZE

Step 2: run some real queries

Reset the statistics, so the counters start at zero, and run four queries that the application might send.

SELECT pg_stat_reset() IS NOT NULL AS reset_done;
SELECT * FROM big_orders WHERE order_id = 77;
SELECT * FROM big_orders WHERE order_id = 78;
SELECT count(*) FROM big_orders WHERE customer_id = 3 AND order_date >= DATE '2026-12-01';
SELECT count(*) FROM big_orders WHERE customer_id = 3;

 reset_done
------------
 t
(1 row)

 order_id | customer_id | order_date | total
----------+-------------+------------+-------
       77 |           6 | 2026-03-19 | 77.50
(1 row)

 order_id | customer_id | order_date | total
----------+-------------+------------+-------
       78 |           7 | 2026-03-20 | 78.50
(1 row)

 count
-------
   209
(1 row)

 count
-------
  2500
(1 row)

PostgreSQL sends the counters from a session in batches. A real server collects them for you over days. In a test script, ask for them to be flushed now.

SELECT pg_stat_force_next_flush();

 pg_stat_force_next_flush
--------------------------

(1 row)

Step 3: list indexes that were never scanned

The pg_stat_user_indexes view counts index scans. Leave out the unique indexes, because they enforce a rule even if no query reads them.

SELECT s.indexrelname, s.idx_scan, pg_size_pretty(pg_relation_size(s.indexrelid)) AS size
FROM pg_stat_user_indexes AS s
JOIN pg_index AS i USING (indexrelid)
WHERE s.relname = 'big_orders' AND s.idx_scan = 0 AND NOT i.indisunique
ORDER BY s.indexrelname;

    indexrelname     | idx_scan |  size
---------------------+----------+--------
 orders_customer_idx |        0 | 152 kB
 orders_total_idx    |        0 | 456 kB
(2 rows)

orders_total_idx was never used, and it is the largest of the group. orders_customer_idx shows zero scans for another reason, which the next step explains.

Step 4: find identical indexes

Two indexes are identical when they have the same table, columns, operator classes, collations, options, expressions and condition. The query below groups the catalog by all of those.

SELECT indrelid::regclass AS table_name,
       array_agg(indexrelid::regclass ORDER BY indexrelid) AS identical_indexes
FROM pg_index
WHERE indrelid = 'big_orders'::regclass
GROUP BY indrelid, indkey, indclass, indcollation, indoption,
         indexprs::text, indpred::text, indisunique
HAVING count(*) > 1;

 table_name |             identical_indexes
------------+--------------------------------------------
 big_orders | {orders_customer_idx,orders_customer_idx2}
(1 row)

Two indexes on customer_id do the same job. The planner picked one of them for the last query, orders_customer_idx2, and left the other with zero scans, so the counter alone would not tell you that the idle one is a copy. Keep one and drop the other.

There is a third kind that the query does not find: a prefix. orders_customer_date_idx starts with customer_id, so it can answer every query that a single-column index on customer_id answers. When you have the two-column index, the single-column ones are often redundant. Decide by looking at the queries, because the two-column index is bigger and a little slower for the one-column queries.

Step 5: drop them without blocking writes

DROP INDEX CONCURRENTLY orders_customer_idx;
DROP INDEX CONCURRENTLY orders_total_idx;
SELECT indexrelid::regclass AS index_name FROM pg_index
WHERE indrelid = 'big_orders'::regclass ORDER BY indexrelid;

DROP INDEX
DROP INDEX
        index_name
--------------------------
 big_orders_pkey
 orders_customer_idx2
 orders_customer_date_idx
(3 rows)

Before you do this on a real database, check four things. The statistics must cover a full business cycle, including month-end jobs and reports. A replica keeps its own counters, so look at them too. An index that backs a primary key, a unique constraint or an exclusion constraint cannot be dropped with DROP INDEX. And keep the CREATE INDEX statement of every index you drop, so you can bring it back with CREATE INDEX CONCURRENTLY.

Related course: SQL Advanced.