Início / dba / Perguntas / 329469
Accepted
sujeet
Asked: 2023-07-20 09:37:08 +0800 CST

Particionamento e indexação do PostgreSQL - cláusula WHERE ideal para remoção de partição

  • 772

Eu tenho uma tabela PostgreSQL chamada orders, que particionei com base na user_idcoluna usando o particionamento LIST usando,

PARITION BY LIST lower(right(user_id, 2))

A tabela possui colunas customer_ide order_id, entre outros. Antes da partição, eu frequentemente consultava a tabela usando o seguinte padrão de consulta:

SELECT *
FROM orders
WHERE customer_id = 234234 AND order_id = 234234;

SELECT *
FROM orders
WHERE order_id = 234234;

Agora, devido a partição que estou usando conforme abaixo:

SELECT *
FROM orders
WHERE user_id = 234234 AND customer_id = 234234 AND order_id = 234234 AND lower(right(user_id, 2)) = '34';

Percebi que a remoção de partição não é totalmente utilizada porque devo incluir lower(right(user_id, 2)) = '34'na cláusula WHERE para acionar a remoção.

No entanto, não tenho uma necessidade específica de filtrar o user_id diretamente nesta consulta.

Minhas perguntas são:

  1. Devo parar de passar user_ida cláusula WHERE, pois já particionei a tabela com base nela?

  2. Seria benéfico criar um índice (user_id, customer_id, order_id)para otimizar o desempenho da consulta?

  3. Como alternativa, devo criar um índice (lower(right(user_id, 2)), customer_id, order_id)e omitir a passagem de user_id na cláusula WHERE para melhor remoção?

  4. Devo também ter um índice (order_id, lower(right(user_id,2)))para a segunda consulta? A seletividade de order_idserá maior que user_id.

Desejo garantir que a remoção de partição seja utilizada de maneira ideal, mantendo um bom desempenho de consulta. Qualquer conselho ou práticas recomendadas sobre indexação e particionamento nesse cenário serão muito apreciados.

postgresql

1 respostas

  1. Best Answer

    O particionamento é uma ferramenta de gerenciamento de dados (como quando você deseja DROPuma partição inteira de dados de uma só vez). Não é uma ferramenta destinada a melhorar o desempenho da pesquisa, como por SELECTtipo de consulta, e há casos em que realmente prejudica um pouco o desempenho.

    Os índices destinam-se a melhorar o desempenho das pesquisas e são exponencialmente mais eficientes do que o particionamento, porque o particionamento divide os dados linearmente e os índices fazem isso de forma logarítmica.

    1. Devo parar de passar user_ida cláusula WHERE já que já particionei a tabela com base nela

    Sim, porque parece que você não precisa de particionamento e user_idnão parece ser necessário para seus casos de uso, supondo que an order_idseja único, porque não reduz mais os dados.

    1. Seria benéfico criar um índice (user_id, customer_id, order_id)para otimizar o desempenho da consulta?

    Não, já que user_idnão filtraria mais os dados de qualquer maneira (novamente com base na cardinalidade do order_idcampo em relação a ele), seria redundante adicionar user_idàs suas consultas e índices. Em vez disso, você deve criar um índice em (order_id)ou no (order_id, customer_id)qual cobrirá suas consultas de exemplo. (É importante que você lidere order_idprimeiro, para cobrir ambas as consultas.)

    1. Como alternativa, devo criar um índice (lower(right(user_id, 2)), customer_id, order_id)e omitir a passagem de user_id na cláusula WHERE para melhor remoção?

    Não. Nem mesmo tenho certeza do que olhar apenas os 2 dígitos certos do user_idse destina a fazer.

    1. Devo também ter um índice (order_id, lower(right(user_id,2)))para a segunda consulta? A seletividade de order_idserá maior que user_id.

    Não. Atenha-se ao índice simples acima mencionado de (order_id, customer_id). Isso cobre suas consultas e não pode ser realmente muito mais eficiente, a menos que haja outro campo para reduzir ainda mais os dados retornados. Além disso, a seletividade não importa para pesquisas de igualdade, que é o que ambas as consultas de exemplo estão fazendo.

    • 1

relate perguntas