Início / dba / Perguntas / 4521
Accepted
David Eyk
Asked: 2011-08-10 15:04:10 +0800 CST

Como criar um índice para acelerar uma consulta LIKE agregada em uma expressão?

  • 772

Posso estar fazendo a pergunta errada no título. Aqui estão os fatos:

Meu pessoal de atendimento ao cliente tem reclamado dos tempos de resposta lentos ao fazer pesquisas de clientes na interface de administração do nosso site baseado em Django.

Estamos usando o Postgres 8.4.6. Comecei a registrar consultas lentas e descobri este culpado:

SELECT COUNT(*) FROM "auth_user" WHERE UPPER("auth_user"."email"::text) LIKE UPPER(E'%deyk%')

Esta consulta está demorando mais de 32 segundos para ser executada. Aqui está o plano de consulta fornecido pelo EXPLAIN:

QUERY PLAN
Aggregate  (cost=205171.71..205171.72 rows=1 width=0)
  ->  Seq Scan on auth_user  (cost=0.00..205166.46 rows=2096 width=0)
        Filter: (upper((email)::text) ~~ '%DEYK%'::text)

Como esta é uma consulta gerada pelo Django ORM a partir de um Django QuerySet gerado pelo aplicativo Django Admin, não tenho nenhum controle sobre a consulta em si. Um índice parece ser a solução lógica. Eu tentei criar um índice para acelerar isso, mas não fez diferença:

CREATE INDEX auth_user_email_upper ON auth_user USING btree (upper(email::text))

O que estou fazendo errado? Como posso acelerar esta consulta?

postgresql performance

2 respostas

  1. Best Answer

    Não há suporte de índice para LIKE/ ILIKEno PostgreSQL 8.4 - exceto para termos de pesquisa ancorados à esquerda .

    Desde o PostgreSQL 9.1 o módulo adicional pg_trgmfornece classes de operadores para índices de trigramas GIN e GiST que suportam LIKE/ ILIKEou expressões regulares (operadores ~e amigos). Instale uma vez por banco de dados:

    CREATE EXTENSION IF NOT EXISTS pg_trgm;

    Exemplo de índice GIN:

    CREATE INDEX tbl_col_gin_trgm_idx ON tbl USING gin (col gin_trgm_ops);

    Relacionado:

    • 34

  2. Esse índice não vai ajudar por causa do '%' no início de sua correspondência - um índice BTREE só pode corresponder a prefixos e o curinga no início de sua consulta significa que não há prefixo fixo para procurar.

    É por isso que está fazendo uma varredura de tabela e correspondendo a cada registro por sua vez com a string de consulta.

    Você provavelmente precisa usar um índice de texto completo e os operadores de correspondência de texto em vez de fazer a pesquisa de substring com LIKE que você está no momento. Você pode encontrar mais informações sobre pesquisa de texto completo na documentação:

    http://www.postgresql.org/docs/8.4/static/textsearch-intro.html

    Na verdade, noto nessa página que LIKE aparentemente nunca usa índices, o que me parece estranho, pois deveria ser capaz de resolver prefixos não curinga usando um índice BTREE. No entanto, alguns testes rápidos sugerem que a documentação provavelmente está correta; nesse caso, nenhuma quantidade de indexação ajudará enquanto você estiver usando LIKE para resolver a consulta.

    • 13

relate perguntas