包含檢查 tstzrange @> timestamptz 不使用 btree 或 gist 索引

架構：

       Column        |           Type           
----------------------+--------------------------
id                   | integer                  
event_id             | integer                  
started_at           | timestamp with time zone 
ended_at             | timestamp with time zone 
created_at           | timestamp with time zone 
   "event_seat_state_lookup_pkey" PRIMARY KEY, btree (id)
   "event_seating_lookup_created_at_idx" btree (created_at)
   "event_seating_lookup_created_at_idx2" gist (created_at)

詢問：

SELECT id
FROM event_seating_lookup esl1
WHERE
 tstzrange(now() - interval '1 hour', now() + interval '1 hour', '[)') @> esl1.created_at;

解釋分析：

<100k 行的表。

Seq Scan on event_seating_lookup esl1  (cost=0.00..1550.30 rows=148 width=4) (actual time=0.013..19.956 rows=29103 loops=1)
  Filter: (tstzrange((now() - '01:00:00'::interval), (now() + '01:00:00'::interval), '[)'::text) @> created_at)
  Buffers: shared hit=809
Planning Time: 0.110 ms
Execution Time: 21.942 ms

具有 1M+ 行的表：

Seq Scan on event_seating_lookup esl1  (cost=10000000000.00..10000042152.75 rows=5832 width=4) (actual time=0.009..621.895 rows=1166413 loops=1)
 Filter: (tstzrange((now() - '01:00:00'::interval), (now() + '01:00:00'::interval), '[)'::text) @> created_at)
 Buffers: shared hit=12995
Planning Time: 0.092 ms
Execution Time: 697.927 ms

我試過了：

VACUUM FULL event_seating_lookup;
VACUUM event_seating_lookup;
VACUUM ANALYZE event_seating_lookup;
SET enable_seqscan = OFF;

問題：

event_seating_lookup_created_at_idx或未event_seating_lookup_created_at_idx2使用索引。

筆記：

• PostgreSQL 11.1。
• btree_gist擴展已安裝。
• created_at timestamp without time zone我已經嘗試過使用和使用的等效設置tsrange；同樣的結果。
• 我知道用>=,<檢查重寫查詢會使其使用 btree 索引。問題是索引不與tstzrange包含操作符一起使用的原因是什麼，以及是否有辦法使其工作。

問題是索引不與tstzrange包含操作符一起使用的原因是什麼，以及是否有辦法使其工作。

原因很微不足道。B-tree 索引不支持包含操作符@>。既不適用於範圍類型，tstzrange也不適用於任何其他類型（包括數組類型）。

手冊：

… btree 運算符類必須提供五個比較運算符，<，<=， 和。=``>=``>

並且 GiST 索引不支持<, , <=>和. 請參閱手冊的這些章節：=``>=``>

• GiST 索引的內置運算符類
• B-Tree 運算符類的行為

在 Postgres 中，索引綁定到運算符（為某些類型實現），而不是單獨的數據類型或函式或其他任何東西。有關的：

• 優化對一系列時間戳（兩列）的查詢
• PostgreSQL 可以索引數組列嗎？

event_seating_lookup_created_at_idx2您擁有的 GiST 索引毫無意義。它是在timestamptz列上創建的created_at。這樣的 GiST 索引對於範圍類型（邏輯的相反方向）很有用。

只有在您安裝了附加擴展以允許此類無用索引時，timestamptz才有可能在列上創建 GiST索引。btree_gist（多列索引或排除約束有一些有用的應用程序……）在股票 Postgres 你會得到一個錯誤：

錯誤：帶時區的數據類型時間戳沒有訪問方法“gist”的預設運算符類

因此，雖然使用 btree 索引（或 GiST 索引）進行查詢在邏輯上有效且技術上可行，但並未實現這種情況：不支持索引timestamptz <@ tstzrangetimestamptz（索引表達式在哪裡！）。它可以用<, <=, >,>=更有效地解決。所以我猜沒有開發人員覺得（或會覺得）需要實現它。

