Thread: partitioning query planner almost always scans all tables

Hello group,

we have a timeseries table, and we tried to partition it by month (with pg_partman). It seems the query planner always reads all tables regardless of WHERE, except when WHERE is equality.

the parent table:

ifms_db=# \dS measurement_events

              Table "public.measurement_events"

        Column         |           Type           | Modifiers

-----------------------+--------------------------+-----------

 measurement_source_id | uuid                     | not null

 measurement_time      | timestamp with time zone | not null

 event_reception_time  | timestamp with time zone | not null

 measurement_value     | character varying(200)   | not null

 quality               | character varying(500)   | not null

Indexes:

    "measurement_events_pkey" PRIMARY KEY, btree (measurement_source_id, measurement_time)

Triggers:

    measurement_events_part_trig BEFORE INSERT ON measurement_events FOR EACH ROW EXECUTE PROCEDURE measurement_events_part_trig_func()

Number of child tables: 25 (Use \d+ to list them.)

One of the children tables (2014_3)

ifms_db=# \dS measurement_events_p2014_03

          Table "public.measurement_events_p2014_03"

        Column         |           Type           | Modifiers

-----------------------+--------------------------+-----------

 measurement_source_id | uuid                     | not null

 measurement_time      | timestamp with time zone | not null

 event_reception_time  | timestamp with time zone | not null

 measurement_value     | character varying(200)   | not null

 quality               | character varying(500)   | not null

Indexes:

    "measurement_events_p2014_03_pkey" PRIMARY KEY, btree (measurement_source_id, measurement_time)

Check constraints:

    "measurement_events_p2014_03_partition_check" CHECK (measurement_time >= '2014-03-01 00:00:00+02'::timestamp with time zone AND measurement_time < '2014-04-01 00:00:00+03'::timestamp with time zone)

Inherits: measurement_events

The query:

# explain analyze  select * from measurement_events where measurement_source_id='df86917e-8df0-11e1-8f8f-525400e76ceb' AND measurement_time >= DATE '2015-01-01' limit 1;

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------

 Limit  (cost=0.00..4.12 rows=1 width=87) (actual time=0.377..0.377 rows=1 loops=1)

   ->  Append  (cost=0.00..2696.08 rows=655 width=87) (actual time=0.376..0.376 rows=1 loops=1)

         ->  Seq Scan on measurement_events  (cost=0.00..0.00 rows=1 width=966) (actual time=0.001..0.001 rows=0 loops=1)

               Filter: ((measurement_time >= '2015-01-01'::date) AND (measurement_source_id = 'df86917e-8df0-11e1-8f8f-525400e76ceb'::uuid))

         ->  Index Scan using measurement_events_p2014_01_pkey on measurement_events_p2014_01  (cost=0.14..8.16 rows=1 width=966) (actual time=0.005..0.005 rows=0 loops=1)

               Index Cond: ((measurement_source_id = 'df86917e-8df0-11e1-8f8f-525400e76ceb'::uuid) AND (measurement_time >= '2015-01-01'::date))

         ->  Index Scan using measurement_events_p2014_02_pkey on measurement_events_p2014_02  (cost=0.14..8.16 rows=1 width=966) (actual time=0.002..0.002 rows=0 loops=1)

               Index Cond: ((measurement_source_id = 'df86917e-8df0-11e1-8f8f-525400e76ceb'::uuid) AND (measurement_time >= '2015-01-01'::date))

         ->  Index Scan using measurement_events_p2014_03_pkey on measurement_events_p2014_03  (cost=0.14..8.16 rows=1 width=966) (actual time=0.002..0.002 rows=0 loops=1)

               Index Cond: ((measurement_source_id = 'df86917e-8df0-11e1-8f8f-525400e76ceb'::uuid) AND (measurement_time >= '2015-01-01'::date))

         ->  Index Scan using measurement_events_p2014_04_pkey on measurement_events_p2014_04  (cost=0.14..8.16 rows=1 width=966) (actual time=0.001..0.001 rows=0 loops=1)

               Index Cond: ((measurement_source_id = 'df86917e-8df0-11e1-8f8f-525400e76ceb'::uuid) AND (measurement_time >= '2015-01-01'::date))

         ->  Index Scan using measurement_events_p2014_05_pkey on measurement_events_p2014_05  (cost=0.14..8.16 rows=1 width=966) (actual time=0.001..0.001 rows=0 loops=1)

               Index Cond: ((measurement_source_id = 'df86917e-8df0-11e1-8f8f-525400e76ceb'::uuid) AND (measurement_time >= '2015-01-01'::date))

         ->  Index Scan using measurement_events_p2014_06_pkey on measurement_events_p2014_06  (cost=0.14..8.16 rows=1 width=966) (actual time=0.002..0.002 rows=0 loops=1)

               Index Cond: ((measurement_source_id = 'df86917e-8df0-11e1-8f8f-525400e76ceb'::uuid) AND (measurement_time >= '2015-01-01'::date))

         ->  Index Scan using measurement_events_p2014_07_pkey on measurement_events_p2014_07  (cost=0.14..8.16 rows=1 width=966) (actual time=0.001..0.001 rows=0 loops=1)

               Index Cond: ((measurement_source_id = 'df86917e-8df0-11e1-8f8f-525400e76ceb'::uuid) AND (measurement_time >= '2015-01-01'::date))

         ->  Index Scan using measurement_events_p2014_08_pkey on measurement_events_p2014_08  (cost=0.14..8.16 rows=1 width=966) (actual time=0.002..0.002 rows=0 loops=1)

               Index Cond: ((measurement_source_id = 'df86917e-8df0-11e1-8f8f-525400e76ceb'::uuid) AND (measurement_time >= '2015-01-01'::date))

         ->  Index Scan using measurement_events_p2014_09_pkey on measurement_events_p2014_09  (cost=0.14..8.16 rows=1 width=966) (actual time=0.001..0.001 rows=0 loops=1)

               Index Cond: ((measurement_source_id = 'df86917e-8df0-11e1-8f8f-525400e76ceb'::uuid) AND (measurement_time >= '2015-01-01'::date))

         ->  Index Scan using measurement_events_p2014_10_pkey on measurement_events_p2014_10  (cost=0.14..8.16 rows=1 width=966) (actual time=0.001..0.001 rows=0 loops=1)

               Index Cond: ((measurement_source_id = 'df86917e-8df0-11e1-8f8f-525400e76ceb'::uuid) AND (measurement_time >= '2015-01-01'::date))

         ->  Index Scan using measurement_events_p2014_11_pkey on measurement_events_p2014_11  (cost=0.14..8.16 rows=1 width=966) (actual time=0.002..0.002 rows=0 loops=1)

               Index Cond: ((measurement_source_id = 'df86917e-8df0-11e1-8f8f-525400e76ceb'::uuid) AND (measurement_time >= '2015-01-01'::date))

         ->  Index Scan using measurement_events_p2014_12_pkey on measurement_events_p2014_12  (cost=0.28..8.04 rows=1 width=51) (actual time=0.009..0.009 rows=0 loops=1)

               Index Cond: ((measurement_source_id = 'df86917e-8df0-11e1-8f8f-525400e76ceb'::uuid) AND (measurement_time >= '2015-01-01'::date))

         ->  Bitmap Heap Scan on measurement_events_p2015_01  (cost=31.02..2500.30 rows=630 width=54) (actual time=0.345..0.345 rows=1 loops=1)

               Recheck Cond: ((measurement_source_id = 'df86917e-8df0-11e1-8f8f-525400e76ceb'::uuid) AND (measurement_time >= '2015-01-01'::date))

               ->  Bitmap Index Scan on measurement_events_p2015_01_pkey  (cost=0.00..30.87 rows=630 width=0) (actual time=0.269..0.269 rows=718 loops=1)

                     Index Cond: ((measurement_source_id = 'df86917e-8df0-11e1-8f8f-525400e76ceb'::uuid) AND (measurement_time >= '2015-01-01'::date))

         ->  Index Scan using measurement_events_p2015_02_pkey on measurement_events_p2015_02  (cost=0.14..8.16 rows=1 width=966) (never executed)

               Index Cond: ((measurement_source_id = 'df86917e-8df0-11e1-8f8f-525400e76ceb'::uuid) AND (measurement_time >= '2015-01-01'::date))

         ->  Index Scan using measurement_events_p2015_03_pkey on measurement_events_p2015_03  (cost=0.14..8.16 rows=1 width=966) (never executed)

.....

More results:

This query:

ifms_db=# explain analyze  select * from measurement_events where measurement_source_id='bd77387a-fdb4-4531-9bb7-7ef67a8f647d' AND measurement_time = DATE  '2015-01-14 15:30:01+02'   limit 1;

searches in all tables:

This query (no date casting):

ifms_db=# explain analyze  select * from measurement_events where measurement_source_id='bd77387a-fdb4-4531-9bb7-7ef67a8f647d' AND measurement_time =  '2015-01-14 15:30:01+02'   limit 1;

searches only 1 table,

and this query (>, no casting):

ifms_db=# explain analyze  select * from measurement_events where measurement_source_id='bd77387a-fdb4-4531-9bb7-7ef67a8f647d' AND measurement_time >  '2015-01-14 15:30:01+02'   limit 1;

searches first the correct table, then all the others.

any ideas?

Hello,

Your constraint column is of 'timestamp with time zone' and the
query uses 'date'. The comparison between them is an operator
based on a static, non-immutable function so constraint exclusion
doesn't work.

SELECT o.oprname, o.oprcode, p.provolatile
 FROM pg_operator o join pg_proc p on (p.oid = o.oprcode)
 WHERE oprname = '<' AND
       oprleft = 'timestamp with time zone'::regtype AND
       oprright = 'date'::regtype;

 oprname |       oprcode       | provolatile
---------+---------------------+-------------
 <       | timestamptz_lt_date | s

# '<' is a random selection. Any comparison ops will do.

The following query instead will do what you wanted. Only the
operand of '>=' is changed.

