Duplicate Index Query #2: Partial matches
-- check for containment | |
-- i.e. index A contains index B | |
-- and both share the same first column | |
-- but they are NOT identical | |
WITH index_cols_ord as ( | |
SELECT attrelid, attnum, attname | |
FROM pg_attribute | |
JOIN pg_index ON indexrelid = attrelid | |
WHERE indkey[0] > 0 | |
ORDER BY attrelid, attnum | |
), | |
index_col_list AS ( | |
SELECT attrelid, | |
array_agg(attname) as cols | |
FROM index_cols_ord | |
GROUP BY attrelid | |
), | |
dup_natts AS ( | |
SELECT indrelid, indexrelid | |
FROM pg_index as ind | |
WHERE EXISTS ( SELECT 1 | |
FROM pg_index as ind2 | |
WHERE ind.indrelid = ind2.indrelid | |
AND ( ind.indkey @> ind2.indkey | |
OR ind.indkey <@ ind2.indkey ) | |
AND ind.indkey[0] = ind2.indkey[0] | |
AND ind.indkey <> ind2.indkey | |
AND ind.indexrelid <> ind2.indexrelid | |
) ) | |
SELECT userdex.schemaname as schema_name, | |
userdex.relname as table_name, | |
userdex.indexrelname as index_name, | |
array_to_string(cols, ', ') as index_cols, | |
indexdef, | |
idx_scan as index_scans | |
FROM pg_stat_user_indexes as userdex | |
JOIN index_col_list ON index_col_list.attrelid = userdex.indexrelid | |
JOIN dup_natts ON userdex.indexrelid = dup_natts.indexrelid | |
JOIN pg_indexes ON userdex.schemaname = pg_indexes.schemaname | |
AND userdex.indexrelname = pg_indexes.indexname | |
ORDER BY userdex.schemaname, userdex.relname, cols, userdex.indexrelname; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment