Обсуждение: 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?
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
Sent from my iPhone
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_eventsTable "public.measurement_events"Column | Type | Modifiers-----------------------+--------------------------+-----------measurement_source_id | uuid | not nullmeasurement_time | timestamp with time zone | not nullevent_reception_time | timestamp with time zone | not nullmeasurement_value | character varying(200) | not nullquality | character varying(500) | not nullIndexes:"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_03Table "public.measurement_events_p2014_03"Column | Type | Modifiers-----------------------+--------------------------+-----------measurement_source_id | uuid | not nullmeasurement_time | timestamp with time zone | not nullevent_reception_time | timestamp with time zone | not nullmeasurement_value | character varying(200) | not nullquality | character varying(500) | not nullIndexes:"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_eventsThe 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
@Rob Sargent: sorry Rob, not sure what you are asking.
@Kyotaro HORIGUCHI
thanks for your reply and time Kyotaro,
thanks for your reply and time Kyotaro,
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?
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:
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
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)
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
> 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.
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.
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
Thank you for your input.
When doing an "ORDER BY measurement_time DESC" I had hoped for the query planner to firstly query the most recent "child" table, i.e. the table holding current month's data, and then move-on to the oldest table, since it knows the partition is based on measurement_time. Instead it always queries the oldest table first and then moves on to the current.
We did the partitioning to be able to drop old data saving disk space, as you said. Not for performance reasons, although we hoped than having smaller indexes would also help.
Best regards,
-Spiros
On 22 January 2015 at 17:37, Tom Lane <tgl@sss.pgh.pa.us> wrote:
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