partitioning query planner almost always scans all tables - Mailing list pgsql-general
| From | Spiros Ioannou |
|---|---|
| Subject | partitioning query planner almost always scans all tables |
| Date | |
| Msg-id | CACKh8C_7pzVsM9=kKCTpnztp3DEu1AvUwoDADoymjE4=-43e=g@mail.gmail.com Whole thread Raw |
| Responses |
Re: partitioning query planner almost always scans all tables
|
| List | pgsql-general |
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?
pgsql-general by date: