Finding foreign keys without an index
PostgreSQL doesn't index FKs automatically: every DELETE on the parent then triggers a seq scan of the child. This catalog query lists FKs whose first column isn't indexed anywhere.
Prerequisites
PostgreSQL (catalogues pg_constraint / pg_index)
SQL
SELECT
c.conrelid::regclass AS table_name,
c.conname AS fk_name,
string_agg(a.attname, ', ') AS fk_columns
FROM pg_constraint c
JOIN pg_attribute a
ON a.attrelid = c.conrelid AND a.attnum = ANY (c.conkey)
WHERE c.contype = 'f'
AND NOT EXISTS (
SELECT 1
FROM pg_index i
WHERE i.indrelid = c.conrelid
AND i.indkey[0] = c.conkey[1] -- 1re colonne de l'index = 1re de la FK
)
GROUP BY c.conrelid, c.conname
ORDER BY table_name;Result
table_name | fk_name | fk_columns -------------+---------------------------+------------- order_items | order_items_order_id_fkey | order_id payments | payments_invoice_id_fkey | invoice_id shipments | shipments_order_id_fkey | order_id (3 rows) -- 3 FK sans index : DELETE parent = seq scan enfant
SQLForeign KeyIndexCatalogue