postgresql - 查询以有效过滤具有相关数据的分区表中的数据

Question

我有一个表analytics_vehicle，在时间戳字段上按月分区，即。analytics_vehicle_2018_04.

我正在编写一个物化视图，我想物化我根据相关表获得的最近一年的数据。显然我只想用范围内的数据打表。

为了测试它，我硬编码了一个日期，我可以看到它只扫描正确的表。

select start_online_datetime 
    FROM core_dataavailability 
    LIMIT 1

返回2017-09-07 01:55:00+00

explain (analyze, buffers, format text)
SELECT * FROM 
analytics_vehicle
WHERE timestamp >= '2017-09-07 01:55:00+00'::timestamptz

只命中范围内的表。

Append  (cost=0.00..311.82 rows=9419 width=88) (actual time=0.008..2.979 rows=9418 loops=1)
  Buffers: shared hit=147
  ->  Seq Scan on analytics_vehicle  (cost=0.00..0.00 rows=1 width=154) (actual time=0.003..0.003 rows=0 loops=1)
        Filter: ("timestamp" >= '2017-09-07 01:55:00+00'::timestamp with time zone)
  ->  Seq Scan on analytics_vehicle_y2017m09  (cost=0.00..52.31 rows=1865 width=88) (actual time=0.005..0.339 rows=1865 loops=1)
        Filter: ("timestamp" >= '2017-09-07 01:55:00+00'::timestamp with time zone)
        Buffers: shared hit=29
  ->  Seq Scan on analytics_vehicle_y2018m03  (cost=0.00..86.46 rows=3077 width=88) (actual time=0.007..0.642 rows=3077 loops=1)
        Filter: ("timestamp" >= '2017-09-07 01:55:00+00'::timestamp with time zone)
        Buffers: shared hit=48
  ->  Seq Scan on analytics_vehicle_y2018m04  (cost=0.00..28.59 rows=1007 width=88) (actual time=0.004..0.200 rows=1007 loops=1)
        Filter: ("timestamp" >= '2017-09-07 01:55:00+00'::timestamp with time zone)
        Buffers: shared hit=16
  ->  Seq Scan on analytics_vehicle_y2018m08  (cost=0.00..76.10 rows=2728 width=88) (actual time=0.004..0.585 rows=2728 loops=1)
        Filter: ("timestamp" >= '2017-09-07 01:55:00+00'::timestamp with time zone)
        Buffers: shared hit=42
  ->  Seq Scan on analytics_vehicle_y2018m09  (cost=0.00..21.26 rows=741 width=88) (actual time=0.006..0.148 rows=741 loops=1)
        Filter: ("timestamp" >= '2017-09-07 01:55:00+00'::timestamp with time zone)
        Buffers: shared hit=12
Planning Time: 1.664 ms
Execution Time: 3.587 ms

explain (analyze, buffers, format text) 
SELECT * FROM 
analytics_vehicle
WHERE timestamp >= (
    select start_online_datetime 
    FROM core_dataavailability 
    LIMIT 1);

导致击中所有表格，例如。

Append  (cost=0.02..1184.90 rows=13915 width=89) (actual time=0.079..7.552 rows=9418 loops=1)
  Buffers: shared hit=645 read=2
  InitPlan 1 (returns $0)
    ->  Limit  (cost=0.00..0.02 rows=1 width=8) (actual time=0.019..0.020 rows=1 loops=1)
          Buffers: shared hit=1
          ->  Seq Scan on core_dataavailability  (cost=0.00..1.90 rows=90 width=8) (actual time=0.018..0.018 rows=1 loops=1)
                Buffers: shared hit=1
  ->  Seq Scan on analytics_vehicle  (cost=0.00..0.00 rows=1 width=154) (actual time=0.004..0.004 rows=0 loops=1)
        Filter: ("timestamp" >= $0)
  ->  Index Scan using analytics_vehicle_y2017m09_timestamp_idx on analytics_vehicle_y2017m09  (cost=0.28..37.15 rows=622 width=88) (actual time=0.073..0.969 rows=1865 loops=1)
        Index Cond: ("timestamp" >= $0)
        Buffers: shared hit=286 read=2
  ->  Bitmap Heap Scan on analytics_vehicle_y2015m03  (cost=59.70..207.03 rows=2506 width=88) (actual time=0.026..0.027 rows=0 loops=1)
        Recheck Cond: ("timestamp" >= $0)
        Buffers: shared hit=2
        ->  Bitmap Index Scan on analytics_vehicle_y2015m03_timestamp_idx  (cost=0.00..59.08 rows=2506 width=0) (actual time=0.025..0.025 rows=0 loops=1)
              Index Cond: ("timestamp" >= $0)
              Buffers: shared hit=2
  ->  Seq Scan on analytics_vehicle_y2015m08  (cost=0.00..12.41 rows=144 width=88) (actual time=0.102..0.102 rows=0 loops=1)
        Filter: ("timestamp" >= $0)
        Rows Removed by Filter: 433
        Buffers: shared hit=7
  ->  Index Scan using analytics_vehicle_y2015m09_timestamp_idx on analytics_vehicle_y2015m09  (cost=0.28..21.80 rows=316 width=88) (actual time=0.014..0.014 rows=0 loops=1)
        Index Cond: ("timestamp" >= $0)
        Buffers: shared hit=2
  ->  Seq Scan on analytics_vehicle_y2016m01  (cost=0.00..6.50 rows=67 width=88) (actual time=0.046..0.046 rows=0 loops=1)
        Filter: ("timestamp" >= $0)
        Rows Removed by Filter: 200
        Buffers: shared hit=4
  ->  Seq Scan on analytics_vehicle_y2014m02  (cost=0.00..6.50 rows=67 width=88) (actual time=0.056..0.056 rows=0 loops=1)
        Filter: ("timestamp" >= $0)
        Rows Removed by Filter: 200
        Buffers: shared hit=4
  ->  Seq Scan on analytics_vehicle_y2016m02  (cost=0.00..59.16 rows=698 width=88) (actual time=0.355..0.355 rows=0 loops=1)
        Filter: ("timestamp" >= $0)
        Rows Removed by Filter: 2093
        Buffers: shared hit=33
  ->  Seq Scan on analytics_vehicle_y2018m03  (cost=0.00..86.46 rows=1026 width=88) (actual time=0.005..0.947 rows=3077 loops=1)
        Filter: ("timestamp" >= $0)
        Buffers: shared hit=48
  ->  Seq Scan on analytics_vehicle_y2018m04  (cost=0.00..28.59 rows=336 width=88) (actual time=0.006..0.292 rows=1007 loops=1)
        Filter: ("timestamp" >= $0)
        Buffers: shared hit=16
  ->  Seq Scan on analytics_vehicle_y2014m04  (cost=0.00..6.50 rows=67 width=88) (actual time=0.037..0.037 rows=0 loops=1)
        Filter: ("timestamp" >= $0)
        Rows Removed by Filter: 200
        Buffers: shared hit=4
  ->  Bitmap Heap Scan on analytics_vehicle_y2015m02  (cost=36.02..124.96 rows=1515 width=88) (actual time=0.013..0.013 rows=0 loops=1)
        Recheck Cond: ("timestamp" >= $0)
        Buffers: shared hit=2
        ->  Bitmap Index Scan on analytics_vehicle_y2015m02_timestamp_idx  (cost=0.00..35.64 rows=1515 width=0) (actual time=0.012..0.012 rows=0 loops=1)
              Index Cond: ("timestamp" >= $0)
              Buffers: shared hit=2
  ->  Seq Scan on analytics_vehicle_y2014m06  (cost=0.00..66.40 rows=784 width=88) (actual time=0.428..0.428 rows=0 loops=1)
        Filter: ("timestamp" >= $0)
        Rows Removed by Filter: 2352
        Buffers: shared hit=37
  ->  Seq Scan on analytics_vehicle_y2014m07  (cost=0.00..13.99 rows=160 width=88) (actual time=0.074..0.074 rows=0 loops=1)
        Filter: ("timestamp" >= $0)
        Rows Removed by Filter: 479
        Buffers: shared hit=8
  ->  Index Scan using analytics_vehicle_y2014m11_timestamp_idx on analytics_vehicle_y2014m11  (cost=0.28..27.73 rows=483 width=88) (actual time=0.010..0.010 rows=0 loops=1)
        Index Cond: ("timestamp" >= $0)
        Buffers: shared hit=2
  ->  Seq Scan on analytics_vehicle_y2014m12  (cost=0.00..15.50 rows=147 width=154) (actual time=0.011..0.011 rows=0 loops=1)
        Filter: ("timestamp" >= $0)
        Rows Removed by Filter: 25
        Buffers: shared hit=1
  ->  Seq Scan on analytics_vehicle_y2014m03  (cost=0.00..39.51 rows=467 width=88) (actual time=0.289..0.289 rows=0 loops=1)
        Filter: ("timestamp" >= $0)
        Rows Removed by Filter: 1401
        Buffers: shared hit=22
  ->  Index Scan using analytics_vehicle_y2000m03_timestamp_idx on analytics_vehicle_y2000m03  (cost=0.28..38.21 rows=682 width=88) (actual time=0.012..0.012 rows=0 loops=1)
        Index Cond: ("timestamp" >= $0)
        Buffers: shared hit=2
  ->  Seq Scan on analytics_vehicle_y2016m05  (cost=0.00..15.69 rows=178 width=88) (actual time=0.097..0.097 rows=0 loops=1)
        Filter: ("timestamp" >= $0)
        Rows Removed by Filter: 535
        Buffers: shared hit=9
  ->  Seq Scan on analytics_vehicle_y2016m06  (cost=0.00..13.86 rows=156 width=88) (actual time=0.099..0.099 rows=0 loops=1)
        Filter: ("timestamp" >= $0)
        Rows Removed by Filter: 469
        Buffers: shared hit=8
  ->  Seq Scan on analytics_vehicle_y2000m11  (cost=0.00..54.00 rows=640 width=88) (actual time=0.391..0.391 rows=0 loops=1)
        Filter: ("timestamp" >= $0)
        Rows Removed by Filter: 1920
        Buffers: shared hit=30
  ->  Seq Scan on analytics_vehicle_y2000m12  (cost=0.00..80.93 rows=958 width=88) (actual time=0.566..0.566 rows=0 loops=1)
        Filter: ("timestamp" >= $0)
        Rows Removed by Filter: 2874
        Buffers: shared hit=45
  ->  Seq Scan on analytics_vehicle_y2018m08  (cost=0.00..76.10 rows=909 width=88) (actual time=0.006..0.769 rows=2728 loops=1)
        Filter: ("timestamp" >= $0)
        Buffers: shared hit=42
  ->  Seq Scan on analytics_vehicle_y2018m09  (cost=0.00..21.26 rows=247 width=88) (actual time=0.005..0.187 rows=741 loops=1)
        Filter: ("timestamp" >= $0)
        Buffers: shared hit=12
  ->  Index Scan using analytics_vehicle_y2016m11_timestamp_idx on analytics_vehicle_y2016m11  (cost=0.28..25.21 rows=396 width=88) (actual time=0.013..0.013 rows=0 loops=1)
        Index Cond: ("timestamp" >= $0)
        Buffers: shared hit=2
  ->  Seq Scan on analytics_vehicle_y2014m08  (cost=0.00..13.70 rows=152 width=88) (actual time=0.079..0.079 rows=0 loops=1)
        Filter: ("timestamp" >= $0)
        Rows Removed by Filter: 456
        Buffers: shared hit=8
  ->  Seq Scan on analytics_vehicle_y2014m09  (cost=0.00..16.15 rows=191 width=88) (actual time=0.102..0.102 rows=0 loops=1)
        Filter: ("timestamp" >= $0)
        Rows Removed by Filter: 572
        Buffers: shared hit=9
Planning Time: 29.613 ms
Execution Time: 8.595 ms

如果查询计划器没有直接获得值，则查询计划器将恢复为命中所有分区表。

下面的帖子突出了我的问题，但没有提供任何解决方案。有没有办法实现我想要做的事情，或者我是否试图在方孔中安装一个圆钉？

https://dba.stackexchange.com/questions/171057/postgresql-partitioned-table-timestamptz-constraint-problem

干杯

标签： postgresqltime-seriesdatabase-partitioning