EXPLAIN ANALYZE  SELECT * FROM measurement_events WHERE measurement_source_id='df86917e-8df0-11e1-8f8f-525400e76ceb'
ANDmeasurement_time >= '2015-01-01 00:00:00+0' LIMIT 1; 

The function in the expression used for exclusion is immutable.

SELECT o.oprname, o.oprcode, p.provolatile
 FROM pg_operator o join pg_proc p on (p.oid = o.oprcode)
 WHERE oprname = '<' AND
       oprleft = 'timestamp with time zone'::regtype AND
       oprright = 'timestamp with time zone::regtype;

 oprname |    oprcode     | provolatile
---------+----------------+-------------
 <       | timestamptz_lt | i


The details about this in the following page,

http://www.postgresql.org/docs/9.3/static/ddl-partitioning.html

> 5.9.6 Cavert

regards,

--
Kyotaro Horiguchi
NTT Open Source Software Center


Jan 2015 06:42:53 -0700, Rob Sargent <robjsargent@gmail.com> wrote in <31FED87E-D31B-4CF9-93F8-CC0F131CB6DF@gmail.com>
> I don't understand having both UUID and time stamp in your PK? The first is by defn. unique and the second might be.
>
> Sent from my iPhone
>
> > On Jan 19, 2015, at 6:12 AM, Spiros Ioannou <sivann@inaccess.com> wrote:
> >
> > Hello group,
> > we have a timeseries table, and we tried to partition it by month (with pg_partman). It seems the query planner
alwaysreads all tables regardless of WHERE, except when WHERE is equality. 
> >
> > the parent table:
> >
> > ifms_db=# \dS measurement_events
> >               Table "public.measurement_events"
> >         Column         |           Type           | Modifiers
> > -----------------------+--------------------------+-----------
> >  measurement_source_id | uuid                     | not null
> >  measurement_time      | timestamp with time zone | not null
> >  event_reception_time  | timestamp with time zone | not null
> >  measurement_value     | character varying(200)   | not null
> >  quality               | character varying(500)   | not null
> > Indexes:
> >     "measurement_events_pkey" PRIMARY KEY, btree (measurement_source_id, measurement_time)
> > Triggers:
> >     measurement_events_part_trig BEFORE INSERT ON measurement_events FOR EACH ROW EXECUTE PROCEDURE
measurement_events_part_trig_func()
> > Number of child tables: 25 (Use \d+ to list them.)
> >
> >
> > One of the children tables (2014_3)
> >
> > ifms_db=# \dS measurement_events_p2014_03
> >           Table "public.measurement_events_p2014_03"
> >         Column         |           Type           | Modifiers
> > -----------------------+--------------------------+-----------
> >  measurement_source_id | uuid                     | not null
> >  measurement_time      | timestamp with time zone | not null
> >  event_reception_time  | timestamp with time zone | not null
> >  measurement_value     | character varying(200)   | not null
> >  quality               | character varying(500)   | not null
> > Indexes:
> >     "measurement_events_p2014_03_pkey" PRIMARY KEY, btree (measurement_source_id, measurement_time)
> > Check constraints:
> >     "measurement_events_p2014_03_partition_check" CHECK (measurement_time >= '2014-03-01 00:00:00+02'::timestamp
withtime zone AND measurement_time < '2014-04-01 00:00:00+03'::timestamp with time zone) 
> > Inherits: measurement_events
> >
> >
> >
> > The query:
> > # explain analyze  select * from measurement_events where
measurement_source_id='df86917e-8df0-11e1-8f8f-525400e76ceb'AND measurement_time >= DATE '2015-01-01' limit 1; 
> >
> >
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> >  Limit  (cost=0.00..4.12 rows=1 width=87) (actual time=0.377..0.377 rows=1 loops=1)
> >    ->  Append  (cost=0.00..2696.08 rows=655 width=87) (actual time=0.376..0.376 rows=1 loops=1)
> >          ->  Seq Scan on measurement_events  (cost=0.00..0.00 rows=1 width=966) (actual time=0.001..0.001 rows=0
loops=1)
> >                Filter: ((measurement_time >= '2015-01-01'::date) AND (measurement_source_id =
'df86917e-8df0-11e1-8f8f-525400e76ceb'::uuid))
> >          ->  Index Scan using measurement_events_p2014_01_pkey on measurement_events_p2014_01  (cost=0.14..8.16
rows=1width=966) (actual time=0.005..0.005 rows=0 loops=1) 
> >                Index Cond: ((measurement_source_id = 'df86917e-8df0-11e1-8f8f-525400e76ceb'::uuid) AND
(measurement_time>= '2015-01-01'::date)) 
> >          ->  Index Scan using measurement_events_p2014_02_pkey on measurement_events_p2014_02  (cost=0.14..8.16
rows=1width=966) (actual time=0.002..0.002 rows=0 loops=1) 
> >                Index Cond: ((measurement_source_id = 'df86917e-8df0-11e1-8f8f-525400e76ceb'::uuid) AND
(measurement_time>= '2015-01-01'::date)) 
> >          ->  Index Scan using measurement_events_p2014_03_pkey on measurement_events_p2014_03  (cost=0.14..8.16
rows=1width=966) (actual time=0.002..0.002 rows=0 loops=1) 
> >                Index Cond: ((measurement_source_id = 'df86917e-8df0-11e1-8f8f-525400e76ceb'::uuid) AND
(measurement_time>= '2015-01-01'::date)) 
> >          ->  Index Scan using measurement_events_p2014_04_pkey on measurement_events_p2014_04  (cost=0.14..8.16
rows=1width=966) (actual time=0.001..0.001 rows=0 loops=1) 
> >                Index Cond: ((measurement_source_id = 'df86917e-8df0-11e1-8f8f-525400e76ceb'::uuid) AND
(measurement_time>= '2015-01-01'::date)) 
> >          ->  Index Scan using measurement_events_p2014_05_pkey on measurement_events_p2014_05  (cost=0.14..8.16
rows=1width=966) (actual time=0.001..0.001 rows=0 loops=1) 
> >                Index Cond: ((measurement_source_id = 'df86917e-8df0-11e1-8f8f-525400e76ceb'::uuid) AND
(measurement_time>= '2015-01-01'::date)) 
> >          ->  Index Scan using measurement_events_p2014_06_pkey on measurement_events_p2014_06  (cost=0.14..8.16
rows=1width=966) (actual time=0.002..0.002 rows=0 loops=1) 
> >                Index Cond: ((measurement_source_id = 'df86917e-8df0-11e1-8f8f-525400e76ceb'::uuid) AND
(measurement_time>= '2015-01-01'::date)) 
> >          ->  Index Scan using measurement_events_p2014_07_pkey on measurement_events_p2014_07  (cost=0.14..8.16
rows=1width=966) (actual time=0.001..0.001 rows=0 loops=1) 
> >                Index Cond: ((measurement_source_id = 'df86917e-8df0-11e1-8f8f-525400e76ceb'::uuid) AND
(measurement_time>= '2015-01-01'::date)) 
> >          ->  Index Scan using measurement_events_p2014_08_pkey on measurement_events_p2014_08  (cost=0.14..8.16
rows=1width=966) (actual time=0.002..0.002 rows=0 loops=1) 
> >                Index Cond: ((measurement_source_id = 'df86917e-8df0-11e1-8f8f-525400e76ceb'::uuid) AND
(measurement_time>= '2015-01-01'::date)) 
> >          ->  Index Scan using measurement_events_p2014_09_pkey on measurement_events_p2014_09  (cost=0.14..8.16
rows=1width=966) (actual time=0.001..0.001 rows=0 loops=1) 
> >                Index Cond: ((measurement_source_id = 'df86917e-8df0-11e1-8f8f-525400e76ceb'::uuid) AND
(measurement_time>= '2015-01-01'::date)) 
> >          ->  Index Scan using measurement_events_p2014_10_pkey on measurement_events_p2014_10  (cost=0.14..8.16
rows=1width=966) (actual time=0.001..0.001 rows=0 loops=1) 
> >                Index Cond: ((measurement_source_id = 'df86917e-8df0-11e1-8f8f-525400e76ceb'::uuid) AND
(measurement_time>= '2015-01-01'::date)) 
> >          ->  Index Scan using measurement_events_p2014_11_pkey on measurement_events_p2014_11  (cost=0.14..8.16
rows=1width=966) (actual time=0.002..0.002 rows=0 loops=1) 
> >                Index Cond: ((measurement_source_id = 'df86917e-8df0-11e1-8f8f-525400e76ceb'::uuid) AND
(measurement_time>= '2015-01-01'::date)) 
> >          ->  Index Scan using measurement_events_p2014_12_pkey on measurement_events_p2014_12  (cost=0.28..8.04
rows=1width=51) (actual time=0.009..0.009 rows=0 loops=1) 
> >                Index Cond: ((measurement_source_id = 'df86917e-8df0-11e1-8f8f-525400e76ceb'::uuid) AND
(measurement_time>= '2015-01-01'::date)) 
> >          ->  Bitmap Heap Scan on measurement_events_p2015_01  (cost=31.02..2500.30 rows=630 width=54) (actual
time=0.345..0.345rows=1 loops=1) 
> >                Recheck Cond: ((measurement_source_id = 'df86917e-8df0-11e1-8f8f-525400e76ceb'::uuid) AND
(measurement_time>= '2015-01-01'::date)) 
> >                ->  Bitmap Index Scan on measurement_events_p2015_01_pkey  (cost=0.00..30.87 rows=630 width=0)
(actualtime=0.269..0.269 rows=718 loops=1) 
> >                      Index Cond: ((measurement_source_id = 'df86917e-8df0-11e1-8f8f-525400e76ceb'::uuid) AND
(measurement_time>= '2015-01-01'::date)) 
> >          ->  Index Scan using measurement_events_p2015_02_pkey on measurement_events_p2015_02  (cost=0.14..8.16
rows=1width=966) (never executed) 
> >                Index Cond: ((measurement_source_id = 'df86917e-8df0-11e1-8f8f-525400e76ceb'::uuid) AND
(measurement_time>= '2015-01-01'::date)) 
> >          ->  Index Scan using measurement_events_p2015_03_pkey on measurement_events_p2015_03  (cost=0.14..8.16
rows=1width=966) (never executed) 
> > .....
> >
> > More results:
> >
> > This query:
> > ifms_db=# explain analyze  select * from measurement_events where
measurement_source_id='bd77387a-fdb4-4531-9bb7-7ef67a8f647d'AND measurement_time = DATE  '2015-01-14 15:30:01+02'
limit1; 
> >
> > searches in all tables:
> >
> > This query (no date casting):
> > ifms_db=# explain analyze  select * from measurement_events where
measurement_source_id='bd77387a-fdb4-4531-9bb7-7ef67a8f647d'AND measurement_time =  '2015-01-14 15:30:01+02'   limit 1; 
> >
> > searches only 1 table,
> >
> > and this query (>, no casting):
> > ifms_db=# explain analyze  select * from measurement_events where
measurement_source_id='bd77387a-fdb4-4531-9bb7-7ef67a8f647d'AND measurement_time >  '2015-01-14 15:30:01+02'   limit 1; 
> >
> > searches first the correct table, then all the others.
> >
> > any ideas?



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

