如何處理範圍類型完全相等導致的錯誤查詢計劃？

我正在執行更新，我需要一個tstzrange變數完全相等。修改了約 1M 行，查詢需要約 13 分鐘。的結果EXPLAIN ANALYZE可以在這裡看到，實際結果與查詢計劃器估計的結果相差甚遠。問題是索引掃描t_range期望返回單行。

這似乎與範圍類型的統計資訊與其他類型的統計資訊儲存方式不同這一事實有關。pg_stats查看該列的視圖，為n_distinct-1，其他欄位（例如most_common_vals, most_common_freqs）為空。

但是，必須在t_range某處儲存統計資訊。我在 t_range 上使用 ‘within’ 而不是完全相等的非常相似的更新需要大約 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

上面distinct 的計數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)

查詢一：

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 編輯

1.5 年後不再可以訪問數據，我創建了一個具有相同結構（沒有索引）和相似基數的測試表。jjanes 的回答提出原因可能是用於更新的臨時表的順序。我無法直接測試假設，因為我無權訪問track_io_timing（使用 Amazon RDS）。

1. 總體結果要快得多（幾倍）。我猜這是因為刪除了索引，這與Erwin 的回答一致。
2. 在這個測試案例中，查詢 1 和 2 基本上花費了相同的時間，因為它們都使用了合併連接。也就是說，我無法觸發導致 Postgres 選擇散列連接的任何原因，所以我不清楚為什麼 Postgres 首先選擇性能不佳的散列連接。

執行計劃中最大的時間差異在頂部節點，即 UPDATE 本身。這表明你在更新期間大部分時間都花在了 IO 上。您可以通過打開track_io_timing並執行查詢來驗證這一點EXPLAIN (ANALYZE, BUFFERS)

不同的計劃以不同的順序呈現要更新的行。一個是按trip_id順序排列的，另一個是按它們在臨時表中物理出現的順序排列的。

正在更新的表的物理順序似乎與 trip_id 列相關，並且按此順序更新行會導致具有預讀/順序讀取的高效 IO 模式。而臨時表的物理順序似乎會導致大量隨機讀取。

如果您可以在order by trip_id創建臨時表的語句中添加一個，那可能會為您解決問題。

PostgreSQL 在規劃 UPDATE 操作時不考慮 IO 排序的影響。（與 SELECT 操作不同，它確實將它們考慮在內）。如果 PostgreSQL 更聰明，它要麼意識到一個計劃產生更有效的順序，要麼會在更新和它的子節點之間插入一個顯式排序節點，以便更新以 ctid 順序獲取行。

您是正確的，PostgreSQL 在估計範圍上相等連接的選擇性方面做得很差。但是，這僅與您的基本問題無關。對更新的選擇部分進行更有效的查詢可能會意外地以更好的順序將行饋送到 update-proper，但如果是這樣，這主要是運氣。

我不完全確定為什麼tstzrange列上的 GiST 索引如此徹底地高估了等式謂詞的選擇性。雖然這本身仍然很有趣，但它似乎與您的特定情況無關。

由於您UPDATE修改了所有現有 3M 行的三分之一（！），因此索引根本沒有幫助。相反，除了表之外，增量更新索引會增加大量的UPDATE.

只需保留您的簡單Query 1。簡單而激進的解決****方案是在. 如果您需要它用於其他目的，請在. 這仍然比在大型.UPDATE``UPDATE``UPDATE

對於UPDATE所有行的三分之一，刪除所有其他索引也可能會付出代價 - 並在UPDATE. 唯一的缺點：您需要額外的權限和表上的排他鎖（如果您使用 ，則只需要很短的時間CREATE INDEX CONCURRENTLY）。

@ypercube使用 btree 而不是 GiST 索引的想法在原則上似乎很好。但不是所有行的三分之一（沒有索引是任何好的開始），而不是just (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 中的批量更新性能

引用自：https://dba.stackexchange.com/questions/73361