主页 / dba / 问题 / 264818
Accepted
Rj_N
Asked: 2020-04-12 16:07:04 +0800 CST

为 WHERE COALESCE() 条件创建索引

  • 772

我正在使用 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

为什么没有发生索引扫描？

postgresql index

1 个回答

  1. Best Answer

    去掉不正确的括号对：

    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 函数。

    有关的：

    您可以通过对时区进行硬编码来使索引与原始（损坏的）表设计一起使用 -'Europe/Vienna'在我的示例中：

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

    只是一个概念证明。希望使用该索引的查询必须使用相同的表达式。如果没有必要，不要去那里。改为修复您的表定义。

    • 4

相关问题