Início / dba / Perguntas / 14060
Accepted
Daniel Lyons
Asked: 2012-02-29 08:10:50 +0800 CST

Desempenho de consulta de particionamento do PostgreSQL inexplicavelmente ruim

  • 772

Acho isso meio misterioso. O custo de obter o valor máximo apenas da tabela pai da partição (que está vazia) é muito barato:

evlampts=# explain select max(timestamp) from only mcdata;
                                              QUERY PLAN                                               
-------------------------------------------------------------------------------------------------------
 Result  (cost=0.15..0.16 rows=1 width=0)
   InitPlan 1 (returns $0)
     ->  Limit  (cost=0.00..0.15 rows=1 width=8)
           ->  Index Scan Backward using mcdatatime_idx on mcdata  (cost=0.00..19.20 rows=130 width=8)
                 Index Cond: ("timestamp" IS NOT NULL)
(5 rows)

Da mesma forma, o custo de obter o valor máximo apenas da partição mais nova é muito barato.

evlampts=# explain select max(timestamp) from mcdata_2011_09;
                                                                QUERY PLAN                                                                
------------------------------------------------------------------------------------------------------------------------------------------
 Result  (cost=3.17..3.18 rows=1 width=0)
   InitPlan 1 (returns $0)
     ->  Limit  (cost=0.00..3.17 rows=1 width=8)
           ->  Index Scan Backward using mcdata_2011_09_timestamp_idx on mcdata_2011_09  (cost=0.00..560255403.07 rows=176936192 width=8)
                 Index Cond: ("timestamp" IS NOT NULL)
(5 rows)

Aqui está a coisa estranha. Obter o máximo de toda a tabela particionada é muito caro. O PostgreSQL não parece produzir subplanos eficientes, embora haja apenas duas tabelas aqui (a master e uma partição) e ambas possam ser consultadas separadamente de forma eficiente.

evlampts=# explain select max(timestamp) from mcdata;
                                          QUERY PLAN                                           
-----------------------------------------------------------------------------------------------
 Aggregate  (cost=4663394.03..4663394.04 rows=1 width=8)
   ->  Append  (cost=0.00..4221053.22 rows=176936322 width=8)
         ->  Seq Scan on mcdata  (cost=0.00..11.30 rows=130 width=8)
         ->  Seq Scan on mcdata_2011_09 mcdata  (cost=0.00..4221041.92 rows=176936192 width=8)
(4 rows)

Existe uma configuração ajustável que eu perdi ou esta é uma limitação que eu não entendo?

Obrigado!

postgresql performance

1 respostas

  1. Best Answer

    Qual versao voce usa? A versão 9.1 tem uma grande melhoria para particionamento, verifique este blog de Greg Smith.

    Ao usar a versão 9.1, obtenho este resultado para esta consulta

    EXPLAIN ANALYZE
