Search Postgresql Archives

partitioning query planner almost always scans all tables

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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?




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux