Postgres JSONB 日期範圍搜尋

我有一個包含許多元素的 JSONB 對象，其中一個是日期。以下查詢現在非常慢，我的表中有數百萬行：

SELECT * FROM table WHERE obj->>'dob' BETWEEN '2019-01-01' AND '2020-01-01'.

如何提高此查詢的性能？

您可以在列上創建索引。然而，將字元串轉換為索引的日期並不簡單，因為 Postgres 不將轉換辨識為“不可變的”（儘管它是）。

通過一些解決方法，可以做到這一點：

create function dob(p_value jsonb)
returns date 
as
$$
 select (p_value ->> 'dob')::date;
$$
language sql
immutable;

然後你可以創建一個索引：

create index on the_table ( (dob(obj)) );

並使用 WHERE 子句中的函式：

SELECT * 
FROM the_table 
WHERE dob(obj) BETWEEN DATE '2019-01-01' AND DATE '2020-01-01'

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