Create index for WHERE COALESCE() condition
4
votes
1
answer
8210
views
I'm using PostgreSQL V9.6.11
Table 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);
I was trying to create an index
for the query below:
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-10 15:29:44.596311-07'
AND COALESCE(u_dttm,l_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
Why is the index scan not happening?
Asked by Rj_N
(436 rep)
Apr 12, 2020, 12:07 AM
Last activity: Apr 12, 2020, 09:57 AM
Last activity: Apr 12, 2020, 09:57 AM