Postgresql

如何使用索引進行簡單的時間範圍連接？

  • December 15, 2019

t_16我在Postgres 11.5中有一個大型（約 1 億行）時間序列表， 其中主鍵是abs_date_timetype的欄位timestamp。

這是這個問題的後續：

最初我認為它與 CTE 有關。但是這個查詢很慢，即使沒有 CTE。

如何使以下查詢使用主鍵索引，以避免全表掃描？

tsrange

此查詢在我的開發 PC 上需要大約 20 秒：

SELECT t_16_gen.*
FROM t_16_gen,
    (VALUES (tsrange('["2019-11-26 12:00:00","2019-11-26 12:00:15")'))
          , (tsrange('["2019-11-26 13:00:00","2019-11-26 13:00:15")'))) as ranges (time_range)
WHERE (abs_date_time >= LOWER(ranges.time_range)
   AND abs_date_time <  UPPER(ranges.time_range));

解釋計劃：

Gather  (cost=1000.00..6185287.15 rows=20571433 width=80)
 Workers Planned: 2
 ->  Nested Loop  (cost=0.00..4127143.85 rows=8571430 width=80)
       Join Filter: ((t_16_gen.abs_date_time >= lower("*VALUES*".column1)) AND (t_16_gen.abs_date_time < upper("*VALUES*".column1)))
       ->  Parallel Seq Scan on t_16_gen  (cost=0.00..1620000.38 rows=38571438 width=80)
       ->  Values Scan on "*VALUES*"  (cost=0.00..0.03 rows=2 width=32)

在生產中，一組 tsranges 來自 UDF - 但總是只有幾個範圍 (<200)，每個範圍的行數少於 1500 行，並且範圍不會重疊。

簡單的時間戳而不是tsrange

當我們直接使用時間戳（即不使用 tsrangeLOWER()和UPPER()）時，查詢已經更快了。此查詢在我的開發 PC 上需要約 7 秒：

SELECT t_16_gen.*
FROM t_16_gen,
    (VALUES ('2019-11-26 12:00:00'::timestamp,'2019-11-26 12:00:15'::timestamp)
          , ('2019-11-26 13:00:00','2019-11-26 13:00:15')) as ranges (start_incl, end_excl)
WHERE (abs_date_time &gt;= ranges.start_incl
   AND abs_date_time &lt;  ranges.end_excl);

解釋計劃：

Nested Loop  (cost=0.00..5400001.28 rows=20571433 width=80)
 Join Filter: ((t_16_gen.abs_date_time &gt;= "*VALUES*".column1) AND (t_16_gen.abs_date_time &lt; "*VALUES*".column2))
 -&gt;  Seq Scan on t_16_gen  (cost=0.00..2160000.50 rows=92571450 width=80)
 -&gt;  Materialize  (cost=0.00..0.04 rows=2 width=16)
       -&gt;  Values Scan on "*VALUES*"  (cost=0.00..0.03 rows=2 width=16)

OR條件 = 快速

當我重寫查詢以使用OR條件時，它很快。此查詢在我的開發 PC 上 需要大約 200 毫秒：

SELECT t_16_gen.*
FROM t_16_gen
WHERE (abs_date_time &gt;= '2019-11-26 12:00:00' AND abs_date_time &lt; '2019-11-26 12:00:15')
  OR (abs_date_time &gt;= '2019-11-26 13:00:00' AND abs_date_time &lt; '2019-11-26 13:00:15');

解釋計劃：

Gather  (cost=13326.98..1533350.92 rows=923400 width=80)
 Workers Planned: 2
 -&gt;  Parallel Bitmap Heap Scan on t_16_gen  (cost=12326.98..1440010.92 rows=384750 width=80)
       Recheck Cond: (((abs_date_time &gt;= '2019-11-26 12:00:00'::timestamp without time zone) AND (abs_date_time &lt; '2019-11-26 12:00:15'::timestamp without time zone)) OR ((abs_date_time &gt;= '2019-11-26 13:00:00'::timestamp without time zone) AND (abs_date_time &lt; '2019-11-26 13:00:15'::timestamp without time zone)))
       -&gt;  BitmapOr  (cost=12326.98..12326.98 rows=925714 width=0)
             -&gt;  Bitmap Index Scan on t_16_pkey  (cost=0.00..5932.64 rows=462857 width=0)
                   Index Cond: ((abs_date_time &gt;= '2019-11-26 12:00:00'::timestamp without time zone) AND (abs_date_time &lt; '2019-11-26 12:00:15'::timestamp without time zone))
             -&gt;  Bitmap Index Scan on t_16_pkey  (cost=0.00..5932.64 rows=462857 width=0)
                   Index Cond: ((abs_date_time &gt;= '2019-11-26 13:00:00'::timestamp without time zone) AND (abs_date_time &lt; '2019-11-26 13:00:15'::timestamp without time zone))

UNION= 快

當我重寫查詢以使用UNION條件時，它也很快。這個查詢在我的開發 PC 上 需要大約 220 毫秒：

SELECT t_16_gen.*
FROM t_16_gen
WHERE (abs_date_time &gt;= '2019-11-26 12:00:00' AND abs_date_time &lt; '2019-11-26 12:00:15')
UNION
SELECT t_16_gen.*
FROM t_16_gen
WHERE (abs_date_time &gt;= '2019-11-26 13:00:00' AND abs_date_time &lt; '2019-11-26 13:00:15');

解釋計劃：

Unique  (cost=1032439.64..1069468.20 rows=925714 width=80)
 -&gt;  Sort  (cost=1032439.64..1034753.93 rows=925714 width=80)
