Início / dba / Perguntas / 253084
Accepted
collimarco
Asked: 2019-11-13 09:19:35 +0800 CST

PostgreSQL NOT IN array consulta lenta

  • 772

Eu tenho uma grande tabela com milhões de linhas. Cada linha tem um campo de matriz tags. Eu também tenho o índice GIN adequado no tags.

Contar as linhas que possuem uma tag é rápido (~7s):

SELECT COUNT(*) FROM "subscriptions" WHERE (tags @> ARRAY['t1']::varchar[]);

No entanto, contar as linhas que não possuem uma tag é extremamente lento (~70s):

SELECT COUNT(*) FROM "subscriptions" WHERE NOT (tags @> ARRAY['t1']::varchar[]);

Eu também tentei outras variantes, mas com os mesmos resultados (~70s):

SELECT COUNT(*) FROM "subscriptions" WHERE NOT ('t1' = ANY (tags));

Como posso tornar a operação "não em array" rápida?

postgresql performance

3 respostas

  1. Best Answer

    Eu resolvi graças a Jeff Janes na lista de discussão pgsql-performance:

    O índice GIN não foi usado pelo PostgreSQL para a operação "NOT". A criação de um índice Btree em todo o array resolveu o problema, permitindo uma varredura apenas de índice. Agora, a consulta leva apenas alguns milissegundos em vez de minutos.

    • 4

  2. Se 't1' for uma tag rara, a contagem de linhas que não possuem uma tag resultará na contagem da maioria dos seus "milhões de linhas". E mesmo que 't1' seja muito comum, contar mais do que alguns por cento de linhas do índice não é uma melhoria em relação a uma varredura sequencial. De qualquer forma, isso nunca será muito rápido. Índices não vão ajudar.

    Se você tiver que fazer várias contagens excluindo tags raras - e o número total de linhas não mudar nesse meio tempo (ou a alteração mínima não importa), uma possível otimização seria obter a contagem total de linhas uma vez (lento) e subtraia a (pequena) contagem de linhas com a tag (rápido com índice correspondente) ...

    Dependendo dos requisitos exatos e do seu caso de uso completo, pode haver outros atalhos. Ver:

    Resumindo, os índices normalmente só podem ajudar a identificar uma porcentagem relativamente pequena de linhas da tabela. BTW, IN, = ANY()e os operadores de contenção @>são ferramentas relacionadas, mas com diferenças sutis. Índices GIN normalmente suportam apenas operadores de array apropriados. Ver:

    Você pode obter algo usando arrays inteiros em combinação com operadores e um índice baseado em uma classe de operador fornecida pelo intarraymódulo adicional. Altamente otimizado, mas não pode desafiar os principais.

    Você também pode combinar any mixture of tags that the row must have or must not havecomo você comentou em uma query_intexpressão .

    • 1

  3. Você já teve uma boa resposta, então aqui está um pouco mais para arquivar em alimento para reflexão. Primeiro, sua pergunta me lembrou de uma técnica que soa interessante:

    https://heap.io/blog/engineering/running-10-million-postgresql-indexes-in-production

    Eu estaria interessado em comentários daqueles que tentaram tal estratégia.

    Como outro pensamento, outra opção é manter suas próprias tabelas de frequência para tags e sua ocorrência. Isso pode fornecer informações para orientar seu próprio gerador de código. A ideia aqui é que o planejador/otimizador de consulta genérico nunca pode saber tanto sobre seus dados específicos quanto você. Com contagens de frequência, mesmo contagens aproximadas razoavelmente boas, você pode criar consultas diferentes para enviar ao Postgres para casos diferentes.

    Concretizando essa ideia de contagem de frequência

    Elaborando um pouco aqui como minha resposta abreviada original não estava clara. A noção aqui é que você pode manter uma tabela de contagens de frequência, como tag_countcom tags exclusivas e uma contagem. Esses pequenos dados permitem testar quão comuns são as tags em uma consulta antes de gerar a consulta real para o Postgres. Este plano "simples" depende de várias coisas, algumas das quais podem não ser verdadeiras no seu caso:

    • Você tem o código que está compondo as consultas que podem ser modificadas para fazer essa etapa de pré-processamento para descobrir a melhor forma de compor a consulta.

    • Você pode encontrar maneiras de usar as contagens de frequência para ajudar o planejador a fazer um trabalho melhor.

    • Existe alguma maneira de você executar o código de atualização da contagem de frequência.

    • Existe alguma maneira de manter as contagens com fidelidade adequada e sem atolar o sistema.

    Esse último ponto é um tópico enorme, obviamente. A maneira mais simples (conceitualmente) é um gatilho para adicionar/modificar/excluir que encontra as tags antigas e novas e ajusta as contagens de acordo. Não é a solução mais eficiente e um potencial gargalo. Existem muitos, muitos projetos alternativos. (Um gatilho em nível de instrução com uma tabela de filas pós-reconciliação seria um design alternativo que não é um gargalo.) Honestamente, ainda não conheço as estratégias de melhor desempenho para atualizações incrementais no Postgres. Eu esbocei ~ 10 estratégias para mim alguns meses atrás, mas não voltei para testar e comparar soluções. Outras pessoas neste fórum usam o Postgres há muito tempo e são super inteligentes e prestativas. Então, se esse tipo de solução é o que você procura, vale a pena perguntar novamente.

    • 1

relate perguntas