Hi,

> @Kyotaro HORIGUCHI
> thanks for your reply and time Kyotaro,

Not at all.

> Using the following query
> EXPLAIN ANALYZE  SELECT * FROM measurement_events WHERE
> measurement_source_id='df86917e-8df0-11e1-8f8f-525400e76ceb' AND
> measurement_time >= '2015-01-01 00:00:00+0' LIMIT 1;
>
> produces this plan:
>
>  Limit  (cost=0.00..4.02 rows=1 width=67) (actual time=49.125..49.125
> rows=1 loops=1)
>    ->  Append  (cost=0.00..3644.05 rows=907 width=67) (actual
> time=49.122..49.122 rows=1 loops=1)
>          ->  Seq Scan on measurement_events  (cost=0.00..0.00 rows=1
> width=966) (actual time=0.003..0.003 rows=0 loops=1)
>                Filter: ((measurement_time >= '2015-01-01
> 02:00:00+02'::timestamp with time zone) AND (measurement_source_id =
> 'df86917e-8df0-11e1-8f8f-525400e76ceb'::uuid))
>          ->  Bitmap Heap Scan on *measurement_events_p2015_01*
>  (cost=41.73..3546.10 rows=894 width=54) (actual time=49.119..49.119 rows=1
> loops=1)
>                Recheck Cond: ((measurement_source_id =
> 'df86917e-8df0-11e1-8f8f-525400e76ceb'::uuid) AND (measurement_time >=
> '2015-01-01 02:00:00+02'::timestamp with time zone))
>                ->  Bitmap Index Scan on *measurement_events_p2015_01_pkey*
>  (cost=0.00..41.51 rows=894 width=0) (actual time=41.836..41.836 rows=997
> loops=1)
>                      Index Cond: ((measurement_source_id =
> 'df86917e-8df0-11e1-8f8f-525400e76ceb'::uuid) AND (measurement_time >=
> '2015-01-01 02:00:00+02'::timestamp with time zone))
>          ->  Index Scan using *measurement_events_p2015_02_pkey* on
> *measurement_events_p2015_02*  (cost=0.14..8.16 rows=1 width=966) (never
> executed)
>                Index Cond: ((measurement_source_id =
> 'df86917e-8df0-11e1-8f8f-525400e76ceb'::uuid) AND (measurement_time >=
> '2015-01-01 02:00:00+02'::timestamp with time zone))
>          ->  Index Scan using *measurement_events_p2015_03_pkey* on
> *measurement_events_p2015_03*  (cost=0.14..8.16 rows=1 width=966) (never
> executed)
>                Index Cond: ((measurement_source_id =
> 'df86917e-8df0-11e1-8f8f-525400e76ceb'::uuid) AND (measurement_time >=
> '2015-01-01 02:00:00+02'::timestamp with time zone))
> ... (cut for brevity)
>
> 1) Do you know if this means that the query will stop on 1st find (since it
> is limit 1), or will it search all tables regardless results?

