Create index for WHERE COALESCE() condition

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-11 15:29:44.596311-07'

Create index as:

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

But the query planner is not scanning the index:

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

Why is the index scan not happening?

Strip the incorrect pair of parentheses:

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

db<>fiddle here

The way you had it was effectively indexing the composite value (updt_dttm, load_dttm), COALESCE not doing anything.

Your second problem was revealed by the added table definition:

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   -- !!!
);

Why would you use different data types for load_dttm and updt_dttm? Fix that and the second problem goes away. I suggest:

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

Why?

You got this error:


ERROR: functions in index expression must be marked IMMUTABLE

.. because COALESCE must return one data type. timestamp with time zone (timestamtz) is the "preferred type", so updt_dttm timestamp is coerced to timestamptz, which uses a function that depends on the timezone setting of the current session and hence is not IMMUTABLE. And index expressions cannot involve non-IMMUTABLE functions.

Related:

• Is timestamptz preferred when timezone logic is performed by the client?

• Ignoring time zones altogether in Rails and PostgreSQL

You could make the index work with your original (broken) table design by hard-coding the time zone - 'Europe/Vienna' in my example:

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

Just a proof of concept. Queries hoping to use that index would have to use the same expression. Don't go there if you don't have to. Fix your table definition instead.

StackExchange Database Administrators Q#264818, answer score: 6