用有利的運算符重寫表達式的函式

您的實施對我來說很有意義。它可以滿足您的需求，並且由於函式內聯在時間戳列上使用了普通的 btree 索引 -event_seating_lookup_created_at_idx在您的範例中。對於具有恆定範圍的呼叫（例如在單個函式呼叫中），我建議使用此修改版本：

CREATE OR REPLACE FUNCTION in_range(timestamptz, tstzrange)
 RETURNS boolean AS
$func$
SELECT CASE WHEN lower_inc($2) THEN $1 >= lower($2) ELSE $1 > lower($2) END
  AND CASE WHEN upper_inc($2) THEN $1 <= upper($2) ELSE $1 < upper($2) END
$func$  LANGUAGE sql IMMUTABLE;

聲明它IMMUTABLE，因為它確實是。不是為了幫助函式內聯（如果聲明為假，甚至可以阻止它），而是為了其他收益。有關的：

• 聲明函式可變性 IMMUTABLE 會損害性能嗎？
• PostgreSQL 內聯函式行為

它可以內聯並像您的版本一樣使用索引。區別：這個抑制了排他邊界的冗餘索引條件。幸運的是，您在這方面的考慮有點偏離目標：

先匹配the $1 >= lower($2) AND $1 <= upper($2) 條件再檢查upper_inc和lower_inc 約束的原因是先從範圍掃描中受益，然後過濾結果。

Postgres 11（至少）比這更聰明。我看不到Filter您的版本的步驟。對於預設[)範圍（如您的範例），我得到了這個查詢計劃（我添加的條件中的換行符）：

 ->  Index Only Scan using foo_idx on foo (actual rows=5206 loops=1)
       Index Cond: ((datetime >= '2018-09-05 22:00:00+00'::timestamp with time zone)
                AND (datetime <= '2018-09-05 22:30:00+00'::timestamp with time zone)
                AND (datetime < '2018-09-05 22:30:00+00'::timestamp with time zone))

Filter對於在排除邊界上具有許多命中的極端情況，實際步驟可能會增加更大的成本。這些將從索引中獲取然後丟棄。與值通常以界限結束的時間範圍非常相關 - 例如整小時的時間戳。

實際的區別是看到是次要的，但為什麼不接受呢？

 ->  Index Only Scan using foo_idx on foo (actual rows=5206 loops=1)
       Index Cond: ((datetime >= '2018-09-05 22:00:00+00'::timestamp with time zone)
                AND (datetime < '2018-09-05 22:30:00+00'::timestamp with time zone))

就我的研究而言，postgresql無法將包含檢查重寫為可以使用 btree 索引匹配的表達式，即

esl1.created_at >= now() - interval '1 hour' AND
esl1.created_at < now() + interval '1 hour'

以這種方式編寫時，使用索引執行查詢：

Index Scan using event_seating_lookup_created_at_idx on event_seating_lookup esl1  (cost=0.44..12623.56 rows=18084 width=4) (actual time=0.013..57.084 rows=70149 loops=1)
 Index Cond: ((created_at >= (now() - '01:00:00'::interval)) AND (created_at < (now() + '01:00:00'::interval)))
Planning Time: 0.223 ms
Execution Time: 62.209 ms

由於我更喜歡包含查詢的語法而不是後一種形式，因此我研究了可能的替代方案。出現的是我可以編寫一個為我內聯條件的過程：

CREATE OR REPLACE FUNCTION in_range(timestamptz, tstzrange)
RETURNS boolean AS $$
SELECT
 (
   $1 >= lower($2) AND
   $1 <= upper($2) AND
   (
     upper_inc($2) OR
     $1 < upper($2)
   ) AND
   (
     lower_inc($2) OR
     $1 > lower($2)
   )
 )
$$
language sql;

先匹配$1 >= lower($2) AND $1 <= upper($2)條件再檢查upper_inc和lower_inc約束的原因是先從範圍掃描中受益，然後過濾結果。

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