It saids that only the first table was scanned because 1 row had
been acquired. But it is unclear whether constraint exclusion
worked. All of the table shown above seems to have the data after
2015/1/1 and match the condition of your query. It's okay if
p_2014_12 and the earlier is not seen in the explain result and
the order in which the tables appear seems to me suggesting it is
okay. Please examine it on that ponit.

> 2) To improve on the above, do you (or anyone else) have any input on this:
>
> to get the latest value from all tables, we were using the following query
> (before partitioning):
>
> EXPLAIN ANALYZE  SELECT * FROM measurement_events WHERE
> measurement_source_id='df86917e-8df0-11e1-8f8f-525400e76ceb' ORDER BY
> measurement_time DESC LIMIT 1;
>
> This seems to fail, scanning all tables. Do you think this can be improved
> at all ? The query plan of the above query is as follows:

The combination of sorting by measurement_source_id and limit
hinders constraint exclusion because the order of the column
across whole the inheritance is not known to planner. And the
below plan also dosn't show whether constraint exclusion worked
or not, by the same reason. But I suppose it worked.

Since constraint exclusion worked, it seems enough optmized. What
kind of optimizaition do you expect?


>
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>  Limit  (cost=5.57..9.99 rows=1 width=921) (actual time=5.361..5.361 rows=1
> loops=1)
>    ->  Merge Append  (cost=5.57..451374.16 rows=102155 width=921) (actual
> time=5.359..5.359 rows=1 loops=1)
>          Sort Key: measurement_events.measurement_time
>          ->  Index Scan Backward using measurement_events_pkey on
> *measurement_events*  (cost=0.12..8.14 rows=1 width=966) (actual
> time=0.004..0.004 rows=0 loops=1)
>                Index Cond: (measurement_source_id =
> 'df86917e-8df0-11e1-8f8f-525400e76ceb'::uuid)
>          ->  Index Scan Backward using *measurement_events_p2014_01_pkey*
> on *measurement_events_p2014_01*  (cost=0.14..8.16 rows=1 width=966)
> (actual time=0.002..0.002 rows=0 loops=1)
>                Index Cond: (measurement_source_id =
> 'df86917e-8df0-11e1-8f8f-525400e76ceb'::uuid)
>          ->  Index Scan Backward using *measurement_events_p2014_02_pkey*
> on *measurement_events_p2014_02*  (cost=0.14..8.16 rows=1 width=966)
> (actual time=0.001..0.001 rows=0 loops=1)
>                Index Cond: (measurement_source_id =
> 'df86917e-8df0-11e1-8f8f-525400e76ceb'::uuid)
>          ->  Index Scan Backward using *measurement_events_p2014_03_pkey*
> on *measurement_events_p2014_03*  (cost=0.14..8.16 rows=1 width=966)
> (actual time=0.001..0.001 rows=0 loops=1)
>                Index Cond: (measurement_source_id =
> 'df86917e-8df0-11e1-8f8f-525400e76ceb'::uuid)
>
> ............. (cut for brevity)

