Last active
April 21, 2023 04:52
-
-
Save sunaoka/2dd341c7e41c7ab40e21908edbecb70e to your computer and use it in GitHub Desktop.
PostgreSQL でインデックスが張られていない外部キーを探す
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
WITH y AS ( | |
SELECT | |
pg_catalog.format('%I.%I', n1.nspname, c1.relname) AS referencing_tbl, | |
pg_catalog.quote_ident(a1.attname) AS referencing_column, | |
t.conname AS existing_fk_on_referencing_tbl, | |
pg_catalog.format('%I.%I', n2.nspname, c2.relname) AS referenced_tbl, | |
pg_catalog.quote_ident(a2.attname) AS referenced_column, | |
pg_relation_size( pg_catalog.format('%I.%I', n1.nspname, c1.relname) ) AS referencing_tbl_bytes, | |
pg_relation_size( pg_catalog.format('%I.%I', n2.nspname, c2.relname) ) AS referenced_tbl_bytes, | |
pg_catalog.format($$CREATE INDEX %I_%I_idx ON %I.%I(%I);$$, c1.relname, a1.attname, n1.nspname, c1.relname, a1.attname) AS suggestion | |
FROM pg_catalog.pg_constraint t | |
JOIN pg_catalog.pg_attribute a1 ON a1.attrelid = t.conrelid AND a1.attnum = t.conkey[1] | |
JOIN pg_catalog.pg_class c1 ON c1.oid = t.conrelid | |
JOIN pg_catalog.pg_namespace n1 ON n1.oid = c1.relnamespace | |
JOIN pg_catalog.pg_class c2 ON c2.oid = t.confrelid | |
JOIN pg_catalog.pg_namespace n2 ON n2.oid = c2.relnamespace | |
JOIN pg_catalog.pg_attribute a2 ON a2.attrelid = t.confrelid AND a2.attnum = t.confkey[1] | |
WHERE t.contype = 'f' | |
AND NOT EXISTS ( | |
SELECT 1 | |
FROM pg_catalog.pg_index i | |
WHERE i.indrelid = t.conrelid | |
AND i.indkey[0] = t.conkey[1] | |
) | |
) | |
SELECT referencing_tbl, | |
referencing_column, | |
existing_fk_on_referencing_tbl, | |
referenced_tbl, | |
referenced_column, | |
pg_size_pretty(referencing_tbl_bytes) AS referencing_tbl_size, | |
pg_size_pretty(referenced_tbl_bytes) AS referenced_tbl_size, | |
suggestion | |
FROM y | |
ORDER BY | |
referencing_tbl_bytes DESC, | |
referenced_tbl_bytes DESC, | |
referencing_tbl, | |
referenced_tbl, | |
referencing_column, | |
referenced_column; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
http://everything-you-do-is-practice.blogspot.com/2017/09/postgresql-v92x.html