Postgres et index sur les clés étrangères et les clés primaires

Est-ce que Postgres place automatiquement les index sur les clés étrangères et les clés primaires? Comment puis-je le dire? Existe-t-il une commande qui renverra tous les index sur une table?

PostgreSQL ™ crée automatiquement des index sur les clés primaires et les contraintes uniques, mais pas sur les références aux relations de clés étrangères.

Lorsque Pg crée un index implicite, il émet un message NOTICE niveau que vous pouvez voir dans psql et / ou dans les journaux du système, pour que vous puissiez voir quand cela se produit. Les index créés automatiquement sont également visibles dans une sortie \d pour une table.

La documentation sur les index uniques dit:

PostgreSQL ™ crée automatiquement un index pour chaque contrainte unique et contrainte de clé primaire afin d’imposer l’unicité. Il n’est donc pas nécessaire de créer un index explicitement pour les colonnes de clé primaire.

et la documentation sur les contraintes dit:

Étant donné qu’un DELETE d’une ligne de la table référencée ou une UPDATE d’une colonne référencée nécessitera une parsing de la table de référencement pour les lignes correspondant à l’ancienne valeur, il est souvent utile d’indexer les colonnes de référence. Étant donné que cela n’est pas toujours nécessaire et que de nombreuses options d’indexation sont disponibles, la déclaration d’une contrainte de clé étrangère ne crée pas automatiquement un index sur les colonnes faisant référence.

Par conséquent, vous devez créer vous-même des index sur les clés étrangères si vous le souhaitez.

Notez que si vous utilisez des clés étrangères primaires, comme 2 FK en tant que PK dans une table M-to-N, vous aurez un index sur la PK et vous n’aurez probablement pas besoin de créer d’index supplémentaire.

Bien qu’il soit généralement utile de créer un index (ou d’inclure) vos colonnes de clé étrangère côté référencement, cela n’est pas obligatoire. Chaque index que vous ajoutez ralentit légèrement les opérations DML, vous payez donc un coût de performance pour chaque INSERT , UPDATE ou DELETE . Si l’indice est rarement utilisé, il ne vaut peut-être pas la peine de l’avoir.

Si vous souhaitez répertorier les index de toutes les tables de vos schémas dans votre programme, toutes les informations sont disponibles dans le catalogue:

 select n.nspname as "Schema" ,t.relname as "Table" ,c.relname as "Index" from pg_catalog.pg_class c join pg_catalog.pg_namespace n on n.oid = c.relnamespace join pg_catalog.pg_index i on i.indexrelid = c.oid join pg_catalog.pg_class t on i.indrelid = t.oid where c.relkind = 'i' and n.nspname not in ('pg_catalog', 'pg_toast') and pg_catalog.pg_table_is_visible(c.oid) order by n.nspname ,t.relname ,c.relname 

Si vous souhaitez approfondir votre recherche (comme les colonnes et le classement), vous devez consulter pg_catalog.pg_index. Utiliser psql -E [dbname] est pratique pour savoir comment interroger le catalogue.

Oui – pour les clés primaires, non – pour les clés étrangères (plus dans les docs ).

 \d  

dans “psql” affiche une description d’une table contenant tous ses index.

