Clefs étrangères

Créer des index sur les clés étrangères (colonnes FOREIGN KEY) n’est pas une obligation. Il est cependant fréquent qu’une jointure sur cette clé pose un problème de performance parce qu’il n’y a pas d’index.

La requête suivante donne les ordres de création sur les clés étrangères non indexées.

Elle est extraite de pgCluu.

-- Attention : peut créer des index sur des colonnes déjà indexées avec un index composé !

SELECT relname,
'CREATE INDEX CONCURRENTLY idx_' || relname || '_' ||
         array_to_string(column_name_list, '_') || ' ON ' || conrelid ||
         ' (' || array_to_string(column_name_list, ',') || ')'
         || CASE WHEN COUNT(DISTINCT redi.indexrelid) >0 THEN '  /* maybe redundant with: '|| string_agg (redi.indexrelid::regclass::text,', ') || ' */' ELSE '' END
         AS ddl
FROM (SELECT DISTINCT conrelid,
       array_agg(attname) AS column_name_list,
       array_agg(attnum) AS column_list
     FROM pg_attribute
          JOIN (-- existing constraints
                SELECT conrelid::regclass, conname,
                 unnest(conkey) AS column_index
                FROM (SELECT DISTINCT conrelid, conname, conkey
                      FROM pg_constraint
                        JOIN pg_class ON pg_class.oid = pg_constraint.conrelid
                        JOIN pg_namespace ON pg_namespace.oid = pg_class.relnamespace
                      WHERE NOT pg_class.relispartition AND nspname !~ '^pg_'
                         AND nspname <> 'information_schema' AND pg_constraint.contype = 'f'
                      ) fkey
               ) fkey
               ON fkey.conrelid = pg_attribute.attrelid
                  AND fkey.column_index = pg_attribute.attnum
     GROUP BY conrelid, conname
     ) candidate_index
JOIN pg_class ON pg_class.oid = candidate_index.conrelid
LEFT JOIN pg_index i ON i.indrelid = conrelid
                      AND i.indkey::text = array_to_string(column_list, ' ')
-- potentially redundant existing indexes
LEFT JOIN pg_index redi ON redi.indrelid = conrelid
                       AND column_list  <@ redi.indkey::SMALLINT[] -- contains
                       AND redi.indkey::text != array_to_string(column_list, ' ')
WHERE i.indrelid IS NULL
GROUP BY relname, conrelid, column_name_list
ORDER BY ddl ;

Exemple

Sur une base pgbench par défaut (sans index de clés étrangères), la requête ci-dessus renvoie:

     relname      |                                     ddl                                      
------------------+------------------------------------------------------------------------------
 pgbench_accounts | CREATE INDEX CONCURRENTLY idx_pgbench_accounts_bid ON pgbench_accounts (bid)
 pgbench_history  | CREATE INDEX CONCURRENTLY idx_pgbench_history_aid ON pgbench_history (aid)
 pgbench_history  | CREATE INDEX CONCURRENTLY idx_pgbench_history_bid ON pgbench_history (bid)
 pgbench_history  | CREATE INDEX CONCURRENTLY idx_pgbench_history_tid ON pgbench_history (tid)
 pgbench_tellers  | CREATE INDEX CONCURRENTLY idx_pgbench_tellers_bid ON pgbench_tellers (bid)
(5 lignes)