如何处理由范围类型上的完全相等引起的错误查询计划？

我正在执行更新，我需要对tstzrange变量进行完全相等的处理。约100万行被修改，查询花费约13分钟。在这里可以看到EXPLAIN ANALYZE的结果，实际结果与查询计划者估计的结果有很大不同。问题在于t_range上的索引扫描期望返回一行。

这似乎与以下事实有关：范围类型的统计信息存储与其他类型的统计信息不同。查看列的pg_stats视图，n_distinct为-1，其他字段（例如most_common_vals，most_common_freqs）为空。

但是，必须将统计信息存储在t_range的某处。一个非常相似的更新，其中我在t_range上使用“内”而不是完全相等，需要大约4分钟的时间来执行，并且使用完全不同的查询计划（请参见此处）。第二个查询计划对我来说很有意义，因为将使用临时表中的每一行以及历史记录表的大部分。更重要的是，查询计划人员可以预测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的评论：

查询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;

查询计划和结果（请参见此处）介于前两个案例之间（约6分钟）。

2016/02/05 EDIT

不再有权访问数据1.5年后，我创建了具有相同结构（无索引）和相似基数的测试表。 jjanes的答案提出原因可能是用于更新的临时表的顺序。由于无法访问track_io_timing（使用Amazon RDS），因此无法直接检验该假设。

总体结果要快得多（好几倍）。我猜这是因为删除了索引，这与Erwin的答案一致。
在此测试案例中，查询1和2基本上花费了相同的时间，因为它们都使用了合并联接。也就是说，我无法触发导致Postgres选择哈希联接的任何原因，因此我不清楚为什么Postgres首先选择性能较差的哈希联接。

如果将相等条件（a = b）转换为两个“包含”条件怎么办：（a @> b AND b @> a）？计划会改变吗？

@ypercube：该计划发生了很大变化，尽管它仍然不是很理想-参见我的编辑＃2。

另一个想法是自从检查相等性后，在（lower（t_range），upper（t_range））上添加常规btree索引。

#1 楼

执行计划中时间上最大的不同在于UPDATE本身。这表明在更新期间，您大部分时间都将花费在IO上。您可以通过打开track_io_timing并使用EXPLAIN (ANALYZE, BUFFERS)运行查询来验证这一点。

不同的计划以不同的顺序显示要更新的行。一个以trip_id的顺序排列，另一个以它们实际出现在临时表中的顺序排列。

正在更新的表似乎具有与trip_id列相关的物理顺序，并且以此顺序更新行会导致具有预读/顺序读取的有效IO模式。虽然临时表的物理顺序似乎会导致很多随机读取。

如果您可以在创建临时表的语句中添加order by trip_id，则可能会为您解决问题。 br />
在计划UPDATE操作时，PostgreSQL不考虑IO排序的影响。（与SELECT操作不同，它确实考虑了它们）。如果PostgreSQL更聪明，它将意识到一个计划会产生更有效的顺序，或者会在更新及其子节点之间插入一个显式的排序节点，以便更新将按ctid顺序馈入行。

您是正确的，因为PostgreSQL在估计范围上相等联接的选择性方面做得不好。但是，这仅与您的基本问题相切。对更新的选择部分进行更有效的查询可能会偶然地使行以更好的顺序输入到正确的更新中，但是如果这样的话，这很大程度上取决于运气。

不幸的是，我无法修改track_io_timing，并且（因为已经一年半了！）我无法再访问原始数据。但是，我通过创建具有相同模式和相似大小（几百万行）的表并运行两个不同的更新来测试您的理论，其中一个更新是对temp更新表的排序与原始表相同，另一个对它进行了排序准随机的。不幸的是，两个更新花费的时间大致相同，这意味着更新表的顺序不会影响此查询。

– abeboparebop
16-2-5在18:40

#2 楼

我不太确定为什么tstzrange列上的GiST索引会如此高估过一个相等谓词的选择性。虽然这本身仍然很有趣，但这似乎与您的特定情况无关。

由于您的UPDATE修改了所有现有3M行的三分之一（！），因此索引根本没有帮助。相反，除了表外，增量更新索引也将增加UPDATE的成本。

只需保留简单的查询1。简单而彻底的解决方案是在索引之前删除索引。 UPDATE。如果您出于其他目的需要它，请在UPDATE之后重新创建它。这仍然比在较大的UPDATE期间维护索引要快。

对于所有行的三分之一上的UPDATE，可能还需要删除所有其他索引-并在之后重新创建它们UPDATE。唯一的缺点是：您需要其他特权和对表的独占锁定（仅当使用CREATE INDEX CONCURRENTLY时才短暂）。

@ypercube使用btree代替GiST索引的想法在主要。但是不是所有行的三分之一（没有索引是没有好处的），而且不是(lower(t_range),upper(t_range))，因为tstzrange不是离散范围类型。

大多数离散范围类型都有规范形式，这使“平等”的概念更简单：规范形式的值的上下限对其进行定义。文档：

离散范围类型应具有规范化功能，
知道元素类型所需的步长。
规范化函数负责将范围类型的等效值
转换为具有相同的表示形式，尤其是
一致的包含或排除范围。如果未指定规范化
函数，则格式不同的范围将
即使它们实际上可能表示
相同的一组值，也总是被视为不相等。

内置范围类型int4range，int8range和daterange都使用
包含下限但不包括上限的规范形式；即[)。但是，用户定义的范围类型可以使用其他
约定。

对于tstzrange则不是这种情况，其中需要考虑上下限的包容性以确保相等。可能的btree索引必须位于：

(lower(t_range), upper(t_range), lower_inc(t_range), upper_inc(t_range))

，并且查询必须在WHERE子句中使用相同的表达式。

可能会尝试仅索引转换为text的整个值：(cast(t_range AS text))-但此表达式不是IMMUTABLE，因为timestamptz值的文本表示形式取决于当前的timezone设置。您需要将其他步骤放到产生规范形式的IMMUTABLE包装函数中，并在该函数上创建功能索引...

其他措施/替代方案

如果对于多个更新的行，shape_dist_traveled可以具有与tt.shape_dist_traveled相同的值（并且您不依赖于UPDATE的任何副作用，例如触发器...），您可以通过排除空更新来加快查询速度：

WHERE ...
AND   shape_dist_traveled IS DISTINCT FROM tt.shape_dist_traveled;

更新具有相同值的行实际上会更新该行吗？

当然，所有有关性能的一般建议优化适用。 Postgres Wiki是一个很好的起点。

VACUUM FULL对您而言是有毒的，因为一些死元组（或FILLFACTOR保留的空间）对于UPDATE的性能是有益的。

那么多的更新行，并且如果您能负担得起（没有并发访问或其他依赖项），那么编写一个全新的表而不是就地更新可能会更快。相关答案中的说明：

在PostgreSQL中优化批量更新性能

编程黑洞网

如何处理由范围类型上的完全相等引起的错误查询计划？

评论

#1 楼

评论

#2 楼