LIMIT on partitioned-table!? - Mailing list pgsql-performance

Hello list,

does `postgres (PostgreSQL) 8.4.5' use the LIMIT of a query when it is run on a partitioned-table or am I doing
somethingwrong? It looks as if postgres queries all partitions and then LIMITing the records afterwards!? This results
ina long (>3 minutes) running query. What can I do to optimise this? 

The query could look like this:

     EXPLAIN ANALYSE
     SELECT *
     FROM flexserver.unitstat
     WHERE nodeid = 'abcd'
     AND ts > '2010-01-01 00:00:00'
     AND ts < '2011-02-15 15:00:00'
     ORDER BY nodeid, ts
     LIMIT 1000;

This is the `EXPLAIN ANALYSE'-output:

     Limit  (cost=232195.49..232197.99 rows=1000 width=194) (actual time=205846.722..205852.218 rows=1000 loops=1)
       ->  Sort  (cost=232195.49..232498.26 rows=121108 width=194) (actual time=205846.717..205848.684 rows=1000
loops=1)
             Sort Key: flexserver.unitstat.ts
             Sort Method:  top-N heapsort  Memory: 314kB
             ->  Result  (cost=0.00..225555.27 rows=121108 width=194) (actual time=444.969..205136.182 rows=203492
loops=1)
                   ->  Append  (cost=0.00..225555.27 rows=121108 width=194) (actual time=444.963..204236.800
rows=203492loops=1) 
                         ->  Seq Scan on unitstat  (cost=0.00..14.90 rows=1 width=258) (actual time=0.007..0.007 rows=0
loops=1)
                               Filter: ((ts > '2010-01-01 00:00:00'::timestamp without time zone) AND (ts < '2011-02-15
15:00:00'::timestampwithout time zone) AND ((nodeid)::text = 'abcd'::text)) 
                         ->  Bitmap Heap Scan on unitstat_y2011m01 unitstat  (cost=116.47..8097.17 rows=4189 width=194)
(actualtime=444.949..9900.002 rows=5377 loops=1) 
                               Recheck Cond: ((nodeid)::text = 'abcd'::text)
                               Filter: ((ts > '2010-01-01 00:00:00'::timestamp without time zone) AND (ts < '2011-02-15
15:00:00'::timestampwithout time zone)) 
                               ->  Bitmap Index Scan on idx_unitstat_y2011m01_nodeid_gps_ts  (cost=0.00..115.42
rows=4190width=0) (actual time=426.599..426.599 rows=5377 loops=1) 
                                     Index Cond: ((nodeid)::text = 'abcd'::text)
                         ->  Bitmap Heap Scan on unitstat_y2011m02 unitstat  (cost=52.67..3689.16 rows=1906 width=194)
(actualtime=73.512..3211.698 rows=796 loops=1) 
                               Recheck Cond: ((nodeid)::text = 'abcd'::text)
                               Filter: ((ts > '2010-01-01 00:00:00'::timestamp without time zone) AND (ts < '2011-02-15
15:00:00'::timestampwithout time zone)) 
                               ->  Bitmap Index Scan on idx_unitstat_y2011m02_nodeid_gps_ts  (cost=0.00..52.20
rows=1906width=0) (actual time=55.458..55.458 rows=796 loops=1) 
                                     Index Cond: ((nodeid)::text = 'abcd'::text)
                         ->  Index Scan using fki_unitstat_y2010m02_nodeid_ts_fkey on unitstat_y2010m02 unitstat
(cost=0.00..10179.11rows=5257 width=193) (actual time=39.531..11660.741 rows=6524 loops=1) 
                               Index Cond: (((nodeid)::text = 'abcd'::text) AND (ts > '2010-01-01 00:00:00'::timestamp
withouttime zone) AND (ts < '2011-02-15 15:00:00'::timestamp without time zone)) 
                         ->  Index Scan using fki_unitstat_y2010m01_nodeid_ts_fkey on unitstat_y2010m01 unitstat
(cost=0.00..10324.31rows=5358 width=193) (actual time=38.255..9808.237 rows=7128 loops=1) 
                               Index Cond: (((nodeid)::text = 'abcd'::text) AND (ts > '2010-01-01 00:00:00'::timestamp
withouttime zone) AND (ts < '2011-02-15 15:00:00'::timestamp without time zone)) 
                         ->  Bitmap Heap Scan on unitstat_y2010m11 unitstat  (cost=586.92..39314.99 rows=21965
width=195)(actual time=1417.528..26090.404 rows=24464 loops=1) 
                               Recheck Cond: ((nodeid)::text = 'abcd'::text)
                               Filter: ((ts > '2010-01-01 00:00:00'::timestamp without time zone) AND (ts < '2011-02-15
15:00:00'::timestampwithout time zone)) 
                               ->  Bitmap Index Scan on idx_unitstat_y2010m11_nodeid_gps_ts  (cost=0.00..581.43
rows=21970width=0) (actual time=1400.898..1400.898 rows=24464 loops=1) 
                                     Index Cond: ((nodeid)::text = 'abcd'::text)
                         ->  Bitmap Heap Scan on unitstat_y2010m12 unitstat  (cost=128.72..9050.29 rows=4683 width=194)
(actualtime=238.679..7472.936 rows=2014 loops=1) 
                               Recheck Cond: ((nodeid)::text = 'abcd'::text)
                               Filter: ((ts > '2010-01-01 00:00:00'::timestamp without time zone) AND (ts < '2011-02-15
15:00:00'::timestampwithout time zone)) 
                               ->  Bitmap Index Scan on idx_unitstat_y2010m12_nodeid_gps_ts  (cost=0.00..127.55
rows=4684width=0) (actual time=225.009..225.009 rows=2014 loops=1) 
                                     Index Cond: ((nodeid)::text = 'abcd'::text)
                         ->  Bitmap Heap Scan on unitstat_y2010m10 unitstat  (cost=101.74..9686.81 rows=4987 width=194)
(actualtime=488.130..35826.742 rows=25279 loops=1) 
                               Recheck Cond: ((nodeid)::text = 'abcd'::text)
                               Filter: ((ts > '2010-01-01 00:00:00'::timestamp without time zone) AND (ts < '2011-02-15
15:00:00'::timestampwithout time zone)) 
                               ->  Bitmap Index Scan on idx_unitstat_y2010m10_nodeid_gps_ts  (cost=0.00..100.49
rows=4988width=0) (actual time=472.796..472.796 rows=25279 loops=1) 
                                     Index Cond: ((nodeid)::text = 'abcd'::text)
                         ->  Bitmap Heap Scan on unitstat_y2010m09 unitstat  (cost=489.56..49567.74 rows=27466
width=194)(actual time=185.198..12753.315 rows=31099 loops=1) 
                               Recheck Cond: ((nodeid)::text = 'abcd'::text)
                               Filter: ((ts > '2010-01-01 00:00:00'::timestamp without time zone) AND (ts < '2011-02-15
15:00:00'::timestampwithout time zone)) 
                               ->  Bitmap Index Scan on idx_unitstat_y2010m09_nodeid_gps_ts  (cost=0.00..482.69
rows=27472width=0) (actual time=158.072..158.072 rows=31099 loops=1) 
                                     Index Cond: ((nodeid)::text = 'abcd'::text)
                         ->  Index Scan using fki_unitstat_y2010m08_nodeid_ts_fkey on unitstat_y2010m08 unitstat
(cost=0.00..9353.76rows=4824 width=194) (actual time=31.351..10259.090 rows=17606 loops=1) 
                               Index Cond: (((nodeid)::text = 'abcd'::text) AND (ts > '2010-01-01 00:00:00'::timestamp
withouttime zone) AND (ts < '2011-02-15 15:00:00'::timestamp without time zone)) 
                         ->  Index Scan using fki_unitstat_y2010m07_nodeid_ts_fkey on unitstat_y2010m07 unitstat
(cost=0.00..8686.72rows=4492 width=194) (actual time=41.572..9636.335 rows=9511 loops=1) 
                               Index Cond: (((nodeid)::text = 'abcd'::text) AND (ts > '2010-01-01 00:00:00'::timestamp
withouttime zone) AND (ts < '2011-02-15 15:00:00'::timestamp without time zone)) 
                         ->  Bitmap Heap Scan on unitstat_y2010m06 unitstat  (cost=311.50..32142.18 rows=17406
width=194)(actual time=113.857..12136.570 rows=17041 loops=1) 
                               Recheck Cond: ((nodeid)::text = 'abcd'::text)
                               Filter: ((ts > '2010-01-01 00:00:00'::timestamp without time zone) AND (ts < '2011-02-15
15:00:00'::timestampwithout time zone)) 
                               ->  Bitmap Index Scan on idx_unitstat_y2010m06_nodeid_gps_ts  (cost=0.00..307.15
rows=17410width=0) (actual time=91.638..91.638 rows=17041 loops=1) 
                                     Index Cond: ((nodeid)::text = 'abcd'::text)
                         ->  Index Scan using fki_unitstat_y2010m05_nodeid_ts_fkey on unitstat_y2010m05 unitstat
(cost=0.00..11942.82rows=6279 width=193) (actual time=62.264..19887.675 rows=19246 loops=1) 
                               Index Cond: (((nodeid)::text = 'abcd'::text) AND (ts > '2010-01-01 00:00:00'::timestamp
withouttime zone) AND (ts < '2011-02-15 15:00:00'::timestamp without time zone)) 
                         ->  Index Scan using fki_unitstat_y2010m04_nodeid_ts_fkey on unitstat_y2010m04 unitstat
(cost=0.00..11840.93rows=6194 width=193) (actual time=52.735..17302.361 rows=21936 loops=1) 
                               Index Cond: (((nodeid)::text = 'abcd'::text) AND (ts > '2010-01-01 00:00:00'::timestamp
withouttime zone) AND (ts < '2011-02-15 15:00:00'::timestamp without time zone)) 
                         ->  Index Scan using fki_unitstat_y2010m03_nodeid_ts_fkey on unitstat_y2010m03 unitstat
(cost=0.00..11664.36rows=6101 width=194) (actual time=66.613..17541.374 rows=15471 loops=1) 
                               Index Cond: (((nodeid)::text = 'abcd'::text) AND (ts > '2010-01-01 00:00:00'::timestamp
withouttime zone) AND (ts < '2011-02-15 15:00:00'::timestamp without time zone)) 
     Total runtime: 205855.569 ms


Regards,

Kim