Início / dba / Perguntas / 111523
Accepted
P.Péter
Asked: 2015-08-20 00:20:47 +0800 CST

A pesquisa de trigramas fica muito mais lenta à medida que a string de pesquisa fica mais longa

  • 772

Em um banco de dados Postgres 9.1, tenho uma tabela table1com cerca de 1,5 milhões de linhas e uma coluna label(nomes simplificados por causa desta questão).

Há um índice de trigrama funcional ativado lower(unaccent(label))( unaccent()tornou-se imutável para permitir seu uso no índice).

A consulta a seguir é bastante rápida:

SELECT count(*) FROM table1
WHERE (lower(unaccent(label)) like lower(unaccent('%someword%')));
 count 
-------
     1
(1 row)

Time: 394,295 ms

Mas a consulta a seguir é mais lenta:

SELECT count(*) FROM table1
WHERE (lower(unaccent(label)) like lower(unaccent('%someword and some more%')));
 count 
-------
     1
(1 row)

Time: 1405,749 ms

E adicionar mais palavras é ainda mais lento, mesmo que a busca seja mais rigorosa.

Tentei um truque simples para executar uma subconsulta para a primeira palavra e, em seguida, uma consulta com a string de pesquisa completa, mas (infelizmente) o planejador de consulta percebeu minhas maquinações:

EXPLAIN ANALYZE
SELECT * FROM (
   SELECT id, title, label from table1
   WHERE lower(unaccent(label)) like lower(unaccent('%someword%'))
   ) t1
WHERE lower(unaccent(label)) like lower(unaccent('%someword and some more%'));

Bitmap Heap Scan na tabela1 (custo=16216.01..16220.04 linhas=1 largura=212) (tempo real=1824.017..1824.019 linhas=1 loops=1)
  Recheck Cond: ((lower(unaccent((label)::text)) ~~ '%someword%'::text) AND (lower(unaccent((label)::text)) ~~ '%someword and some more %'::texto))
  -> Varredura de índice de bitmap em table1_label_hun_gin_trgm (custo=0.00..16216.01 linhas=1 largura=0) (tempo real=1823.900..1823.900 linhas=1 loops=1)
        Index Cond: ((lower(unaccent((label)::text)) ~~ '%someword%'::text) AND (lower(unaccent((label)::text)) ~~ '%someword and some more %'::texto))
Tempo de execução total: 1824,064 ms

Meu problema final é que a string de pesquisa vem de uma interface da web que pode enviar strings bastante longas e, portanto, ser bastante lenta e também constituir um vetor DOS.

Então minhas perguntas são:

  • Como agilizar a consulta?
  • Existe uma maneira de dividi-lo em subconsultas para que seja mais rápido?
  • Talvez uma versão posterior do Postgres seja melhor? (Eu tentei 9.4 e não parece mais rápido: ainda o mesmo efeito. Talvez uma versão posterior?)
  • Talvez seja necessária uma estratégia de indexação diferente?
postgresql full-text-search

2 respostas

  1. Best Answer

    No PostgreSQL 9.6 haverá uma nova versão do pg_trgm, 1.2, que será muito melhor quanto a isso. Com um pouco de esforço, você também pode fazer com que esta nova versão funcione no PostgreSQL 9.4 (você precisa aplicar o patch, compilar você mesmo o módulo de extensão e instalá-lo).

    O que a versão mais antiga faz é buscar cada trigrama na consulta e pegar a união deles, e depois aplicar um filtro. O que a nova versão fará é escolher o trigrama mais raro na consulta e procurar apenas por ele e, em seguida, filtrar o restante mais tarde.

    A maquinaria para fazer isso não existe em 9.1. No 9.4 esse maquinário foi adicionado, mas o pg_trgm não foi adaptado para fazer uso dele naquele momento.

    Você ainda teria um possível problema de DOS, pois a pessoa mal-intencionada pode criar uma consulta que tenha apenas trigramas comuns. como '%and%', ou mesmo '%a%'

    Se você não pode atualizar para pg_trgm 1.2, outra maneira de enganar o planejador seria:

    WHERE (lower(unaccent(label)) like lower(unaccent('%someword%'))) 
AND   (lower(unaccent(label||'')) like 
      lower(unaccent('%someword and some more%')));

    Ao concatenar a string vazia ao rótulo, você engana o planejador fazendo-o pensar que não pode usar o índice naquela parte da cláusula where. Portanto, ele usa o índice apenas em %someword% e aplica um filtro apenas a essas linhas.

    Além disso, se você estiver sempre procurando palavras inteiras, poderá usar uma função para tokenizar a string em uma matriz de palavras e usar um índice GIN interno regular (não pg_trgm) nessa função de retorno de matriz.

    • 35

  2. Eu encontrei uma maneira de enganar o planejador de consulta, é um hack bastante simples:

    SELECT *
FROM (
   select id, title, label
   from   table1
   where  lower(unaccent(label)) like lower(unaccent('%someword%'))
   ) t1
WHERE lower(lower(unaccent(label))) like lower(unaccent('%someword and more%'))

    EXPLAINresultado:

    Bitmap Heap Scan na tabela1 (custo=6749.11..7332.71 linhas=1 largura=212) (tempo real=256.607..256.609 linhas=1 loops=1)
  Verifique novamente Cond: (lower(unaccent((label_hun)::text)) ~~ '%someword%'::text)
  Filtro: (lower(lower(unaccent((label)::text))) ~~ '%someword and some more%'::text)
  -> Varredura de índice de bitmap em table1_label_hun_gin_trgm (custo=0.00..6749.11 linhas=147 largura=0) (tempo real=256.499..256.499 linhas=1 loops=1)
        Index Cond: (lower(unaccent((label)::text)) ~~ '%someword%'::text)
Duração total: 256,653 ms

    Então, como não há índice para lower(lower(unaccent(label))), isso criaria uma varredura sequencial, tornando-se um filtro simples. Além disso, um simples AND também fará o mesmo:

    SELECT id, title, label
FROM table1
WHERE lower(unaccent(label)) like lower(unaccent('%someword%'))
AND   lower(lower(unaccent(label))) like lower(unaccent('%someword and more%'))

    Claro, esta é uma heurística que pode não funcionar bem, se a parte recortada usada na digitalização do índice for muito comum. Mas em nosso banco de dados, não há muita repetição, se eu usar cerca de 10 a 15 caracteres.

    Restam duas pequenas questões:

    • Por que o postgres não consegue descobrir que algo assim seria benéfico?
    • O que o postgres faz no intervalo de tempo 0..256.499 (consulte a saída de análise)?
    • 4

relate perguntas