Início / dba / Perguntas / 249022
Accepted
Morris de Oryx
Asked: 2019-09-18 18:15:50 +0800 CST

Índice de expressão em uma coluna citext ignorado, por quê?

  • 772

Executando em RDS com cerca de 32 milhões de linhas.

PostgreSQL 11.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-11), 64-bit

Também testando localmente no macOS com cerca de 8 milhões de linhas.

PostgreSQL 11.5 on x86_64-apple-darwin16.7.0, compiled by Apple LLVM version 8.1.0 (clang-802.0.42), 64-bit

Eu tenho uma coluna chamada old_valueque é do tipo citext. Eu já perguntei sobre isso, mas postei muitos dos meus passos de descoberta ao longo do caminho . Aqui está uma versão resumida que eu espero que chegue ao ponto.

Fundo

Eu tenho uma tabela de log de alterações de campo chamada record_changes_log_detail com 32 milhões de linhas e em crescimento que inclui um campo citext chamado old_value.

Os dados são muito distorcidos. A maioria dos valores são menos de uma dúzia de caracteres, alguns são mais de 5.000.

O Postgres engasga com valores grandes com um erro sobre as entradas da árvore B serem limitadas a 2172 caracteres. Portanto, acredito que para uma árvore B, preciso substringir o valor de origem.

O interesse principal dos meus usuários é uma pesquisa =, uma pesquisa que começa com e, às vezes, uma pesquisa contém esta substring. Então = string% e %string%

Metas

Crie um índice que suporte essas pesquisas que o planejador usa.

Tentou e falhou

Uma árvore B reta falha ao construir, em alguns casos, devido a valores longos.

Uma expressão B-tree como esta é construída, mas não é usada

CREATE INDEX record_changes_log_detail_old_value_ix_btree
    ON  record_changes_log_detail 
    USING btree (substring(old_value,1,1024));

Adicionar text_pattern_opts não ajuda.

CREATE INDEX record_changes_log_detail_old_value_ix_btree
    ON  record_changes_log_detail 
    USING btree (substring(old_value,1,1024) text_pattern_opts);

Tentei e funciona parcialmente

Um índice de hash funciona, mas apenas para igualdade. (Como diz na lata.)

Este é o mais próximo que cheguei do sucesso:

CREATE INDEX record_changes_log_detail_old_value_ix_btree
    ON record_changes_log_detail 
    USING btree (old_value citext_pattern_ops);

Isso funciona para qualidade, mas não para LIKE. As notas de lançamento do PG 11 dizem que deve funcionar para LIKE:

https://www.postgresql.org/docs/11/release-11.html

Por "trabalho" quero dizer "o índice é usado".

Não consegui substring com sucesso com essa abordagem.

O que as pessoas fazem nesta situação com campos de citexto?

postgresql index

3 respostas

  1. Edite sua pergunta, em vez de postar respostas que não a respondam.

    Se você criar um índice na expressão substring(old_value,1,1024), esse índice só poderá ser usado se a consulta envolver substring(old_value,1,1024).

    Embora seja teoricamente possível provar que isso old_value='foo'implica que substring(old_value,1,1024)='foo'(e, portanto, a contrapositiva a isso) se você tiver conhecimento suficiente sobre as partes internas da substring, o PostgreSQL não tenta provar isso . Você precisa escrever a consulta de forma que nenhuma prova seja necessária.

    • 3
  2. Best Answer

    É incomum indexar uma coluna tão longa inteiramente.

    Três ideias:

    1. Modifique a consulta assim:

      WHERE substring(old_value, 1, 100) LIKE substring(pattern, 1, 100)
  AND old_value LIKE pattern

      ( patternaqui seria a string padrão, algo como 'string%'.)

      Então um índice b-tree substring(old_value, 1, 100)pode ser usado (se o padrão não começar com um caractere curinga, é claro).

    2. Dependendo dos requisitos exatos (você está pesquisando palavras completas ou prefixos de palavras em um texto em linguagem natural ou não), a pesquisa de texto completo pode ser uma boa solução.

    3. Outra opção são os índices de trigramas :

      CREATE INDEX ON record_changes_log_detail USING gin (old_value gin_trgm_ops);

      Isso requer que a pg_trgmextensão seja instalada.

      Esse índice também funcionará para padrões de pesquisa que começam com um curinga. Para um bom desempenho, imponha um comprimento mínimo na string de pesquisa.

    • 2

  3. Estou de volta para encerrar esta questão. Seguindo uma sugestão de Laurenz Albe, experimentei a implementação de trigrama do Postgres. Eles governam!

    DROP INDEX IF EXISTS record_changes_log_detail_old_value_ix_tgrm;
CREATE INDEX record_changes_log_detail_old_value_ix_tgrm
    ON record_changes_log_detail 
    USING gin (old_value gin_trgm_ops);

    O segredo aqui quando você está usando citext é converter seu valor para ::text, assim:

    select * from record_changes_log_detail 
where old_value::text LIKE '%Gold Kerrison Neuro%';

    Executando isso com a análise de explicação confirma que o índice é usado. Percebi que tenho que usar LIKE para uma pesquisa =, mas tudo bem.

    • 0

relate perguntas