Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 2 down vote favorite
1

I find this a bit mysterious. The cost of getting the maximum value out of just the partition parent table (which is empty) is very cheap:

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)

Likewise, the cost of getting the maximum value out of just the newest partition is very cheap.

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)

Here's the weird thing. Getting the maximum from the whole partitioned table is very expensive. PostgreSQL doesn't seem to produce efficient subplans, even though there are only two tables here (the master and one partition) and both can be queried separately efficiently.

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)

Is there a configuration tunable I've missed or is this a limitation I don't understand?

Thanks!

share|improve this question
I've been wondering the same thing. – François Beausoleil Feb 29 '12 at 17:01

1 Answer

up vote 2 down vote accepted

What version do you use? Version 9.1 has a major improvement for partioning, check this blog from Greg Smith.

When using version 9.1, I get this result for this query

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
share|improve this answer
Thanks, I hadn't seen this. Yes, I'm on 9.0. This is definitely another good reason to upgrade to 9.1. – Daniel Lyons Feb 29 '12 at 22:12

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.