Sample Header Ad - 728x90

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