Postgres und Indizes auf Fremdschlüsseln und Primärschlüsseln

Question

Setzt Postgres automatisch Indizes auf Foreign Keys und Primary Keys? Wie kann ich das feststellen? Gibt es einen Befehl, der alle Indizes für eine Tabelle zurückgibt?

Answer 1

PostgreSQL erstellt automatisch Indizes für Primärschlüssel und Unique Constraints, aber nicht für die referenzierende Seite von Fremdschlüsselbeziehungen.

Wenn Pg einen impliziten Index erstellt, gibt es eine NOTICE -Ebene Nachricht, die Sie in psql und/oder die Systemprotokolle, damit Sie sehen können, wann es passiert. Automatisch erstellte Indizes sind sichtbar in \d Ausgabe auch für eine Tabelle.

En Dokumentation über eindeutige Indizes dit :

PostgreSQL erstellt automatisch einen Index für jede eindeutige Beschränkung und Primärschlüssel-Beschränkung, um die Einzigartigkeit zu erzwingen. Es ist also nicht notwendig, explizit einen Index für Primärschlüsselspalten zu erstellen.

und die Dokumentation auf Zwänge dit :

Da ein DELETE einer Zeile aus der referenzierten Tabelle oder ein UPDATE einer einer referenzierten Spalte einen Scan der referenzierenden Tabelle für Zeilen mit dem alten Wert übereinstimmen, ist es oft eine gute Idee, die referenzierenden Spalten zu indizieren. Da dies nicht immer erforderlich ist, und es gibt viele Möglichkeiten gibt, wie man indexieren kann, ist die Deklaration eines Fremdschlüssels Einschränkung nicht automatisch einen Index für die referenzierende Spalten.

Daher müssen Sie selbst Indizes für fremde Schlüssel erstellen, wenn Sie diese benötigen.

Beachten Sie, dass Sie bei der Verwendung von primären Fremdschlüsseln, z. B. 2 FKs als PK in einer M-zu-N-Tabelle, einen Index auf den PK haben und wahrscheinlich keine zusätzlichen Indizes erstellen müssen.

Obwohl es normalerweise eine gute Idee ist, einen Index auf (oder einschließlich) Ihrer referenzierenden Fremdschlüsselspalten zu erstellen, ist dies nicht erforderlich. Jeder Index, den Sie hinzufügen, verlangsamt die DML-Operationen ein wenig, so dass Sie einen Leistungspreis für jede INSERT , UPDATE o DELETE . Wenn der Index nur selten verwendet wird, lohnt er sich möglicherweise nicht.

Answer 2

Diese Abfrage wird fehlende Indizes für Fremdschlüssel auflisten , Originalquelle .

Modifier : Beachten Sie, dass kleine Tabellen (weniger als 9 MB) und einige andere Fälle nicht überprüft werden. Siehe endgültig WHERE Erklärung.

-- 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', 'restrict' ),
        ( '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_attributes AS (
    SELECT fkoid, conrelid, attname, attnum
    FROM fk_list
        JOIN pg_attribute
            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_attributes
    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;

Answer 3

Wenn Sie die Indizes aller Tabellen Ihres Schemas von Ihrem Programm aus auflisten möchten, stehen Ihnen alle Informationen im Katalog zur Verfügung:

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

Wenn Sie weiter in die Tiefe gehen wollen (z. B. Spalten und Reihenfolge), müssen Sie sich pg_catalog.pg_index ansehen. Verwendung von psql -E [dbname] ist sehr nützlich, um herauszufinden, wie man den Katalog abfragt.

Answer 4

Ja - für Primärschlüssel, nein - für Fremdschlüssel (mehr dazu in der docs ).

\d <table_name>

in "psql" zeigt eine Beschreibung einer Tabelle einschließlich aller ihrer Indizes.

Answer 5

Ich finde es toll, wie dies in dem Artikel erklärt wird Tolle Leistungsmerkmale von EclipseLink 2.5

Indizierung von Fremdschlüsseln

Das erste Merkmal ist die automatische Indizierung von Fremdschlüsseln. Die meisten Leute nehmen fälschlicherweise an, dass Datenbanken Fremdschlüssel standardmäßig indizieren. Das tun sie aber nicht. Primärschlüssel werden automatisch indiziert, Fremdschlüssel jedoch nicht. Das bedeutet, dass jede Abfrage, die auf einem Fremdschlüssel basiert, führt eine vollständige Tabellendurchsuchung durch. Dies ist jede OneToMany , ManyToMany o ElementCollection Beziehung, als auch viele Einszu-Eins Beziehungen, und die meisten Abfragen zu einer Beziehung, die Verknüpfungen oder Objektvergleichen . Dies kann ein großes Leistungsproblem sein, und Sie sollten Ihre Fremdschlüsselfelder immer indizieren.