Início / dba / Perguntas / 114337
Accepted
P.Péter
Asked: 2015-09-08 01:05:22 +0800 CST

Consulta PostgreSQL muito lenta quando a subconsulta é adicionada

  • 772

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.

postgresql performance

3 respostas

  1. O núcleo do problema torna-se óbvio aqui:

    Seq Scan na publicação (custo=0.01..349652.84 linhas=744661 largura=8) (tempo real=2735.888..2841.393 linhas=1 loops=1)

    O Postgres estima retornar 744661 linhas enquanto, na verdade, é uma única linha. Se o Postgres não souber melhor o que esperar da consulta, ele não poderá planejar melhor. Precisaríamos ver a consulta real oculta (SELECT 9762715)- e provavelmente também conhecer a definição da tabela, restrições, cardinalidades e distribuição de dados. Obviamente, o Postgres não é capaz de prever quantas linhas serão retornadas por ele. Pode haver maneiras de reescrever a consulta, dependendo do que for .

    Se você sabe que a subconsulta nunca pode retornar mais do que nlinhas, basta informar ao Postgres usando:

    SELECT mtid
FROM   publication
WHERE  mtid IN (SELECT ... LIMIT n) --  OR last_modifier=21321
LIMIT  5000;

    Se n for pequeno o suficiente, o Postgres mudará para varreduras de índice (bitmap). No entanto , isso só funciona para o caso simples. Pára de funcionar ao adicionar uma ORcondição: o planejador de consulta não pode lidar com isso no momento.

    Eu raramente uso IN (SELECT ...)para começar. Normalmente, há uma maneira melhor de implementar o mesmo, geralmente com uma EXISTSsemi-junção. Às vezes com um ( LEFT) JOIN( LATERAL) ...

    A solução óbvia seria usar UNION, mas você descartou isso. Não posso dizer mais sem conhecer a subconsulta real e outros detalhes relevantes.

    • 8
  2. Best Answer

    Meu colega encontrou uma maneira de alterar a consulta para que ela precise de uma reescrita simples e faça o que precisa, ou seja, fazendo a subseleção em uma etapa e, em seguida, fazendo as operações adicionais no resultado:

    SELECT mtid FROM publication 
WHERE 
  mtid = ANY( (SELECT ARRAY(SELECT 9762715))::bigint[] )
  OR last_modifier=21321
LIMIT 5000;

    A análise explicada agora é:

     Limit  (cost=92.58..9442.38 rows=2478 width=8) (actual time=0.071..0.074 rows=1 loops=1)
   InitPlan 2 (returns $1)
     ->  Result  (cost=0.01..0.02 rows=1 width=0) (actual time=0.010..0.011 rows=1 loops=1)
           InitPlan 1 (returns $0)
             ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.002 rows=1 loops=1)
   ->  Bitmap Heap Scan on publication  (cost=92.56..9442.36 rows=2478 width=8) (actual time=0.069..0.070 rows=1 loops=1)
         Recheck Cond: ((mtid = ANY (($1)::bigint[])) OR (last_modifier = 21321))
         Heap Blocks: exact=1
         ->  BitmapOr  (cost=92.56..92.56 rows=2478 width=0) (actual time=0.060..0.060 rows=0 loops=1)
               ->  Bitmap Index Scan on publication_pkey  (cost=0.00..44.38 rows=10 width=0) (actual time=0.046..0.046 rows=1 loops=1)
                     Index Cond: (mtid = ANY (($1)::bigint[]))
               ->  Bitmap Index Scan on publication_last_modifier_btree  (cost=0.00..46.94 rows=2468 width=0) (actual time=0.011..0.011 rows=0 loops=1)
                     Index Cond: (last_modifier = 21321)
 Planning time: 0.704 ms
 Execution time: 0.153 ms

    Parece que podemos criar um analisador simples que localiza e reescreve todas as subseleções dessa maneira e adicioná-lo a um gancho de hibernação para manipular a consulta nativa.

    • 8

  3. Resposta a uma segunda pergunta: Sim, você pode adicionar ORDER BY à sua subconsulta, o que terá um impacto positivo. Mas é semelhante à solução "EXISTS (subconsulta)" em desempenho. Há uma diferença significativa mesmo com a subconsulta resultando em duas linhas.

    SELECT mtid FROM publication
WHERE mtid IN (SELECT #column# ORDER BY #column#) OR last_modifier=21321
LIMIT 5000;
    • 1

relate perguntas