Detectar las claves foráneas sin índice
PostgreSQL no indexa las FK automáticamente: cada DELETE en el padre desencadena entonces un seq scan del hijo. Esta consulta al catálogo lista las FK cuya primera columna no está indexada en ningún sitio.
Requisitos
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;Resultado
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