stratoflow
Published: Jul 6, 2021
How to find missing foreign key indexes in PostgreSQL
Would you like to quickly find and generate missing foreign key indexes in PostgreSQL?
If you ever wondered how to almost immediately speed up your PostgreSQL database when it is created without proper foreign key indexes, e.g. with schema generated by JPA, you may want to use the following query to collect candidate indexes:
select 'CREATE INDEX idx_'||conname||' ON '||conrelid|| ' ('||attname||');' from (
SELECT conrelid::regclass
,conname
,reltuples::bigint,unnest(conkey) coloid
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 conkey @> indkey AND conkey <@ indkey
)
ORDER BY reltuples DESC
) as cols
join pg_attribute pga on pga.attrelid = cols.conrelid and pga.attnum=cols.coloid
;It will return a list of DDL creating missing foreign key indexes ready for execution after your review:
"CREATE INDEX idx_fk_4ywoftpugx4dycijy8i9tyhwb ON profile (project_id);" "CREATE INDEX idx_fk_o4al1lv1rgjw8m8xvrwnv797f ON profile_user_data (profile_id);" "CREATE INDEX idx_fk_euwbenmq4r06p1k5cjlypng8s ON profile_user_data (user_id);" "CREATE INDEX idx_fk_b7i81l1tk1ph95xnhtoftyv53 ON task (project_id);" "CREATE INDEX idx_fk_1ytmg44m35ff160cofosaum8h ON user_projects (project_id);" "CREATE INDEX idx_fk_8xi9c7nbxov8bdubwdcgahnjp ON user_projects (user_id);"
