Início / dba / Perguntas / 33007
Accepted
dezso
Asked: 2013-01-17 02:09:33 +0800 CST

Desempenho de diferentes soluções de pseudocolunas de precedência com LIMIT

  • 772

Eu estava me perguntando sobre o desempenho relativo das soluções fornecidas nas respostas a uma pergunta no stackoverflow , resolvi fazer alguns testes.

O OP queria obter a primeira linha correspondente, dado um conjunto de condições com precedência decrescente. Ambas as soluções envolviam uma pseudocoluna, mas uma (a minha) envolvia várias SELECTdeclarações UNION ALLunidas, enquanto a outra construía uma CASEexpressão.

Compartilho meus resultados na esperança de que alguém ache isso útil.

postgresql index

2 respostas

  1. Best Answer

    Seu design de teste é falho. Você está testando resultados incorretos .

    Adicionei uma resposta à pergunta no SO a que você está se referindo .

    Na sua versão CASE, você não pode adicionar arquivos ORDER BY col1, col2. Teria que ser ORDER BY precedence. Mas ainda estaria incorreto. Você teria que fazer ORDER BYa soma das pontuações para condições individuais para obter primeiro as linhas que atendem à maioria das condições.

    Da mesma forma, sua versão UNION ALL produz resultados incorretos.

    Porém, nada disso parece necessário, existe uma solução mais simples e rápida com UNION ALL. Consulte minha resposta à pergunta SO ou use o sqlfiddle para brincar

    • 5

  2. Para uma tabela com apenas algumas linhas, não consegui ver nenhuma diferença no tempo de execução (embora os planos de consulta fossem diferentes, os resultados não eram mostrados).

    Em seguida, reutilizei uma tabela de teste de um experimento anterior. Esta tabela consiste em 436421 linhas e possui a seguinte estrutura:

                 Table "public.avg_test"
 Column |            Type             | Modifiers
--------+-----------------------------+-----------
 col1   | timestamp without time zone |
 col2   | integer                     |

    Agora as execuções de teste foram as seguintes (as condições escolhidas retornam menos de 2% de todas as linhas sem LIMIT):

    versão do CASO

    EXPLAIN ANALYZE SELECT 
      col1
    , col2
    , CASE 
          WHEN col1 = '2012-01-01 12:00:00' THEN 1
          WHEN col2 > 98 THEN 2 ELSE 100000 
      END AS precedence 
FROM avg_test
ORDER BY col1, col2
LIMIT 1;

                                                         QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=11090.78..11090.78 rows=1 width=12) (actual time=144.559..144.559 rows=1 loops=1)
   ->  Sort  (cost=11090.78..12182.13 rows=436539 width=12) (actual time=144.557..144.557 rows=1 loops=1)
         Sort Key: col1, col2
         Sort Method: top-N heapsort  Memory: 25kB
         ->  Seq Scan on avg_test  (cost=0.00..8908.09 rows=436539 width=12) (actual time=0.057..88.477 rows=436421 loops=1)
 Total runtime: 144.595 ms
(6 rows)

    versão UNION ALL

    EXPLAIN ANALYZE 
SELECT 
      col1
    , col2
    , 1 AS precedence 
FROM avg_test
WHERE col1 = '2012-01-01 12:00:00'

UNION ALL

SELECT 
      col1
    , col2
    , 2 AS precedence 
FROM avg_test
WHERE col2 > 98

ORDER BY col1, col2
LIMIT 1
;


                                                             QUERY PLAN                                                      
-------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=15766.57..15766.57 rows=1 width=16) (actual time=85.771..85.771 rows=1 loops=1)
   ->  Sort  (cost=15766.57..15788.75 rows=8873 width=16) (actual time=85.769..85.769 rows=1 loops=1)
         Sort Key: public.avg_test.col1, public.avg_test.col2
         Sort Method: top-N heapsort  Memory: 25kB
         ->  Result  (cost=0.00..15722.20 rows=8873 width=16) (actual time=0.056..84.276 rows=8802 loops=1)
               ->  Append  (cost=0.00..15722.20 rows=8873 width=16) (actual time=0.056..83.153 rows=8802 loops=1)
                     ->  Seq Scan on avg_test  (cost=0.00..7816.74 rows=99 width=12) (actual time=0.056..39.860 rows=101 loops=1)
                           Filter: (col1 = '2012-01-01 12:00:00'::timestamp without time zone)
                     ->  Seq Scan on avg_test  (cost=0.00..7816.74 rows=8774 width=12) (actual time=0.046..42.363 rows=8701 loops=1)
                           Filter: (col2 > 98)
 Total runtime: 85.815 ms
(11 rows)

    (Ambos foram executados várias vezes para excluir efeitos de cache.)

    Nesta configuração, o UNION ALLfoi mais rápido porque atingiu muito menos linhas - isso se reflete tanto nas varreduras sequenciais quanto na classificação.

    Agora criei dois índices:

    CREATE INDEX idx_avg_1 ON avg_test (col1, col2);

CREATE INDEX idx_avg_2 ON avg_test (col2);

ANALYZE avg_test;

    As execuções subsequentes deram resultados muito diferentes:

    Versão CASE com índices

                                                                 QUERY PLAN                                                      
------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..0.05 rows=1 width=12) (actual time=0.024..0.025 rows=1 loops=1)
   ->  Index Scan using idx_avg_1 on avg_test  (cost=0.00..21105.89 rows=436421 width=12) (actual time=0.023..0.023 rows=1 loops=1)
 Total runtime: 0.056 ms
(3 rows)

    Versão UNION ALL com índices

                                                                        QUERY PLAN                                               
---------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=3095.29..3095.30 rows=1 width=16) (actual time=12.037..12.037 rows=1 loops=1)
   ->  Sort  (cost=3095.29..3124.87 rows=11832 width=16) (actual time=12.036..12.036 rows=1 loops=1)
         Sort Key: public.avg_test.col1, public.avg_test.col2
         Sort Method: top-N heapsort  Memory: 25kB
         ->  Result  (cost=0.00..3036.13 rows=11832 width=16) (actual time=0.026..10.181 rows=8802 loops=1)
               ->  Append  (cost=0.00..3036.13 rows=11832 width=16) (actual time=0.025..8.374 rows=8802 loops=1)
                     ->  Index Scan using idx_avg_1 on avg_test  (cost=0.00..187.92 rows=99 width=12) (actual time=0.025..0.055 rows=101 loops=1)
                           Index Cond: (col1 = '2012-01-01 12:00:00'::timestamp without time zone)
                     ->  Bitmap Heap Scan on avg_test  (cost=223.23..2729.89 rows=11733 width=12) (actual time=1.997..7.093 rows=8701 loops=1)
                           Recheck Cond: (col2 > 98)
                           ->  Bitmap Index Scan on idx_avg_2  (cost=0.00..220.30 rows=11733 width=0) (actual time=1.433..1.433 rows=8701 loops=1)
                                 Index Cond: (col2 > 98)
 Total runtime: 12.105 ms
(13 rows)

    Esta é uma diferença enorme, e desta vez CASEfoi o vencedor. Um plano de execução não poderia ser mais simples do que isso...

    • 3

relate perguntas