Início / dba / Perguntas / 16612
Accepted
dezso
Asked: 2012-04-18 03:34:27 +0800 CST

Índice na chave primária não usado na junção simples

  • 772

Eu tenho as seguintes definições de tabela e índice:

CREATE TABLE munkalap (
    munkalap_id serial PRIMARY KEY,
    ...
);

CREATE TABLE munkalap_lepes (
    munkalap_lepes_id serial PRIMARY KEY,
    munkalap_id integer REFERENCES munkalap (munkalap_id),
    ...
);

CREATE INDEX idx_munkalap_lepes_munkalap_id ON munkalap_lepes (munkalap_id);

Por que nenhum dos índices em munkalap_id é usado na consulta a seguir?

EXPLAIN ANALYZE SELECT ml.* FROM munkalap m JOIN munkalap_lepes ml USING (munkalap_id);

QUERY PLAN
Hash Join  (cost=119.17..2050.88 rows=38046 width=214) (actual time=0.824..18.011 rows=38046 loops=1)
  Hash Cond: (ml.munkalap_id = m.munkalap_id)
  ->  Seq Scan on munkalap_lepes ml  (cost=0.00..1313.46 rows=38046 width=214) (actual time=0.005..4.574 rows=38046 loops=1)
  ->  Hash  (cost=78.52..78.52 rows=3252 width=4) (actual time=0.810..0.810 rows=3253 loops=1)
        Buckets: 1024  Batches: 1  Memory Usage: 115kB
        ->  Seq Scan on munkalap m  (cost=0.00..78.52 rows=3252 width=4) (actual time=0.003..0.398 rows=3253 loops=1)
Total runtime: 19.786 ms

É o mesmo, mesmo se eu adicionar um filtro:

EXPLAIN ANALYZE SELECT ml.* FROM munkalap m JOIN munkalap_lepes ml USING (munkalap_id) WHERE NOT lezarva;

QUERY PLAN
Hash Join  (cost=79.60..1545.79 rows=1006 width=214) (actual time=0.616..10.824 rows=964 loops=1)
  Hash Cond: (ml.munkalap_id = m.munkalap_id)
  ->  Seq Scan on munkalap_lepes ml  (cost=0.00..1313.46 rows=38046 width=214) (actual time=0.007..5.061 rows=38046 loops=1)
  ->  Hash  (cost=78.52..78.52 rows=86 width=4) (actual time=0.587..0.587 rows=87 loops=1)
        Buckets: 1024  Batches: 1  Memory Usage: 4kB
        ->  Seq Scan on munkalap m  (cost=0.00..78.52 rows=86 width=4) (actual time=0.014..0.560 rows=87 loops=1)
              Filter: (NOT lezarva)
Total runtime: 10.911 ms
postgresql performance

2 respostas

  1. Best Answer

    Muitas pessoas já ouviram orientações de que "varreduras sequenciais são ruins" e buscam eliminá-las de seus planos, mas não é tão simples assim. Se uma consulta cobrir todas as linhas de uma tabela, uma verificação sequencial é a maneira mais rápida de obter essas linhas. É por isso que sua consulta de junção original usou a varredura seq, porque todas as linhas em ambas as tabelas eram necessárias.

    Ao planejar uma consulta, o planejador do Postgres estima os custos de várias operações (computação, IO sequencial e aleatória) em diferentes esquemas possíveis e escolhe o plano que estima ter o custo mais baixo. Ao fazer IO de armazenamento rotativo (discos), IO aleatório geralmente é substancialmente mais lento que IO sequencial, a configuração pg padrão para random_page_cost e seq_page_cost estima uma diferença de custo de 4:1.

    Essas considerações entram em jogo ao considerar um método de junção ou filtro que usa um índice versus um que varre sequencialmente uma tabela. Ao usar um índice, o plano pode encontrar uma linha rapidamente por meio do índice e, em seguida, ter que considerar uma leitura de bloco aleatória para resolver os dados da linha. No caso de sua segunda consulta que adicionou um predicado de filtragem WHERE NOT lezarva, você pode ver como isso afetou as estimativas de planejamento nos resultados EXPLAIN ANALYZE. O planejador estima 1.006 linhas resultantes da junção (o que corresponde bastante ao conjunto de resultados real de 964). Dado que a tabela maior munkalap_lepes contém cerca de 38 mil linhas, o planejador vê que a junção terá que acessar cerca de 1006/38046 ou 1/38 das linhas da tabela. Ele também sabe que a largura média da linha é de 214 bytes e um bloco é de 8K, portanto, há cerca de 38 linhas/bloco.

    Com essas estatísticas, o planejador considera provável que a junção tenha que ler todos ou a maioria dos blocos de dados da tabela. Como as pesquisas de índice também não são gratuitas e a computação para varrer um bloco avaliando uma condição de filtro é muito barata em relação a IO, o planejador optou por varrer sequencialmente a tabela e evitar sobrecarga de índice e leituras aleatórias enquanto calcula a varredura seq. será mais rápido.

    No mundo real, os dados geralmente estão disponíveis na memória por meio do cache de página do sistema operacional e, portanto, nem toda leitura de bloco requer IO. Pode ser muito difícil prever a eficácia de um cache para uma determinada consulta, mas o planejador Pg usa algumas heurísticas simples. O valor de configuração Effective_cache_size informa as estimativas dos planejadores sobre a probabilidade de incorrer em custos reais de E/S. Um valor maior fará com que ele estime um custo menor para E/S aleatória e, portanto, pode incliná-lo para um método orientado por índice em uma varredura sequencial.

    • 30

  2. Você está recuperando todas as linhas de ambas as tabelas, portanto, não há nenhum benefício real usando uma varredura de índice. Uma varredura de índice só faz sentido se você estiver selecionando apenas algumas linhas de uma tabela (normalmente menos de 10%-15%)

    • 8

relate perguntas