Início / dba / Perguntas / 329297
Accepted
sujeet
Asked: 2023-07-14 13:42:13 +0800 CST

Ordem ideal para criar um índice composto no PostgreSQL com várias condições

  • 772

Tenho uma tabela com três colunas: user_id, customer_id, and order_id.

Em minhas consultas, frequentemente filtro os dados usando condições como,

  ... WHERE user_id = 23434 AND customer_id = 234234 AND order_id IN [23334, 23423, 23452];

Desejo otimizar o desempenho da consulta criando um índice composto nessas colunas, mas não tenho certeza em que ordem as colunas devem ser incluídas no índice.

Considerando que a seletividade das colunas estaria na ordem customer_id, ordre_ide user_id, qual seria a ordem ótima para criar o índice composto?

Eu poderia fazer (customer_id, order_id, user_id), mas aqui para filtrar registros com base em user_id, banco de dados tem que visitar cada nó folha no intervalo de passado order_id.

Mas, se eu fizer isso (customer_id, user_id, order_id), aqui ele irá verificar muitos índices correspondentes a user_ide fazer a filtragem e, em seguida, buscar as linhas de diskou shared_buffers. Eu não tenho certeza aqui. Se alguém puder fornecer mais informações sobre isso.

Devo priorizar as colunas com base em sua seletividade ou seguir uma abordagem diferente?

Estou mais inclinado a isso (customer_id, order_id, user_id), mas preciso de alguns esclarecimentos.

postgresql

1 respostas

  1. Best Answer

    A seletividade das colunas não importa, desde que você as compare com= . Escrevo isso em negrito, já que é um elemento comum, mas errado, do folclore DBA que a seletividade é importante.

    O que importa, no entanto, é que as colunas com as quais são comparadas =sejam as primeiras na definição do índice.

    Portanto, você só precisa ter certeza de que order_idé a última coluna em seu índice de três colunas.

    Para ampliar a rede, raramente é uma boa ideia considerar apenas uma única consulta ao criar um índice. Você deve considerar os índices existentes e o restante da carga de trabalho. Deixe-me dar alguns exemplos:

    • Se já houver um índice em user_id, você deve descartar esse índice e, em vez disso, criar um em (user_id, customer_id, order_id). Livrar-se de um índice é um benefício que pode sugerir uma determinada ordem de coluna no índice.

    • Se algumas de suas consultas usam WHEREcláusulas como WHERE user_id IN (1, 2, 3) AND customer_id IN (4, 5, 6), você deve colocar a coluna mais seletiva primeiro no índice. Para comparações diferentes de =, isso importa.

    • Se você já tem um índice em (customer_id, order_id), talvez não deva criar um índice adicional. Claro, esse índice não será o ideal, mas se user_idnão for muito seletivo, pode ser bom o suficiente.

    • 3

relate perguntas