Re: partitioning query planner almost always scans all tables - Mailing list pgsql-general

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