Skip site navigation (1) Skip section navigation (2)

LIMIT on partitioned-table!?

From: "Kim A(dot) Brandt" <kimabrandt(at)gmx(dot)de>
To: pgsql-performance(at)postgresql(dot)org
Subject: LIMIT on partitioned-table!?
Date: 2011-02-15 14:23:40
Message-ID: 4D5A8C6C.7080000@gmx.de (view raw or flat)
Thread:
Lists: 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 something wrong? It looks as if postgres queries all partitions and then LIMITing the records afterwards!? This results in a 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=203492 loops=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'::timestamp without time zone) AND ((nodeid)::text = 'abcd'::text))
                         ->  Bitmap Heap Scan on unitstat_y2011m01 unitstat  (cost=116.47..8097.17 rows=4189 width=194) (actual time=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'::timestamp without time zone))
                               ->  Bitmap Index Scan on idx_unitstat_y2011m01_nodeid_gps_ts  (cost=0.00..115.42 rows=4190 width=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) (actual time=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'::timestamp without time zone))
                               ->  Bitmap Index Scan on idx_unitstat_y2011m02_nodeid_gps_ts  (cost=0.00..52.20 rows=1906 width=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.11 rows=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 without time 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.31 rows=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 without time 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'::timestamp without time zone))
                               ->  Bitmap Index Scan on idx_unitstat_y2010m11_nodeid_gps_ts  (cost=0.00..581.43 rows=21970 width=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) (actual time=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'::timestamp without time zone))
                               ->  Bitmap Index Scan on idx_unitstat_y2010m12_nodeid_gps_ts  (cost=0.00..127.55 rows=4684 width=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) (actual time=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'::timestamp without time zone))
                               ->  Bitmap Index Scan on idx_unitstat_y2010m10_nodeid_gps_ts  (cost=0.00..100.49 rows=4988 width=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'::timestamp without time zone))
                               ->  Bitmap Index Scan on idx_unitstat_y2010m09_nodeid_gps_ts  (cost=0.00..482.69 rows=27472 width=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.76 rows=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 without time 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.72 rows=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 without time 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'::timestamp without time zone))
                               ->  Bitmap Index Scan on idx_unitstat_y2010m06_nodeid_gps_ts  (cost=0.00..307.15 rows=17410 width=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.82 rows=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 without time 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.93 rows=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 without time 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.36 rows=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 without time zone) AND (ts < '2011-02-15 15:00:00'::timestamp without time zone))
     Total runtime: 205855.569 ms


Regards,

Kim

Responses

pgsql-performance by date

Next:From: Shaun ThomasDate: 2011-02-15 14:49:37
Subject: Re: LIMIT on partitioned-table!?
Previous:From: Strange, John WDate: 2011-02-15 11:45:28
Subject: Checkpointing question

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group