Created
May 3, 2021 10:06
Star
You must be signed in to star a gist
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/* Background and source: https://www.cybertec-postgresql.com/en/index-your-foreign-key/ */ | |
SELECT c.conrelid::regclass AS "table", | |
/* list of key column names in order */ | |
string_agg(a.attname, ',' ORDER BY x.n) AS columns, | |
pg_catalog.pg_size_pretty( | |
pg_catalog.pg_relation_size(c.conrelid) | |
) AS size, | |
c.conname AS constraint, | |
c.confrelid::regclass AS referenced_table | |
FROM pg_catalog.pg_constraint c | |
/* enumerated key column numbers per foreign key */ | |
CROSS JOIN LATERAL | |
unnest(c.conkey) WITH ORDINALITY AS x(attnum, n) | |
/* name for each key column */ | |
JOIN pg_catalog.pg_attribute a | |
ON a.attnum = x.attnum | |
AND a.attrelid = c.conrelid | |
WHERE NOT EXISTS | |
/* is there a matching index for the constraint? */ | |
(SELECT 1 FROM pg_catalog.pg_index i | |
WHERE i.indrelid = c.conrelid | |
/* it must not be a partial index */ | |
AND i.indpred IS NULL | |
/* the first index columns must be the same as the | |
key columns, but order doesn't matter */ | |
AND (i.indkey::smallint[])[0:cardinality(c.conkey)-1] | |
OPERATOR(pg_catalog.@>) c.conkey) | |
AND c.contype = 'f' | |
GROUP BY c.conrelid, c.conname, c.confrelid | |
ORDER BY pg_catalog.pg_relation_size(c.conrelid) DESC; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment