為 WHERE COALESCE() 條件創建索引

我正在使用 PostgreSQL V9.6.11

DDL 表：

CREATE TABLE test_c ( 
         insrt_prcs_id bigint NOT NULL, 
         updt_prcs_id bigint, src_sys_id integer NOT NULL,
         load_dttm timestamp(6) with time zone NOT NULL, 
         updt_dttm timestamp(6) without time zone);

我試圖為index下面的查詢創建一個：

SELECT * 
FROM test_c             
WHERE COALESCE(u_dttm,l_dttm) > '2020-04-10 15:29:44.596311-07'
AND   COALESCE(u_dttm,l_dttm) <= '2020-04-11 15:29:44.596311-07' 

創建index為：

create index idx_test_c  on test_c(COALESCE((updt_dttm, load_dttm)))

但是查詢計劃器沒有掃描索引：

EXPLAIN ANALYZE
SELECT *            
FROM test_c             
WHERE COALESCE(u_dttm,l_dttm) > '2020-04-10 15:29:44.596311-07'
AND   COALESCE(u_dttm,l_dttm) <= '2020-04-11 15:29:44.596311-07' 

Seq Scan on test_c as test_c (cost=0..1857.08 rows=207 width=496) (actual=5.203..5.203 rows=0 loops=1)  
Filter: ((COALESCE((test_c.updt_dttm)::timestamp with time zone, test_c.load_dttm) > '2020-04-10 15:29:44.596311-07'::timestamp with time zone) AND (COALESCE((test_c.updt_dttm)::timestamp with time zone, test_c.load_dttm) <= '2020-04-11 15:29:44.596311-07'::timestamp with time zone))
Rows Removed by Filter: 41304

為什麼沒有發生索引掃描？

去掉不正確的括號對：

CREATE INDEX idx_test_c ON test_c(COALESCE(updt_dttm, load_dttm));

db<>在這裡擺弄

你擁有它的方式是有效地索引複合值 (updt_dttm, load_dttm)，COALESCE而不是做任何事情。

添加的表定義揭示了您的第二個問題：

CREATE TABLE test_c ( 
 insrt_prcs_id bigint NOT NULL
, updt_prcs_id  bigint
, src_sys_id    integer NOT NULL
, load_dttm     timestamp(6) with time zone NOT NULL
, updt_dttm     timestamp(6) without time zone   -- !!!
);

為什麼要為load_dttm和使用不同的數據類型updt_dttm？解決這個問題，第二個問題就消失了。我建議：

CREATE TABLE test_c ( 
 -- ...
, load_dttm     timestamp with time zone NOT NULL
, updt_dttm     timestamp with time zone  -- !!!
);

為什麼？

你得到這個錯誤：

錯誤：索引表達式中的函式必須標記為 IMMUTABLE

..因為COALESCE必須返回一種數據類型。timestamp with time zone( timestamtz) 是“首選類型”，因此updt_dttm timestamp被強制為timestamptz，它使用的函式取決於timezone目前會話的設置，因此不是 IMMUTABLE。並且索引表達式不能涉及非 IMMUTABLE 函式。

有關的：

• 當客戶端執行時區邏輯時，timestamptz 是首選嗎？
• 在 Rails 和 PostgreSQL 中完全忽略時區

您可以通過對時區進行硬編碼來使索引與原始（損壞的）表設計一起使用 -'Europe/Vienna'在我的範例中：

CREATE INDEX ON test_c(COALESCE(updt_dttm AT TIME ZONE 'Europe/Vienna', load_dttm));

只是一個概念證明。希望使用該索引的查詢必須使用相同的表達式。如果沒有必要，不要去那裡。改為修復您的表定義。

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