--
Kyotaro Horiguchi
NTT Open Source Software Center

Spiros Ioannou <sivann@inaccess.com> writes:
> It is ORDER BY measurement_time, not measurement_id, and measurement_time
> is used to create the partition. So the planner should know the correct
> order, but instead it seems to query tables in the wrong order.

The planner does not know that, and even if it attempted to figure it out
by comparing the child tables' constraints, it could not generate a plan
that considered only one child table as you incorrectly imagine.  What if
the "latest" table turned out to be empty at runtime?

The obtained plan with a Merge Append atop Index Scan Backwards nodes
seems perfectly reasonable to me.  This will result in fetching only the
latest row within each partition, so that the work involved is O(number of
partitions) not O(total number of rows).

If you're not happy with that, reconsider how many partitions you really
need.  Newbies almost invariably create far more partitions than is a good
idea for performance.  In my view, if you've got more than a couple dozen,
you're doing it wrong.  Partitioning is, in general, not a benefit for
query performance (except in a few very narrow, specialized cases); and
the more partitions you have the worse the penalty.  Partitioning only
helps for data management, in particular being able to drop old data in
bulk rather than through expensive DELETE WHERE queries.  How often do
you do that, and do you really need to be able to do it at a small
granularity?

            regards, tom lane

Hello,

Your constraint column is of 'timestamp with time zone' and the
query uses 'date'. The comparison between them is an operator
based on a static, non-immutable function so constraint exclusion
doesn't work.

SELECT o.oprname, o.oprcode, p.provolatile
 FROM pg_operator o join pg_proc p on (p.oid = o.oprcode)
 WHERE oprname = '<' AND
       oprleft = 'timestamp with time zone'::regtype AND
       oprright = 'date'::regtype;

 oprname |       oprcode       | provolatile
---------+---------------------+-------------
 <       | timestamptz_lt_date | s

# '<' is a random selection. Any comparison ops will do.

The following query instead will do what you wanted. Only the
operand of '>=' is changed.

EXPLAIN ANALYZE  SELECT * FROM measurement_events WHERE measurement_source_id='df86917e-8df0-11e1-8f8f-525400e76ceb'
ANDmeasurement_time >= '2015-01-01 00:00:00+0' LIMIT 1; 

The function in the expression used for exclusion is immutable.

SELECT o.oprname, o.oprcode, p.provolatile
 FROM pg_operator o join pg_proc p on (p.oid = o.oprcode)
 WHERE oprname = '<' AND
       oprleft = 'timestamp with time zone'::regtype AND
       oprright = 'timestamp with time zone::regtype;

 oprname |    oprcode     | provolatile
---------+----------------+-------------
 <       | timestamptz_lt | i


The details about this in the following page,

http://www.postgresql.org/docs/9.3/static/ddl-partitioning.html

> 5.9.6 Cavert

regards,

--
Kyotaro Horiguchi
NTT Open Source Software Center


