Início / dba / Perguntas / 60480
Accepted
Jérôme Verstrynge
Asked: 2014-03-09 07:59:07 +0800 CST

Devo criar um índice para colunas não-chave?

  • 772

Eu tenho uma tabela em um banco de dados PostGreSql definido da seguinte forma:

CREATE TABLE public."MATCH"(
    "ITEM_A_ID" bigint DEFAULT 0,
    "ITEM_B_ID" bigint DEFAULT 0,
    "OWNER_A_ID" bigint DEFAULT 0,
    "OWNER_B_ID" bigint DEFAULT 0,
    "OTHER_DATA" varchar(100) NOT NULL DEFAULT ''
    CONSTRAINT "MATCH_PK" PRIMARY KEY ("ITEM_A_ID","ITEM_B_ID")
);

Ele conterá muitas linhas. Haverá muitas consultas como as seguintes realizadas nesta tabela:

SELECT * FROM "MATCH" WHERE "OWNER_A_ID" = owner_a_id;
SELECT * FROM "MATCH" WHERE "OWNER_B_ID" = owner_b_id;

Eu estava pensando em criar índices em OWNER_A_IDe OWNER_B_ID, já que essas colunas não são chaves. Esta é uma boa ideia e, se sim, como devo criá-los? Devo criar um índice com ambas as colunas? Devo criar dois índices? Devo incluir outras colunas?

postgresql index

2 respostas

  1. Seja mais específico: "Ele conterá muitas linhas". Quantos? Milhões, milhares ou bilhões. "É uma boa ideia?" Depende.

    Se suas consultas forem como as mencionadas, você deve criar dois índices de árvore b, um para cada campo. As instruções estão aqui: http://www.postgresql.org/docs/9.1/static/sql-createindex.html

    Você deve criar apenas um índice para ambos os campos, SOMENTE SE todas as suas consultas forem como:

    SELECT * FROM "MATCH" WHERE "OWNER_A_ID" = owner_a_id AND "OWNER_B_ID"=owner_b_id;

    Este índice também funcionará para consultas como:

    SELECT * FROM "MATCH" WHERE "OWNER_A_ID" = owner_a_id;

    mas NÃO para

    SELECT * FROM "MATCH" WHERE "OWNER_B_ID" = owner_b_id;
    • 6
  2. Best Answer

    A seleção de índices suficientes costuma ser difícil. No seu caso, deve ser útil criar dois índices.

    Você só deve criar um índice com as duas colunas se sua consulta sempre incluir a primeira coluna como condição:

    SELECT * FROM "MATCH" WHERE "OWNER_A_ID" = owner_a_id [AND "OWNER_B_ID" = owner_b_id]

    Toda a B-Tree é construída sobre a ordem das colunas no índice! Você não pode usar totalmente um índice de várias colunas em a, b nas seguintes consultas:

    SELECT * FROM "MATCH" WHERE "OWNER_A_ID" = owner_a_id OR "OWNER_B_ID" = owner_b_id
SELECT * FROM "MATCH" WHERE "OWNER_A_ID" = "OWNER_B_ID"

    Se você estiver usando apenas verificações de igualdade, considere um índice de hash. Mas o postgresql tem algumas desvantagens que você deve verificar primeiro.

    Em outros dbms, você deve considerar adicionar colunas adicionais no índice como dados. Isso seria útil se você consultar essas colunas específicas e não * porque o dbms não precisaria alimentar os dados da tabela depois de usar o índice.

    Um fator importante: os índices se fragmentam ao longo do tempo (a menos que você não esteja realizando nenhuma inserção/atualização/exclusão na tabela). Verifique se o seu dba possui algumas operações de otimização instaladas.

    Verifique a documentação para opções adicionais como FILLFACTOR ou índices parciais: http://www.postgresql.org/docs/9.3/static/sql-createindex.html

    • 5

relate perguntas