Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
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.
1 2 3 4 5 6 7 8 9 | 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; |
