TLDR：我可以創建一個由以下WHERE子句使用的索引：

WHERE foo_date <@ tsrange('2018-01-01', '2018-02-01')

---

假設我有一張這樣的桌子：

創建表 foo
(
foo_id INTEGER 預設為 IDENTITY 生成，
沒有時區的 foo_date 時間戳 NOT NULL，
約束 foo_pkey 主鍵 (foo_id)
);

此表包含 100,000 條記錄，日期從2009-01-01到2018-12-29。我希望能夠查詢給定日期範圍內的行（例如 2018 年 1 月的行）。

選項1

一種方法是使用BETWEEN運算符：

SELECT * FROM foo WHERE foo_date BETWEEN '2018-01-01' AND '2018-01-31';

這種方法的問題是，如果foo_date發生在2018-01-31午夜之後，它們將不會包含在此查詢中。所以我可以將查詢更改為BETWEEN '2018-01-01' AND '2018-02-01'. 然而，問題是發生在 上的記錄2018-02-01 00:00:00。這些將包括在內，這是我不想要的。

選項 2

Aaron Bertrand提出的另一種選擇是使用以下構造：

foo_date >= '2018-01-01' AND foo_date < '2018-02-01'

（是的，此部落格適用於 SQL Server，但似乎適用於此處）。

雖然這種形式明確地給出了我想要的結果，但它很麻煩：我必須重複列名兩次。

選項 3

由於 Postgres 為我們提供了範圍數據類型，我認為更清晰的形式可能是：

foo_date <@ tsrange('2018-01-01', '2018-02-01') 

那麼我的下一個問題是，如果我使用這種形式，我可以使用索引來加速操作嗎？

---

使用上面的選項 1 和 2，可以使用普通的 b-tree 索引：

CREATE INDEX idx_foo ON foo(foo_date);

使用選項 1 或 2 的查詢將使用索引：

EXPLAIN SELECT * FROM foo 
WHERE 
   foo_date >= '2018-01-01' 
   AND foo_date < '2018-02-01';

給我這個查詢計劃：

Bitmap Heap Scan on foo  (cost=21.95..592.70 rows=942 width=12)
 Recheck Cond: ((foo_date >= '2018-01-01 00:00:00'::timestamp without time zone) AND (foo_date < '2018-02-01 00:00:00'::timestamp without time zone))
 ->  Bitmap Index Scan on idx_foo  (cost=0.00..21.71 rows=942 width=0)
       Index Cond: ((foo_date >= '2018-01-01 00:00:00'::timestamp without time zone) AND (foo_date < '2018-02-01 00:00:00'::timestamp without time zone))

但是，如果我使用選項 3，則不使用索引：

EXPLAIN SELECT * FROM foo 
WHERE foo_date <@ tsrange('2018-01-01', '2018-02-01');

給我：

Seq Scan on foo  (cost=0.00..1791.00 rows=500 width=12)
 Filter: (foo_date <@ '["2018-01-01 00:00:00","2018-02-01 00:00:00")'::tsrange)

如果我嘗試創建一個 gist 索引，我最初會收到一條錯誤消息。

ERROR:  data type timestamp without time zone has no default operator class for access method "gist"
HINT:  You must specify an operator class for the index or define a default operator class for the data type.
SQL state: 42704 

添加btree_gist擴展後，我可以創建索引：

CREATE INDEX idx_foo ON foo USING gist (foo_date)

但是，使用@>or<@仍然不會使用索引。

有什麼我想念的嗎？或者創建一個可以被這個構造使用的索引是不可行的？

1. 名稱*“foo_date”表示一個列date，並且是一個不好的選擇timestamp。選項 1適用於實際日期*。
2. 結合選項 2的普通 btree 索引 無疑是最佳解決方案。不要再看了。除了具有物理排序數據的大型表的 BRIN 索引的特殊情況。看：

• 加快 Postgres 部分索引的創建

概念證明

也就是說，要使 GiST 或 SP-GiST 索引起作用，您可以在假範圍上創建表達式索引。你不需要這個模組btree_gist。使用 SP-GiST 進行展示，因為這里通常會更快一些。看：

• 在 PostgreSQL 中執行此營業時間查詢

CREATE INDEX foo_date_spgist_idx ON foo USING spgist(tsrange(foo_date, foo_date, '[]'));

SELECT * FROM foo
WHERE  tsrange(foo_date, foo_date, '[]') <@ tsrange('2018-01-01', '2018-02-01')

或使用範圍文字：

...
WHERE  tsrange(foo_date, foo_date, '[]') <@ '[2018-01-01,2018-02-01)'

**但是：**更大，維護成本更高，比 btree 索引慢。寫起來更麻煩。對你的情況毫無意義。

除了：從技術上講，您可以：

... WHERE foo_date BETWEEN '2018-01-01' AND '2018-01-31 23:59.999999';

Postgres 時間戳類型（目前）以微秒解析度實現，即最大。6 位小數。因此，表達式完全符合您的要求。但我強烈建議不要在此實現細節上進行建構。選項2是要走的路。有關的：

• PostgreSQL 中的間隔（天），有兩個參數
• 在 Rails 和 PostgreSQL 中完全忽略時區

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