SELECT MAX(logdate) FROM logs;

    :

    Result  (cost=0.88..0.89 rows=1 width=0) (actual time=0.251..0.252 rows=1 loops=1)
  InitPlan 1 (returns $0)
    ->  Limit  (cost=0.78..0.88 rows=1 width=8) (actual time=0.243..0.243 rows=1 loops=1)
          ->  Merge Append  (cost=0.78..14533886.71 rows=147545737 width=8) (actual time=0.238..0.238 rows=1 loops=1)
                Sort Key: public.logs.logdate
                ->  Sort  (cost=0.01..0.02 rows=1 width=8) (actual time=0.013..0.013 rows=0 loops=1)
                      Sort Key: public.logs.logdate
                      Sort Method: quicksort  Memory: 25kB
                      ->  Seq Scan on logs  (cost=0.00..0.00 rows=1 width=8) (actual time=0.003..0.003 rows=0 loops=1)
                            Filter: (logdate IS NOT NULL)
                ->  Index Scan Backward using idx_logs_2011_10_logdate on logs_2011_10 logs  (cost=0.00..46.31 rows=746 width=8) (actual time=0.014..0.014 rows=0 loops=1)
                      Index Cond: (logdate IS NOT NULL)
                ->  Index Scan Backward using idx_logs_2011_11_logdate on logs_2011_11 logs  (cost=0.00..46.31 rows=746 width=8) (actual time=0.006..0.006 rows=0 loops=1)
                      Index Cond: (logdate IS NOT NULL)
                ->  Index Scan Backward using idx_logs_2011_12_logdate on logs_2011_12 logs  (cost=0.00..6785740.95 rows=146709024 width=8) (actual time=0.055..0.055 rows=1 loops=1)
                      Index Cond: (logdate IS NOT NULL)
                ->  Index Scan Backward using idx_logs_2011_4_logdate on logs_2011_4 logs  (cost=0.00..46.31 rows=746 width=8) (actual time=0.005..0.005 rows=0 loops=1)
                      Index Cond: (logdate IS NOT NULL)
                ->  Index Scan Backward using idx_logs_2011_5_logdate on logs_2011_5 logs  (cost=0.00..46.31 rows=746 width=8) (actual time=0.005..0.005 rows=0 loops=1)
                      Index Cond: (logdate IS NOT NULL)
                ->  Index Scan Backward using idx_logs_2011_6_logdate on logs_2011_6 logs  (cost=0.00..46.31 rows=746 width=8) (actual time=0.005..0.005 rows=0 loops=1)
                      Index Cond: (logdate IS NOT NULL)
                ->  Index Scan Backward using idx_logs_2011_7_logdate on logs_2011_7 logs  (cost=0.00..46.31 rows=746 width=8) (actual time=0.004..0.004 rows=0 loops=1)
                      Index Cond: (logdate IS NOT NULL)
                ->  Index Scan Backward using idx_logs_2011_8_logdate on logs_2011_8 logs  (cost=0.00..46.31 rows=746 width=8) (actual time=0.004..0.004 rows=0 loops=1)
                      Index Cond: (logdate IS NOT NULL)
                ->  Index Scan Backward using idx_logs_2011_9_logdate on logs_2011_9 logs  (cost=0.00..46.31 rows=746 width=8) (actual time=0.004..0.004 rows=0 loops=1)
                      Index Cond: (logdate IS NOT NULL)
                ->  Index Scan Backward using idx_logs_2012_1_logdate on logs_2012_1 logs  (cost=0.00..46.31 rows=746 width=8) (actual time=0.004..0.004 rows=0 loops=1)
                      Index Cond: (logdate IS NOT NULL)
                ->  Index Scan Backward using idx_logs_2012_2_logdate on logs_2012_2 logs  (cost=0.00..68278.63 rows=815824 width=8) (actual time=0.025..0.025 rows=1 loops=1)
                      Index Cond: (logdate IS NOT NULL)
                ->  Index Scan Backward using idx_logs_2012_3_logdate on logs_2012_3 logs  (cost=0.00..46.31 rows=746 width=8) (actual time=0.004..0.004 rows=0 loops=1)
                      Index Cond: (logdate IS NOT NULL)
                ->  Index Scan Backward using idx_logs_2012_4_logdate on logs_2012_4 logs  (cost=0.00..46.31 rows=746 width=8) (actual time=0.005..0.005 rows=0 loops=1)
                      Index Cond: (logdate IS NOT NULL)
                ->  Index Scan Backward using idx_logs_2010_1_logdate on logs_2010_1 logs  (cost=0.00..46.31 rows=746 width=8) (actual time=0.006..0.006 rows=0 loops=1)
                      Index Cond: (logdate IS NOT NULL)
                ->  Index Scan Backward using idx_logs_2010_2_logdate on logs_2010_2 logs  (cost=0.00..46.31 rows=746 width=8) (actual time=0.006..0.006 rows=0 loops=1)
                      Index Cond: (logdate IS NOT NULL)
                ->  Index Scan Backward using idx_logs_2010_3_logdate on logs_2010_3 logs  (cost=0.00..46.31 rows=746 width=8) (actual time=0.004..0.004 rows=0 loops=1)
                      Index Cond: (logdate IS NOT NULL)
                ->  Index Scan Backward using idx_logs_2010_4_logdate on logs_2010_4 logs  (cost=0.00..46.31 rows=746 width=8) (actual time=0.004..0.004 rows=0 loops=1)
                      Index Cond: (logdate IS NOT NULL)
                ->  Index Scan Backward using idx_logs_2010_5_logdate on logs_2010_5 logs  (cost=0.00..46.31 rows=746 width=8) (actual time=0.003..0.003 rows=0 loops=1)
                      Index Cond: (logdate IS NOT NULL)
                ->  Index Scan Backward using idx_logs_2010_6_logdate on logs_2010_6 logs  (cost=0.00..46.31 rows=746 width=8) (actual time=0.004..0.004 rows=0 loops=1)
                      Index Cond: (logdate IS NOT NULL)
                ->  Index Scan Backward using idx_logs_2010_7_logdate on logs_2010_7 logs  (cost=0.00..46.31 rows=746 width=8) (actual time=0.004..0.004 rows=0 loops=1)
                      Index Cond: (logdate IS NOT NULL)
                ->  Index Scan Backward using idx_logs_2010_8_logdate on logs_2010_8 logs  (cost=0.00..46.31 rows=746 width=8) (actual time=0.004..0.004 rows=0 loops=1)
                      Index Cond: (logdate IS NOT NULL)
                ->  Index Scan Backward using idx_logs_2010_9_logdate on logs_2010_9 logs  (cost=0.00..46.31 rows=746 width=8) (actual time=0.004..0.004 rows=0 loops=1)
                      Index Cond: (logdate IS NOT NULL)
                ->  Index Scan Backward using idx_logs_2010_10_logdate on logs_2010_10 logs  (cost=0.00..46.31 rows=746 width=8) (actual time=0.004..0.004 rows=0 loops=1)
                      Index Cond: (logdate IS NOT NULL)
                ->  Index Scan Backward using idx_logs_2010_11_logdate on logs_2010_11 logs  (cost=0.00..46.31 rows=746 width=8) (actual time=0.004..0.004 rows=0 loops=1)
                      Index Cond: (logdate IS NOT NULL)
                ->  Index Scan Backward using idx_logs_2010_12_logdate on logs_2010_12 logs  (cost=0.00..46.31 rows=746 width=8) (actual time=0.003..0.003 rows=0 loops=1)
                      Index Cond: (logdate IS NOT NULL)
                ->  Index Scan Backward using idx_logs_2011_1_logdate on logs_2011_1 logs  (cost=0.00..46.31 rows=746 width=8) (actual time=0.003..0.003 rows=0 loops=1)
                      Index Cond: (logdate IS NOT NULL)
                ->  Index Scan Backward using idx_logs_2011_2_logdate on logs_2011_2 logs  (cost=0.00..46.31 rows=746 width=8) (actual time=0.004..0.004 rows=0 loops=1)
                      Index Cond: (logdate IS NOT NULL)
                ->  Index Scan Backward using idx_logs_2011_3_logdate on logs_2011_3 logs  (cost=0.00..46.31 rows=746 width=8) (actual time=0.004..0.004 rows=0 loops=1)
                      Index Cond: (logdate IS NOT NULL)
                ->  Index Scan Backward using idx_logs_2012_5_logdate on logs_2012_5 logs  (cost=0.00..46.31 rows=746 width=8) (actual time=0.004..0.004 rows=0 loops=1)
                      Index Cond: (logdate IS NOT NULL)
                ->  Index Scan Backward using idx_logs_2012_6_logdate on logs_2012_6 logs  (cost=0.00..46.31 rows=746 width=8) (actual time=0.004..0.004 rows=0 loops=1)
                      Index Cond: (logdate IS NOT NULL)
Total runtime: 0.718 ms
    • 2

relate perguntas