Skip to content

Instantly share code, notes, and snippets.

@jonasraoni
Created November 4, 2017 09:27
Show Gist options
  • Save jonasraoni/51fa64b3844990a80b1863592a489b45 to your computer and use it in GitHub Desktop.
Save jonasraoni/51fa64b3844990a80b1863592a489b45 to your computer and use it in GitHub Desktop.
PostgreSQL SELECT statement to find/create foreign keys that are missing indexes
-- find missing indexes
SELECT
conrelid::regclass, conname, reltuples::bigint
FROM
pg_constraint
JOIN pg_class
ON conrelid = pg_class.oid
WHERE
contype = 'f'
AND NOT EXISTS (
SELECT 1
FROM
pg_index
WHERE
indrelid = conrelid
AND (
SELECT
ARRAY(
SELECT
conkey[i]
FROM
generate_series(array_lower(conkey, 1), array_upper(conkey, 1)) i
ORDER BY
1
)
) = (
SELECT
ARRAY(
SELECT
indkey[i]
FROM
generate_series(array_lower(indkey, 1), array_upper(indkey, 1)) i
ORDER BY
1
)
)
)
ORDER BY
reltuples DESC
-- generate statements
SELECT 'CREATE INDEX idx_' || table_name || '_' || column_name || ' ON '
|| foreign_schema || '.' || table_name || '(' || column_name || ');'
FROM (
SELECT
n.nspname AS schema,
cl.relname AS table_name,
a.attname AS column_name,
ct.conname AS key_name,
nf.nspname AS foreign_schema,
clf.relname AS foreign_table_name,
af.attname AS foreign_column_name,
pg_get_constraintdef(ct.oid) AS create_sql
FROM
pg_catalog.pg_attribute a
JOIN pg_catalog.pg_class cl
ON a.attrelid = cl.oid AND cl.relkind = 'r'
JOIN pg_catalog.pg_namespace n
ON n.oid = cl.relnamespace
JOIN pg_catalog.pg_constraint ct
ON a.attrelid = ct.conrelid
AND ct.confrelid != 0
AND ct.conkey[1] = a.attnum
JOIN pg_catalog.pg_class clf
ON ct.confrelid = clf.oid
AND clf.relkind = 'r'
JOIN pg_catalog.pg_namespace nf
ON nf.oid = clf.relnamespace
JOIN pg_catalog.pg_attribute af
ON af.attrelid = ct.confrelid
AND af.attnum = ct.confkey[1]
) X
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment