postgresql – 如何处理由范围类型的完全相等导致的错误查询计划

我正在执行更新,我需要在tstzrange变量上完全相等.修改了~100行,查询大约需要13分钟. EXPLAIN ANALYZE的结果可以看到 here,实际结果与查询计划器估计的结果有很大不同.问题是t_range上的索引扫描需要返回一行.

这似乎与范围类型的统计信息与其他类型的统计信息存储不同有关.查看列的pg_stats视图,n_distinct为-1,其他字段(例如most_common_vals,most_common_freqs)为空.

但是,某些地方必须存储在t_range上的统计信息.一个非常相似的更新,我在t_range上使用’within’而不是完全相等需要大约4分钟来执行,并使用一个截然不同的查询计划(见here).第二个查询计划对我有意义,因为将使用临时表中的每一行和历史表的大部分.更重要的是,查询计划程序会在t_range上为过滤器预测大致正确的行数.

t_range的分布有点不寻常.我正在使用此表来存储另一个表的历史状态,并且对另一个表的更改会在大转储中同时发生,因此t_range的值不是很多.以下是与t_range的每个唯一值对应的计数：

t_range                              |  count  
-------------------------------------------------------------------+---------
 ["2014-06-12 20:58:21.447478+00","2014-06-27 07:00:00+00")        |  994676
 ["2014-06-12 20:58:21.447478+00","2014-08-01 01:22:14.621887+00") |   36791
 ["2014-06-27 07:00:00+00","2014-08-01 07:00:01+00")               | 1000403
 ["2014-06-27 07:00:00+00",infinity)                               |   36791
 ["2014-08-01 07:00:01+00",infinity)                               |  999753

上面的不同t_range的计数是完整的,因此基数是~3M(其中?1M将受到更新查询的影响).

为什么查询1的性能比查询2差得多？在我的例子中,查询2是一个很好的替代品,但如果真正需要确切的范围相等,我怎么能让Postgres使用更智能的查询计划？

带索引的表定义(删除不相关的列)：

Column        |   Type    |                                  Modifiers                                   
---------------------+-----------+------------------------------------------------------------------------------
 history_id          | integer   | not null default nextval('gtfs_stop_times_history_history_id_seq'::regclass)
 t_range             | tstzrange | not null
 trip_id             | text      | not null
 stop_sequence       | integer   | not null
 shape_dist_traveled | real      | 
Indexes:
    "gtfs_stop_times_history_pkey" PRIMARY KEY,btree (history_id)
    "gtfs_stop_times_history_t_range" gist (t_range)
    "gtfs_stop_times_history_trip_id" btree (trip_id)

查询1：

UPDATE gtfs_stop_times_history sth
SET shape_dist_traveled = tt.shape_dist_traveled
FROM gtfs_stop_times_temp tt
WHERE sth.trip_id = tt.trip_id
AND sth.stop_sequence = tt.stop_sequence
AND sth.t_range = '["2014-08-01 07:00:01+00",infinity)'::tstzrange;

查询2：

UPDATE gtfs_stop_times_history sth
SET shape_dist_traveled = tt.shape_dist_traveled
FROM gtfs_stop_times_temp tt
WHERE sth.trip_id = tt.trip_id
AND sth.stop_sequence = tt.stop_sequence
AND '2014-08-01 07:00:01+00'::timestamptz <@ sth.t_range;

Q1更新999753行和Q2更新999753 36791 = 1036544(即,临时表使得与时间范围条件匹配的每一行都被更新).

我尝试了这个查询以响应@ypercube’s comment：

查询3：

UPDATE gtfs_stop_times_history sth
SET shape_dist_traveled = tt.shape_dist_traveled
FROM gtfs_stop_times_temp tt
WHERE sth.trip_id = tt.trip_id
AND sth.stop_sequence = tt.stop_sequence
AND sth.t_range <@ '["2014-08-01 07:00:01+00",infinity)'::tstzrange
AND '["2014-08-01 07:00:01+00",infinity)'::tstzrange <@ sth.t_range;

查询计划和结果(见here)介于前两种情况(约6分钟)之间.

2016/02/05编辑

在1.5年后不再访问数据时,我创建了一个具有相同结构(没有索引)和类似基数的测试表. jjanes’s answer提出原因可能是用于更新的临时表的排序.我无法直接测试该假设,因为我无法访问track_io_timing(使用Amazon RDS).

>总体结果要快得多(几倍).我猜这是因为删除了索引,与Erwin’s answer一致.
>在此测试用例中,查询1和2基本上花费了相同的时间,因为它们都使用了合并连接.也就是说,我无法触发导致Postgres选择散列连接的任何内容,因此我不清楚为什么Postgres首先选择性能较差的散列连接.