Re: LIMIT on partitioned-table!?

Thank you Shaun,

removing the ORDER BY worked. But I am afraid to ask this. How can I order by partition? It seams that the planner has picked a random(!?) order of partition to select from. The returned records, from the selected partition, are correctly sorted bythe index though.

On 2011-02-15 15:49, Shaun Thomas wrote:
> On 02/15/2011 08:23 AM, Kim A. Brandt wrote:
>
>> 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?
>
> Make sure you have constraint_exclusion set to 'on' in your config. Also, what are your checks for your partitions? You've got a pretty wide range in your 'ts' checks, so if you're using them as your partition definition, you're not helping yourself.

The parameter `constraint_exclusion' is set to `partition'. Postgres is on FreeBSD.

My checks (if I understand you right) are as follows:

CREATE TABLE flexserver.unitstat_y2011m02
(
ts timestamp without time zone NOT NULL,
nodeid character varying(10) NOT NULL,
gps_ts timestamp without time zone NOT NULL,
...
CONSTRAINT unitstat_y2011m02_ts_check CHECK (ts >= '2011-02-01 00:00:00'::timestamp without time zone AND ts < '2011-03-01 00:00:00'::timestamp without time zone)
)
INHERITS (flexserver.unitstat);

Each partition is constrained to one month.

About the wide range, I am aware of that. This probably has to change anyway!? So the current (and probably final solution) is to use a narrower search range. Thank you for the hint.

> The main issue might just be that you've used an order clause. LIMIT 1000 or not, even if it can restrict the result set based on your CHECK criteria, it'll still need to select every matching row from every matched partition, order the results, and chop off the first 1000.

That was it. Just how can one order by partition if one would do a wide range search over multiple partitions?

The new query and EXPLAIN ANALYSE-output is:

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;

Limit (cost=0.00..1862.46 rows=1000 width=194) (actual time=2.569..18.948 rows=1000 loops=1)
-> Result (cost=0.00..225611.08 rows=121136 width=194) (actual time=2.566..15.412 rows=1000 loops=1)
-> Append (cost=0.00..225611.08 rows=121136 width=194) (actual time=2.558..11.243 rows=1000 loops=1)
-> Seq Scan on unitstat (cost=0.00..14.90 rows=1 width=258) (actual time=0.003..0.003 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=2.550..7.701 rows=1000 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=1.706..1.706 rows=5377 loops=1)
Index Cond: ((nodeid)::text = 'abcd'::text)
-> Bitmap Heap Scan on unitstat_y2011m02 unitstat (cost=52.92..3744.97 rows=1934 width=194) (never executed)
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.44 rows=1935 width=0) (never executed)
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) (never executed)
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) (never executed)
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) (never executed)
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) (never executed)
Index Cond: ((nodeid)::text = 'abcd'::text)
-> Bitmap Heap Scan on unitstat_y2010m12 unitstat (cost=128.72..9050.29 rows=4683 width=194) (never executed)
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) (never executed)
Index Cond: ((nodeid)::text = 'abcd'::text)
-> Bitmap Heap Scan on unitstat_y2010m10 unitstat (cost=101.74..9686.81 rows=4987 width=194) (never executed)
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) (never executed)
Index Cond: ((nodeid)::text = 'abcd'::text)
-> Bitmap Heap Scan on unitstat_y2010m09 unitstat (cost=489.56..49567.74 rows=27466 width=194) (never executed)
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) (never executed)
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) (never executed)
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) (never executed)
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) (never executed)
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) (never executed)
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) (never executed)
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) (never executed)
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) (never executed)
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: 21.219 ms

Now most partitions are not looked at (never executed). But how can one affect the order of partition (e.g. begin with the oldest)?

Sorry for asking the same thing thrice. I just need to understand this one. :)

Kind regards,

Kim