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

Question

Postgres est automatiquement met les index sur des clés étrangères et les clés primaires ? Comment puis-je savoir ? Y at-il une commande qui retourne tous les index sur une table ?

Answer 1

PostgreSQL crée automatiquement des index sur les clés primaires et les contraintes unique, mais pas sur le référencement côté des relations de clé étrangère.

Lorsque Pg crée un indice implicite, il émet un NOTICE-niveau de message que vous pouvez voir en psql et/ou les journaux du système, de sorte que vous pouvez voir quand il arrive. Automatiquement créé des index sont visibles en \d de sortie pour une table, trop.

La documentation sur les index uniques , dit:

PostgreSQL crée automatiquement un index pour chaque contrainte d'unicité et de la contrainte de clé primaire pour renforcer l'unicité. Ainsi, il n'est pas nécessaire de créer un index explicitement pour les colonnes de clé primaire.

et de la documentation sur les contraintes , dit:

Depuis une SUPPRESSION d'une ligne dans la table référencée ou une mise à JOUR d'un la colonne référencée nécessitera une analyse de la table de référence pour les lignes correspondant à l'ancienne valeur, il est souvent une bonne idée de l'indice de la le référencement de colonnes. Parce que ce n'est pas toujours nécessaire, et il y a beaucoup de choix disponibles sur la façon d'index, la déclaration d'une clé étrangère la contrainte n'est pas automatiquement la création d'un index sur le référencement les colonnes.

Par conséquent, vous devez créer des index sur les touches vous-même si vous le souhaitez.

Notez que si vous utilisez primaire étrangères-clés, comme 2 FK est comme un PK dans un M-N table, vous aurez un indice sur le PK et probablement n'avez pas besoin de créer un extra index.

Alors que c'est généralement une bonne idée de créer un index sur (ou de) votre référencement du côté des colonnes de clé étrangère, il n'est pas nécessaire. Chaque indice vous ajoutez ralentit les opérations DML baisse légèrement, de sorte que vous payez un coût de performance sur chaque INSERT, UPDATE ou DELETE. Si l'index est rarement utilisé, il peut ne pas être la peine d'avoir.

Answer 2

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.

Answer 3

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

 \d <table_name>
 

dans "psql" affiche une description d'une table contenant tous ses index.

Answer 4

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

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

Pour un FOREIGN KEY , la contrainte ne sera pas créée s'il n'y a pas d'index sur la table de référence ** ed **.

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