"        Sort Key: t_16_gen.abs_date_time, t_16_gen.c_422, t_16_gen.c_423, t_16_gen.c_424, t_16_gen.c_425, t_16_gen.c_426, t_16_gen.c_427, t_16_gen.c_428, t_16_gen.c_429, t_16_gen.c_430, t_16_gen.c_431, t_16_gen.c_432, t_16_gen.c_433, t_16_gen.c_434, t_16_gen.c_435"
       -&gt;  Append  (cost=0.57..892513.13 rows=925714 width=80)
             -&gt;  Index Scan using t_16_pkey on t_16_gen  (cost=0.57..439313.71 rows=462857 width=80)
                   Index Cond: ((abs_date_time &gt;= '2019-11-26 12:00:00'::timestamp without time zone) AND (abs_date_time &lt; '2019-11-26 12:00:15'::timestamp without time zone))
             -&gt;  Index Scan using t_16_pkey on t_16_gen t_16_gen_1  (cost=0.57..439313.71 rows=462857 width=80)
                   Index Cond: ((abs_date_time &gt;= '2019-11-26 13:00:00'::timestamp without time zone) AND (abs_date_time &lt; '2019-11-26 13:00:15'::timestamp without time zone))

重現問題

為了重現這個問題，我可以創建一個新表並用虛擬數據填充它。然後每次測試前重啟數據庫，使數據不被記憶體。

注意：插入查詢可能會執行幾分鐘！

create table if not exists t_16_gen (
   abs_date_time timestamp constraint t_16_pkey primary key,
   c_422 bigint,
   c_423 bigint,
   c_424 real,
   c_425 real,
   c_426 real,
   c_427 real,
   c_428 real,
   c_429 real,
   c_430 bigint,
   c_431 real,
   c_432 real,
   c_433 real,
   c_434 bigint,
   c_435 real
);

INSERT INTO t_16_gen
SELECT ts, 1,2,3,4,5,6,7,8,9,10,11,12,13,14
FROM (SELECT generate_series('2019-11-26'::timestamp, '2019-11-27', '1 millisecond') as ts) as gs;

您的最後一個（快速）查詢有兩個相同WHERE的條件，Postgres 能夠辨識並折疊為one。因此，只有一個索引條件的更簡單的計劃。

在多種不同條件下，它變得更加昂貴。但是 Postgres 仍然根據對實際輸入值的估計繼續執行。WHERE嘗試在包含大部分或全部表的子句中使用一個或多個大間隔，您將看到順序掃描。

對於基於VALUES表達式的前兩個查詢，原則上是不同的。在那裡，Postgres 分叉了兩種情況：

  • 對於一個輸入行，Postgres 查看實際值並生成與您的第三個查詢相同的計劃，其中包含單個WHERE條件，並根據實際輸入值進行估計。你得到相應的索引/點陣圖索引/順序掃描。
  • 對於多個輸入行，Postgres 停止查看單個值，並根據一般估計和實際輸入行數準備查詢計劃。您可以提供一個VALUES表達式，其中包含 5 行導致根本沒有結果或 5 行返回整個表，這將是相同的查詢計劃。

在 Postgres 11 中測試。

另請注意，加入集合（VALUES表達式）在邏輯上與添加多個OR‘ed 範圍謂詞不同。匹配集合中多個時間範圍的行被多次返回，而第二種形式只返回一個實例，即使它匹配多個謂詞。

因此，具有 many 的第二種形式OR自然有利於點陣圖索引掃描，它會自動將多個命中合併為一個。Postgres 不知道您的條件永遠不會重疊。（或者他們會嗎？？那麼你有一個更大的問題。）如果你的表中的數據是按時間物理排序的（匹配你的 PK 列abs_date_time），那麼這應該仍然對你有利。

但是由於您的行相當寬（每頁元組較少），並且如果您有很多時間範圍（最多 200 個？），那麼過度支持點陣圖索引掃描可能是一個劣勢，而簡單的索引掃描可能會更快。

解決方案

UNION ALL應該更適合你！

SELECT * FROM s_28.t_16 WHERE abs_date_time &gt;= '2019-11-26 12:00:00' AND abs_date_time &lt; '2019-11-26 12:10:00'
UNION ALL
SELECT * FROM s_28.t_16 WHERE abs_date_time &gt;= '2019-11-26 13:00:00' AND abs_date_time &lt; '2019-11-26 13:10:00'
-- add (many) more
;

首先，它是這里工作邏輯的最佳匹配。未來版本的 Postgres 將繼續使用良好的查詢計劃的機會要大得多。

這樣，Postgres 使用基於每個實際輸入的估計值SELECT- 並且考慮到您的規格（所有範圍都很小），只要您的表統計資訊不完全誤導，查詢就永遠不會降級為順序掃描。

並且索引掃描不再是點陣圖索引掃描的（不公平的）劣勢。

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

相關問答

Postgresql

臨時表上的索引使用情況

  • March 12, 2021
檢視問答
Postgresql

指定 JOIN 和 LIMIT 時 SELECT 非常慢，但找不到/很少找到結果

  • February 18, 2019
檢視問答
Postgresql

Postgres繞過帶有100多個值的IN子句的索引？

  • July 26, 2018
檢視問答
Postgresql

使用大 IN 優化 Postgres 查詢

  • March 28, 2022
檢視問答
Postgresql

大表中的慢速索引掃描

  • March 25, 2022
檢視問答
Postgresql

使用 GIN 索引位串

  • March 22, 2022
檢視問答