Mac

When designing a large DB using PostgreSQL it’s actually quite easy to create duplicate indexes. Duplicate indexes are bad because:
- They don’t bring any value added
- They take disk space
- They take memory space (when cached in the disk buffers)
- They make INSERT / UPDATE slower (those commands need to write in two index files instead of just one)

An easy way to spot the duplicate indexes is to run the following command:


select max(what.relname),
min(what.relname),
whatfor.relname,
replace(pg_get_indexdef(what.oid, 0, true), ' ' || what.relname || ' ', ' IDX_NAME ') as create,
count(*)
from pg_index
join pg_class as whatfor on indrelid = whatfor.oid
join pg_class as what on indexrelid = what.oid
group by whatfor.relname, replace(pg_get_indexdef(what.oid, 0, true), ' ' || what.relname || ' ', ' IDX_NAME ')
having count(*) > 1

This will output the name of 2 indexes which are suspected to be similar as well as their respective CREATE commands.

It will only spot the simple indexes (ie no functions) which are the same, but it’s usually enough to determine most of the problems.