Rj_N's questions

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

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

我试图了解哪种扫描有效，这是explain查询的内容，

EXPLAIN SELECT *
FROM 'myTable_v' --view
WHERE cast(TIMESTAMP AS VARCHAR(100)) > CAST(1586415955169 AS VARCHAR(100))
AND cast(TIMESTAMP AS VARCHAR(100)) <= CAST(1586416090443 AS VARCHAR(100))  

上面的视图创建如下，

CREATE VIEW 'myTable_v' AS
SELECT 
msg ->> 'customerType'::text AS customertype,
msg ->> 'trackingId'::text AS "trackingId",
msg ->> 'conversationId'::text AS "conversationId",
msg ->> 'authState'::text AS authstate,
msg ->> 'tmoId'::text AS id,
msg ->> 'msisdn'::text AS sdn,
msg ->> 'ban'::text AS ban,
FROM myTable -- main table
WHERE (msg ->> 'type'::text) = 'CTYPE'::text;

规划器输出是，没有index

"Seq Scan on 'myTable' (cost=0.00..472877.98 rows=7300 width=904)"
"  Filter: (((msg ->> 'type'::text) = 'EdsConversationData'::text) AND ((("timestamp")::character varying(100))::text > '1586415955169'::text) AND ((("timestamp")::character varying(100))::text <= '1586416090443'::text))"

在添加如下索引后，

CREATE INDEX idx_timestmp_n ON 'myTable' ( (msg ->> 'type'),((('timestamp')::character varying(100))))

查询规划器出来的是，

"Bitmap Heap Scan on 'myTable'  (cost=505.45..49649.59 rows=7300 width=904)"
"  Recheck Cond: ((msg ->> 'type'::text) = 'EdsConversationData'::text)"
"  Filter: (((("timestamp")::character varying(100))::text > '1586415955169'::text) AND ((("timestamp")::character varying(100))::text <= '1586416090443'::text))"
"  ->  Bitmap Index Scan on idx_timestmp_n  (cost=0.00..505.43 rows=14517 width=0)"
"        Index Cond: ((msg ->> 'type'::text) = 'EdsConversationData'::text)"

那么，这里哪一个是建议使用索引或不使用索引来执行查询的呢？

感谢您的回复。

谢谢