Jan 2015 06:42:53 -0700, Rob Sargent <robjsargent@gmail.com> wrote in <31FED87E-D31B-4CF9-93F8-CC0F131CB6DF@gmail.com>
> I don't understand having both UUID and time stamp in your PK? The first is by defn. unique and the second might be.
>
> Sent from my iPhone
>
> > On Jan 19, 2015, at 6:12 AM, Spiros Ioannou <sivann@inaccess.com> wrote:
> >
> > Hello group,
> > we have a timeseries table, and we tried to partition it by month (with pg_partman). It seems the query planner
alwaysreads all tables regardless of WHERE, except when WHERE is equality. 
> >
> > the parent table:
> >
> > ifms_db=# \dS measurement_events
> >               Table "public.measurement_events"
> >         Column         |           Type           | Modifiers
> > -----------------------+--------------------------+-----------
> >  measurement_source_id | uuid                     | not null
> >  measurement_time      | timestamp with time zone | not null
> >  event_reception_time  | timestamp with time zone | not null
> >  measurement_value     | character varying(200)   | not null
> >  quality               | character varying(500)   | not null
> > Indexes:
> >     "measurement_events_pkey" PRIMARY KEY, btree (measurement_source_id, measurement_time)
> > Triggers:
> >     measurement_events_part_trig BEFORE INSERT ON measurement_events FOR EACH ROW EXECUTE PROCEDURE
measurement_events_part_trig_func()
> > Number of child tables: 25 (Use \d+ to list them.)
> >
> >
> > One of the children tables (2014_3)
> >
> > ifms_db=# \dS measurement_events_p2014_03
> >           Table "public.measurement_events_p2014_03"
> >         Column         |           Type           | Modifiers
> > -----------------------+--------------------------+-----------
> >  measurement_source_id | uuid                     | not null
> >  measurement_time      | timestamp with time zone | not null
> >  event_reception_time  | timestamp with time zone | not null
> >  measurement_value     | character varying(200)   | not null
> >  quality               | character varying(500)   | not null
> > Indexes:
> >     "measurement_events_p2014_03_pkey" PRIMARY KEY, btree (measurement_source_id, measurement_time)
> > Check constraints:
> >     "measurement_events_p2014_03_partition_check" CHECK (measurement_time >= '2014-03-01 00:00:00+02'::timestamp
withtime zone AND measurement_time < '2014-04-01 00:00:00+03'::timestamp with time zone) 
> > Inherits: measurement_events
> >
> >
> >
> > The query:
> > # explain analyze  select * from measurement_events where
measurement_source_id='df86917e-8df0-11e1-8f8f-525400e76ceb'AND measurement_time >= DATE '2015-01-01' limit 1; 
> >
> >
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> >  Limit  (cost=0.00..4.12 rows=1 width=87) (actual time=0.377..0.377 rows=1 loops=1)
> >    ->  Append  (cost=0.00..2696.08 rows=655 width=87) (actual time=0.376..0.376 rows=1 loops=1)
> >          ->  Seq Scan on measurement_events  (cost=0.00..0.00 rows=1 width=966) (actual time=0.001..0.001 rows=0
loops=1)
> >                Filter: ((measurement_time >= '2015-01-01'::date) AND (measurement_source_id =
'df86917e-8df0-11e1-8f8f-525400e76ceb'::uuid))
> >          ->  Index Scan using measurement_events_p2014_01_pkey on measurement_events_p2014_01  (cost=0.14..8.16
rows=1width=966) (actual time=0.005..0.005 rows=0 loops=1) 
> >                Index Cond: ((measurement_source_id = 'df86917e-8df0-11e1-8f8f-525400e76ceb'::uuid) AND
(measurement_time>= '2015-01-01'::date)) 
> >          ->  Index Scan using measurement_events_p2014_02_pkey on measurement_events_p2014_02  (cost=0.14..8.16
rows=1width=966) (actual time=0.002..0.002 rows=0 loops=1) 
> >                Index Cond: ((measurement_source_id = 'df86917e-8df0-11e1-8f8f-525400e76ceb'::uuid) AND
(measurement_time>= '2015-01-01'::date)) 
> >          ->  Index Scan using measurement_events_p2014_03_pkey on measurement_events_p2014_03  (cost=0.14..8.16
rows=1width=966) (actual time=0.002..0.002 rows=0 loops=1) 
> >                Index Cond: ((measurement_source_id = 'df86917e-8df0-11e1-8f8f-525400e76ceb'::uuid) AND
(measurement_time>= '2015-01-01'::date)) 
> >          ->  Index Scan using measurement_events_p2014_04_pkey on measurement_events_p2014_04  (cost=0.14..8.16
rows=1width=966) (actual time=0.001..0.001 rows=0 loops=1) 
> >                Index Cond: ((measurement_source_id = 'df86917e-8df0-11e1-8f8f-525400e76ceb'::uuid) AND
(measurement_time>= '2015-01-01'::date)) 
> >          ->  Index Scan using measurement_events_p2014_05_pkey on measurement_events_p2014_05  (cost=0.14..8.16
rows=1width=966) (actual time=0.001..0.001 rows=0 loops=1) 
> >                Index Cond: ((measurement_source_id = 'df86917e-8df0-11e1-8f8f-525400e76ceb'::uuid) AND
(measurement_time>= '2015-01-01'::date)) 
> >          ->  Index Scan using measurement_events_p2014_06_pkey on measurement_events_p2014_06  (cost=0.14..8.16
rows=1width=966) (actual time=0.002..0.002 rows=0 loops=1) 
> >                Index Cond: ((measurement_source_id = 'df86917e-8df0-11e1-8f8f-525400e76ceb'::uuid) AND
(measurement_time>= '2015-01-01'::date)) 
> >          ->  Index Scan using measurement_events_p2014_07_pkey on measurement_events_p2014_07  (cost=0.14..8.16
rows=1width=966) (actual time=0.001..0.001 rows=0 loops=1) 
> >                Index Cond: ((measurement_source_id = 'df86917e-8df0-11e1-8f8f-525400e76ceb'::uuid) AND
(measurement_time>= '2015-01-01'::date)) 
> >          ->  Index Scan using measurement_events_p2014_08_pkey on measurement_events_p2014_08  (cost=0.14..8.16
rows=1width=966) (actual time=0.002..0.002 rows=0 loops=1) 
> >                Index Cond: ((measurement_source_id = 'df86917e-8df0-11e1-8f8f-525400e76ceb'::uuid) AND
(measurement_time>= '2015-01-01'::date)) 
> >          ->  Index Scan using measurement_events_p2014_09_pkey on measurement_events_p2014_09  (cost=0.14..8.16
rows=1width=966) (actual time=0.001..0.001 rows=0 loops=1) 
> >                Index Cond: ((measurement_source_id = 'df86917e-8df0-11e1-8f8f-525400e76ceb'::uuid) AND
(measurement_time>= '2015-01-01'::date)) 
> >          ->  Index Scan using measurement_events_p2014_10_pkey on measurement_events_p2014_10  (cost=0.14..8.16
rows=1width=966) (actual time=0.001..0.001 rows=0 loops=1) 
> >                Index Cond: ((measurement_source_id = 'df86917e-8df0-11e1-8f8f-525400e76ceb'::uuid) AND
(measurement_time>= '2015-01-01'::date)) 
> >          ->  Index Scan using measurement_events_p2014_11_pkey on measurement_events_p2014_11  (cost=0.14..8.16
rows=1width=966) (actual time=0.002..0.002 rows=0 loops=1) 
> >                Index Cond: ((measurement_source_id = 'df86917e-8df0-11e1-8f8f-525400e76ceb'::uuid) AND
(measurement_time>= '2015-01-01'::date)) 
> >          ->  Index Scan using measurement_events_p2014_12_pkey on measurement_events_p2014_12  (cost=0.28..8.04
rows=1width=51) (actual time=0.009..0.009 rows=0 loops=1) 
> >                Index Cond: ((measurement_source_id = 'df86917e-8df0-11e1-8f8f-525400e76ceb'::uuid) AND
(measurement_time>= '2015-01-01'::date)) 
> >          ->  Bitmap Heap Scan on measurement_events_p2015_01  (cost=31.02..2500.30 rows=630 width=54) (actual
time=0.345..0.345rows=1 loops=1) 
> >                Recheck Cond: ((measurement_source_id = 'df86917e-8df0-11e1-8f8f-525400e76ceb'::uuid) AND
(measurement_time>= '2015-01-01'::date)) 
> >                ->  Bitmap Index Scan on measurement_events_p2015_01_pkey  (cost=0.00..30.87 rows=630 width=0)
(actualtime=0.269..0.269 rows=718 loops=1) 
> >                      Index Cond: ((measurement_source_id = 'df86917e-8df0-11e1-8f8f-525400e76ceb'::uuid) AND
(measurement_time>= '2015-01-01'::date)) 
> >          ->  Index Scan using measurement_events_p2015_02_pkey on measurement_events_p2015_02  (cost=0.14..8.16
rows=1width=966) (never executed) 
> >                Index Cond: ((measurement_source_id = 'df86917e-8df0-11e1-8f8f-525400e76ceb'::uuid) AND
(measurement_time>= '2015-01-01'::date)) 
> >          ->  Index Scan using measurement_events_p2015_03_pkey on measurement_events_p2015_03  (cost=0.14..8.16
rows=1width=966) (never executed) 
> > .....
> >
> > More results:
> >
> > This query:
> > ifms_db=# explain analyze  select * from measurement_events where
measurement_source_id='bd77387a-fdb4-4531-9bb7-7ef67a8f647d'AND measurement_time = DATE  '2015-01-14 15:30:01+02'
limit1; 
> >
> > searches in all tables:
> >
> > This query (no date casting):
> > ifms_db=# explain analyze  select * from measurement_events where
measurement_source_id='bd77387a-fdb4-4531-9bb7-7ef67a8f647d'AND measurement_time =  '2015-01-14 15:30:01+02'   limit 1; 
> >
> > searches only 1 table,
> >
> > and this query (>, no casting):
> > ifms_db=# explain analyze  select * from measurement_events where
measurement_source_id='bd77387a-fdb4-4531-9bb7-7ef67a8f647d'AND measurement_time >  '2015-01-14 15:30:01+02'   limit 1; 
> >
> > searches first the correct table, then all the others.
> >
> > any ideas?



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