Início / dba / Perguntas / 190132
Accepted
ldrg
Asked: 2017-11-05 19:18:50 +0800 CST

A cobertura de índices no PostgreSQL ajuda as colunas JOIN?

  • 772

Eu tenho um monte de tabelas que se parecem vagamente com isso:

CREATE TABLE table1(id INTEGER PRIMARY KEY, t1c1 INTEGER, t1c2 INTEGER);
CREATE TABLE table2(id INTEGER PRIMARY KEY, t1 INTEGER REFERENCES table1(id), t2c1 INTEGER);

E eu faço muitas junções onde estou tentando filtrar na tabela unida para obter coisas da primeira tabela, assim:

SELECT t1c1
FROM table1
JOIN table2 ON table2.t1 = table1.id
WHERE t2c1 = 42;

Quando vou escrever índices para uma tabela, olho para as colunas que são usadas na cláusula WHERE e construo índices para satisfazê-las. Então, para esta consulta, eu acabaria escrevendo um índice como este:

CREATE INDEX ON table2 (t2c1);

E esse índice é pelo menos elegível para uso nessa consulta.

Minha pergunta é que, se eu escrever um índice assim:

CREATE INDEX ON table2 (t2c1, t1);

O índice será usado como um índice de cobertura para ajudar o JOIN na consulta acima? Devo alterar minha estratégia de escrita de índice para cobrir colunas de chave estrangeira?

postgresql index

2 respostas

  1. Best Answer

    O índice será usado como um índice de cobertura para ajudar o JOIN na consulta acima?

    Depende. O Postgres tem varreduras "somente índice" como método de acesso ao índice , não há "índices de cobertura" per se - até o Postgres 10.

    Começando com o Postgres 11 índices de cobertura verdadeiros com INCLUDEcolunas estão disponíveis. Entrada de blog de Michael Paquier apresentando o recurso:

    Resposta relacionada com exemplo de código:

    Dito isso, o índice CREATE INDEX ON table2 (t2c1, t1);faz todo o sentido para a consulta que você demonstra. Ele pode ser usado para uma varredura somente de índice se pré-condições adicionais forem atendidas, ou pode ser usado em uma varredura de índice de bitmap ou em uma varredura de índice simples. Relacionado:

    JOINcondições e WHEREcondições são quase completamente equivalentes no Postgres. Eles certamente podem usar índices da mesma maneira. Você pode reescrever sua consulta:

    SELECT t1.t1c1
FROM   table1 t1
JOIN   table2 t2 ON t2.t1 = t1.id
WHERE  t2.t2c1 = 42;

    Com este equivalente:

    SELECT t1.t1c1
FROM   table1 t1 CROSS JOIN table2 t2
WHERE  t2.t1 = t1.id
AND    t2.t2c1 = 42;

    A primeira forma é obviamente preferível, no entanto. Mais fácil de ler.

    Por que "quase" equivalente? (Não faz diferença para a consulta simples em mãos.)

    Relacionado:

    • 22

  2. O índice será usado como um índice de cobertura para ajudar o JOIN na consulta acima? Devo alterar minha estratégia de escrita de índice para cobrir colunas de chave estrangeira?

    Não é provável na consulta acima. Este é um problema complexo enganador com os resultados baseados nas estimativas e seletividade das duas condições,

    • tabela2.t1 = tabela1.id
    • t2c1 = 42

    Essencialmente, você deseja lançar ambos os ambientes (contagens de linhas) para que ambas as condições tenham mais ou menos seletividade. E se você obtiver um loop aninhado, desejará aumentar a quantidade bruta até que esse não seja mais o método de junção mais viável.

    CREATE TABLE table1(
   id INTEGER PRIMARY KEY,
   t1c1 INTEGER,
   t1c2 INTEGER
);
INSERT INTO table1(id, t1c1, t1c2)
  SELECT x,x,x FROM generate_series(1,1000)
  AS gs(x);

CREATE TABLE table2(
  id INTEGER PRIMARY KEY,
  t1 INTEGER REFERENCES table1(id),
  t2c1 INTEGER
);
INSERT INTO table2(id, t1, t2c1)
SELECT x,1+x%1000,x%50 FROM generate_series(1,1e6)
  AS gs(x);

EXPLAIN ANALYZE
  SELECT t1c1
  FROM table1
  JOIN table2 ON table2.t1 = table1.id
  WHERE t2c1 = 42;

    Agora verifique o plano.

    Agora crie o índice composto,

    CREATE INDEX ON table2 (t2c1, t1);
VACUUM FULL ANALYZE table1;
VACUUM FULL ANALYZE table2;

    E verifique o plano novamente,

    EXPLAIN ANALYZE
  SELECT t1c1
  FROM table1
  JOIN table2 ON table2.t1 = table1.id
  WHERE t2c1 = 42;

    Você pode soltar as chaves e tal para descobrir qual forma ele prefere

    CREATE INDEX ON table2 (t1, t2c1);

    ou

    CREATE INDEX ON table2 (t2c1, t1);

    Em última análise, embora isso seja muito trabalhoso, sugiro começar com

    CREATE INDEX ON table2 (t1);
CREATE INDEX ON table2 (t2c1);

    E otimizar apenas se isso não for suficiente.

    Você também pode desabilitar opções específicas do planejador para ver se outro plano é realmente mais rápido ou mais lento e, em seguida, procurar corrigi-lo, mas isso também pode ser muito trabalhoso.

    • 4

relate perguntas