LIMIT on partitioned-table!?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux