wake-up-neo.com

Postgres und Indizes für Fremdschlüssel und Primärschlüssel

Setzt Postgres automatisch Indizes für Fremdschlüssel und Primärschlüssel? Wie kann ich sagen? Gibt es einen Befehl, der alle Indizes für eine Tabelle zurückgibt?

290
mainstringargs

PostgreSQL erstellt automatisch Indizes für Primärschlüssel und eindeutige Einschränkungen, jedoch nicht für die referenzierende Seite von Fremdschlüsselbeziehungen.

Wenn Pg einen impliziten Index erstellt, wird eine Meldung auf NOTICE-Ebene ausgegeben, die Sie in psql und/oder in den Systemprotokollen sehen können, sodass Sie sehen können, wann dies geschieht. Automatisch erstellte Indizes sind auch in der Ausgabe \d Für eine Tabelle sichtbar.

Das Dokumentation zu eindeutigen Indizes sagt:

PostgreSQL erstellt automatisch einen Index für jede eindeutige Einschränkung und jede Primärschlüsseleinschränkung, um die Eindeutigkeit zu erzwingen. Daher ist es nicht erforderlich, explizit einen Index für Primärschlüsselspalten zu erstellen.

und die Dokumentation zu Einschränkungen sagt:

Da ein DELETE einer Zeile aus der referenzierten Tabelle oder ein UPDATE einer referenzierten Spalte ein Durchsuchen der referenzierten Tabelle nach Zeilen erfordert, die mit dem alten Wert übereinstimmen, empfiehlt es sich häufig, die referenzierenden Spalten zu indizieren. Da dies nicht immer erforderlich ist und viele Möglichkeiten zur Indizierung zur Verfügung stehen, wird durch die Deklaration einer Fremdschlüsseleinschränkung nicht automatisch ein Index für die referenzierenden Spalten erstellt.

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

Beachten Sie, dass Sie bei Verwendung von Primärfremdschlüsseln wie 2 FKs als PK in einer M-zu-N-Tabelle einen Index für die PK haben und wahrscheinlich keine zusätzlichen Indizes erstellen müssen.

In der Regel empfiehlt es sich, einen Index für die referenzierungsseitigen Fremdschlüsselspalten zu erstellen (oder diese einzuschließen). Dies ist jedoch nicht erforderlich. Jeder von Ihnen hinzugefügte Index verlangsamt die DML-Operationen geringfügig, sodass Sie für jeden INSERT, UPDATE oder DELETE Leistungskosten zahlen. Wenn der Index selten verwendet wird, ist er möglicherweise nicht wert.

341
Philipp

Wenn Sie die Indizes aller Tabellen in Ihren Schemata aus Ihrem Programm auflisten möchten, sind alle Informationen im Katalog verfügbar:

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 sich eingehender mit Themen wie Spalten und Reihenfolge befassen möchten, müssen Sie sich pg_catalog.pg_index ansehen. Mit psql -E [dbname] ist praktisch, um herauszufinden, wie der Katalog abgefragt werden kann.

32
dland

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

\d <table_name>

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

20
Milen A. Radev

Diese Abfrage listet fehlende Indizes für Fremdschlüssel , Originalquelle auf.

-- 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;
20
SergeyB

Ich liebe es, wie dies im Artikel erklärt wird Coole Leistungsmerkmale von EclipseLink 2.5

Indizieren von Fremdschlüsseln

Die erste Funktion ist die automatische Indizierung von Fremdschlüsseln. Die meisten Leute gehen fälschlicherweise davon aus, dass Datenbanken standardmäßig Fremdschlüssel indizieren. Nun, das tun sie nicht. Primärschlüssel werden automatisch indiziert, Fremdschlüssel jedoch nicht. Dies bedeutet, dass bei allen Abfragen, die auf dem Fremdschlüssel basieren, vollständige Tabellensuchen durchgeführt werden. Dies ist eine beliebige OneToMany , ManyToMany oder ElementCollection Beziehung sowie viele OneToOne Beziehungen und die meisten Abfragen zu Beziehungen, die Verknüpfungen oder Objektvergleiche beinhalten . Dies kann ein schwerwiegendes Problem sein, und Sie sollten Ihre Fremdschlüsselfelder immer indizieren.

10
Nabi

Für ein PRIMARY KEY wird ein Index mit der folgenden Meldung erstellt:

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

Für ein FOREIGN KEY, die Einschränkung wird nicht erstellt, wenn für die referenc ed -Tabelle kein Index vorhanden ist.

Ein Index für referenc ing table ist nicht erforderlich (obwohl gewünscht) und wird daher nicht implizit erstellt.

7
Quassnoi