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
