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' AND measurement_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@xxxxxxxxx> wrote in <31FED87E-D31B-4CF9-93F8-CC0F131CB6DF@xxxxxxxxx>
> 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@xxxxxxxxxxxx> wrote:
> >
> > 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?
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