Cette requête répertorie les index manquants sur les clés étrangères , source d’origine .

 -- check for FKs where there is no matching index -- on the referencing side -- or a bad index WITH fk_actions ( code, action ) AS ( VALUES ( 'a', 'error' ), ( 'r', 'ressortingct' ), ( 'c', 'cascade' ), ( 'n', 'set null' ), ( 'd', 'set default' ) ), fk_list AS ( SELECT pg_constraint.oid as fkoid, conrelid, confrelid as parentid, conname, relname, nspname, fk_actions_update.action as update_action, fk_actions_delete.action as delete_action, conkey as key_cols FROM pg_constraint JOIN pg_class ON conrelid = pg_class.oid JOIN pg_namespace ON pg_class.relnamespace = pg_namespace.oid JOIN fk_actions AS fk_actions_update ON confupdtype = fk_actions_update.code JOIN fk_actions AS fk_actions_delete ON confdeltype = fk_actions_delete.code WHERE contype = 'f' ), fk_atsortingbutes AS ( SELECT fkoid, conrelid, attname, attnum FROM fk_list JOIN pg_atsortingbute ON conrelid = attrelid AND attnum = ANY( key_cols ) ORDER BY fkoid, attnum ), fk_cols_list AS ( SELECT fkoid, array_agg(attname) as cols_list FROM fk_atsortingbutes GROUP BY fkoid ), index_list AS ( SELECT indexrelid as indexid, pg_class.relname as indexname, indrelid, indkey, indpred is not null as has_predicate, pg_get_indexdef(indexrelid) as indexdef FROM pg_index JOIN pg_class ON indexrelid = pg_class.oid WHERE indisvalid ), fk_index_match AS ( SELECT fk_list.*, indexid, indexname, indkey::int[] as indexatts, has_predicate, indexdef, array_length(key_cols, 1) as fk_colcount, array_length(indkey,1) as index_colcount, round(pg_relation_size(conrelid)/(1024^2)::numeric) as table_mb, cols_list FROM fk_list JOIN fk_cols_list USING (fkoid) LEFT OUTER JOIN index_list ON conrelid = indrelid AND (indkey::int2[])[0:(array_length(key_cols,1) -1)] @> key_cols ), fk_perfect_match AS ( SELECT fkoid FROM fk_index_match WHERE (index_colcount - 1) <= fk_colcount AND NOT has_predicate AND indexdef LIKE '%USING btree%' ), fk_index_check AS ( SELECT 'no index' as issue, *, 1 as issue_sort FROM fk_index_match WHERE indexid IS NULL UNION ALL SELECT 'questionable index' as issue, *, 2 FROM fk_index_match WHERE indexid IS NOT NULL AND fkoid NOT IN ( SELECT fkoid FROM fk_perfect_match) ), parent_table_stats AS ( SELECT fkoid, tabstats.relname as parent_name, (n_tup_ins + n_tup_upd + n_tup_del + n_tup_hot_upd) as parent_writes, round(pg_relation_size(parentid)/(1024^2)::numeric) as parent_mb FROM pg_stat_user_tables AS tabstats JOIN fk_list ON relid = parentid ), fk_table_stats AS ( SELECT fkoid, (n_tup_ins + n_tup_upd + n_tup_del + n_tup_hot_upd) as writes, seq_scan as table_scans FROM pg_stat_user_tables AS tabstats JOIN fk_list ON relid = conrelid ) SELECT nspname as schema_name, relname as table_name, conname as fk_name, issue, table_mb, writes, table_scans, parent_name, parent_mb, parent_writes, cols_list, indexdef FROM fk_index_check JOIN parent_table_stats USING (fkoid) JOIN fk_table_stats USING (fkoid) WHERE table_mb > 9 AND ( writes > 1000 OR parent_writes > 1000 OR parent_mb > 10 ) ORDER BY issue_sort, table_mb DESC, table_name, fk_name; 

J’aime la façon dont cela est expliqué dans l’article Fonctionnalités de performance géniales d’EclipseLink 2.5

Indexer les clés étrangères

La première fonctionnalité est l’indexation automatique des clés étrangères. La plupart des gens supposent à tort que les bases de données indexent les clés étrangères par défaut. Eh bien, ils ne le font pas. Les clés primaires sont automatiquement indexées, mais les clés étrangères ne le sont pas. Cela signifie que toute requête basée sur la clé étrangère effectuera des parsings de table complètes. Il s’agit de toute relation OneToMany , ManyToMany ou ElementCollection , ainsi que de nombreuses relations OneToOne et de la plupart des requêtes sur des relations impliquant des jointures ou des comparaisons d’object . Cela peut être un problème majeur et vous devez toujours indexer vos champs de clés étrangères.

Pour une PRIMARY KEY , un index sera créé avec le message suivant:

 NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "index" for table "table" 

Pour une FOREIGN KEY , la contrainte ne sera pas créée s’il n’y a pas d’index sur la table référencée.

Un index sur la table de référence n’est pas nécessaire (bien que désiré), et ne sera donc pas implicitement créé.