PostgreSQL Duplicate Index Detection

Faruk Erdem

2 months ago

In this article, we will discuss the detection and prevention of indexes created on the same columns in PostgreSQL.

In this way, we can both increase query performance and prevent unnecessary disk space usage.

SELECT pg_size_pretty(sum(pg_relation_size(idx))::bigint) as indexsize,
       (array_agg(idx))[1] as index1name, (array_agg(idx))[2] as index2name,
       (array_agg(idx))[3] as index3name, (array_agg(idx))[4] as index4name
FROM (
    SELECT indexrelid::regclass as idx, (indrelid::text ||E'\n'|| indclass::text ||E'\n'|| indkey::text ||E'\n'||
                                         coalesce(indexprs::text,'')||E'\n' || coalesce(indpred::text,'')) as key
    FROM PG_INDEX) sub
GROUP BY key HAVING count(*)>1
ORDER BY sum(pg_relation_size(idx)) DESC;

SELECT pg_size_pretty(sum(pg_relation_size(idx))::bigint) as indexsize,

(array_agg(idx))[1] as index1name, (array_agg(idx))[2] as index2name,

(array_agg(idx))[3] as index3name, (array_agg(idx))[4] as index4name

FROM (

SELECT indexrelid::regclass as idx, (indrelid::text ||E'\n'|| indclass::text ||E'\n'|| indkey::text ||E'\n'||

coalesce(indexprs::text,'')||E'\n' || coalesce(indpred::text,'')) as key

FROM PG_INDEX) sub

GROUP BY key HAVING count(*)>1

ORDER BY sum(pg_relation_size(idx)) DESC;