P.Péter's questions

Eu tenho uma consulta relativamente simples em uma tabela com 1,5 milhões de linhas:

SELECT mtid FROM publication
WHERE mtid IN (9762715) OR last_modifier=21321
LIMIT 5000;

EXPLAIN ANALYZEresultado:

Limit  (cost=8.84..12.86 rows=1 width=8) (actual time=0.985..0.986 rows=1 loops=1)
  ->  Bitmap Heap Scan on publication  (cost=8.84..12.86 rows=1 width=8) (actual time=0.984..0.985 rows=1 loops=1)
        Recheck Cond: ((mtid = 9762715) OR (last_modifier = 21321))
        ->  BitmapOr  (cost=8.84..8.84 rows=1 width=0) (actual time=0.971..0.971 rows=0 loops=1)
              ->  Bitmap Index Scan on publication_pkey  (cost=0.00..4.42 rows=1 width=0) (actual time=0.295..0.295 rows=1 loops=1)
                    Index Cond: (mtid = 9762715)
              ->  Bitmap Index Scan on publication_last_modifier_btree  (cost=0.00..4.42 rows=1 width=0) (actual time=0.674..0.674 rows=0 loops=1)
                    Index Cond: (last_modifier = 21321)
Total runtime: 1.027 ms

Até aí tudo bem, rápido e usa os índices disponíveis.
Agora, se eu modificar um pouco uma consulta, o resultado será:

SELECT mtid FROM publication
WHERE mtid IN (SELECT 9762715) OR last_modifier=21321
LIMIT 5000;

A EXPLAIN ANALYZEsaída é:

Limit  (cost=0.01..2347.74 rows=5000 width=8) (actual time=2735.891..2841.398 rows=1 loops=1)
  ->  Seq Scan on publication  (cost=0.01..349652.84 rows=744661 width=8) (actual time=2735.888..2841.393 rows=1 loops=1)
        Filter: ((hashed SubPlan 1) OR (last_modifier = 21321))
        SubPlan 1
          ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=1)
Total runtime: 2841.442 ms

Não tão rápido, e usando o seq scan...

Claro que a consulta original executada pelo aplicativo é um pouco mais complexa, e até mais lenta, e claro que a original gerada pelo hibernate não é (SELECT 9762715), mas a lentidão existe até por isso (SELECT 9762715)! A consulta é gerada pelo hibernate, por isso é um grande desafio alterá-los, e alguns recursos não estão disponíveis (por exemplo UNION, não estão disponíveis, o que seria rápido).

As questões

1. Por que o índice não pode ser usado no segundo caso? Como eles poderiam ser usados?
2. Posso melhorar o desempenho da consulta de outra maneira?

Pensamentos adicionais

Parece que poderíamos usar o primeiro caso fazendo manualmente um SELECT e, em seguida, colocando a lista resultante na consulta. Mesmo com 5.000 números na lista IN(), é quatro vezes mais rápido que a segunda solução. No entanto, parece ERRADO (além disso, poderia ser 100 vezes mais rápido :)). É completamente incompreensível porque o planejador de consulta usa um método completamente diferente para essas duas consultas, então gostaria de encontrar uma solução melhor para esse problema.

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?