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 <siv...@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 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? > > >
