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

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

Reply via email to