sql www Postgres e índices em chaves estrangeiras e chaves primárias




www postgresql org docs (5)

Esta consulta listará os índices ausentes em chaves estrangeiras , fonte original .

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

O Postgres coloca automaticamente índices em Chaves Estrangeiras e Chaves Primárias? Como posso eu saber? Existe um comando que retornará todos os índices em uma tabela?


Se você quiser listar os índices de todas as tabelas em seu (s) esquema (s) do seu programa, todas as informações estão disponíveis no catálogo:

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

Se você quiser aprofundar ainda mais (como colunas e ordenação), é necessário consultar o pg_catalog.pg_index. Usar o psql -E [dbname] é útil para descobrir como consultar o catálogo.


O PostgreSQL cria automaticamente índices em chaves primárias e restrições exclusivas, mas não no lado de referência dos relacionamentos de chave estrangeira.

Quando Pg cria um índice implícito, ele emitirá uma mensagem NOTICE level que você pode ver no psql e / ou nos logs do sistema, para que você possa ver quando isso acontece. Índices criados automaticamente são visíveis na saída de \d para uma tabela também.

A documentação sobre índices exclusivos diz:

O PostgreSQL cria automaticamente um índice para cada restrição exclusiva e restrição de chave primária para impor exclusividade. Portanto, não é necessário criar um índice explicitamente para colunas de chave primária.

e a documentação sobre constraints diz:

Como um DELETE de uma linha da tabela referenciada ou um UPDATE de uma coluna referenciada exigirá uma varredura da tabela de referência para linhas que correspondam ao valor antigo, geralmente é uma boa idéia indexar as colunas de referência. Como isso nem sempre é necessário, e há muitas opções disponíveis sobre como indexar, a declaração de uma restrição de chave estrangeira não cria automaticamente um índice nas colunas de referência.

Portanto, você tem que criar índices em chaves estrangeiras se quiser.

Note que se você usar chaves estrangeiras primárias, como 2 FK's como um PK em uma tabela M-para-N, você terá um índice no PK e provavelmente não precisará criar nenhum índice extra.

Embora geralmente seja uma boa ideia criar um índice (ou incluir) suas colunas de chave estrangeira do lado da referência, isso não é obrigatório. Cada índice adicionado reduz as operações DML um pouco, para que você pague um custo de desempenho em cada INSERT , UPDATE ou DELETE . Se o índice for raramente usado, pode não valer a pena.


Para uma PRIMARY KEY , um índice será criado com a seguinte mensagem:

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

Para uma FOREIGN KEY , a restrição não será criada se não houver um índice na tabela referenciada.

Um índice na tabela de referência não é necessário (embora desejado) e, portanto, não será criado implicitamente.


Sim - para chaves primárias, não - para chaves estrangeiras (mais nos docs ).

\d <table_name>

em "psql" mostra uma descrição de uma tabela incluindo todos os seus índices.





postgresql