Postgresql not using created functional index

Question

For running the query,

SELECT count(*) FROM reservations WHERE 
(((json #>> '{details, attributes, checkIn}')::timestamptz at time zone (json #>> '{details, attributes, destinationTimeZone}'))) >= '2019-01-17' AND (((json #>> '{details, attributes, checkIn}')::timestamptz at time zone (json #>> '{details, attributes, destinationTimeZone}'))) < '2020-04-01';

I created functional Index:

CREATE FUNCTION text2tstz(text) RETURNS timestamp with time zone
   LANGUAGE sql IMMUTABLE AS
$$SELECT CASE WHEN $1 ~ '^\d{4}-\d{2}-\d{2}T\d{2}:\d{2}:\d{2}Z?$'
            THEN CAST($1 AS timestamp with time zone)
       END$$;


 CREATE INDEX CONCURRENTLY checkIn_index ON reservations 
((text2tstz(json ->> '{details,attributes,checkIn}')at time zone (json ->> '{details, attributes, destinationTimeZone}')));

The index has got created successfully, but when I do an EXPLAIN ANALYSE I dont find my index being used, Can anyone help me with where I am wrong?

explain analyze SELECT count(*) FROM reservations WHERE 
((text2tstz(json #>> '{details, attributes, checkIn}') at time zone (json #>> '{details, attributes, destinationTimeZone}'))) >= '2019-01-17' AND ((text2tstz(json #>> '{details, attributes, checkIn}') at time zone (json #>> '{details, attributes, destinationTimeZone}'))) < '2020-04-01';

Result of explain analyze

Aggregate  (cost=120515.80..120515.81 rows=1 width=0) (actual time=13794.176..13794.176 rows=1 loops=1)
  ->  Seq Scan on reservations  (cost=0.00..120510.32 rows=2193 width=0) (actual time=9479.960..13792.877 rows=2973 loops=1)
        Filter: ((timezone((json #>> '{details,attributes,destinationTimeZone}'::text[]), ((json #>> '{details,attributes,checkIn}'::text[]))::timestamp with time zone) >= '2019-01-17 00:00:00'::timestamp without time zone) AND (timezone((json #>> '{details,attributes,destinationTimeZone}'::text[]), ((json #>> '{details,attributes,checkIn}'::text[]))::timestamp with time zone) < '2020-04-01 00:00:00'::timestamp without time zone))
        Rows Removed by Filter: 435536
Planning time: 0.246 ms
Execution time: 13794.257 ms

I am using Postgresql 9.4.8

Use the #>> operator instead of ->> in the index creation and see how that works. It is incorrect as it is. There is no guaranty that postgres will use the index though, as it might be better to read the whole table as it only has few records. — Bjarni Ragnarsson, Mar 23 '20 at 13:22
@BjarniRagnarsson Thanyou but #>> didnt help. Its still the same. — iamcoded, Mar 23 '20 at 14:40

score 0 · Answer 1 · answered Mar 23 '20 at 14:26

You should define your immutable function like this:

CREATE FUNCTION get_checkin(jsonb) RETURNS timestamp with time zone
   LANGUAGE sql IMMUTABLE AS
$$SELECT ($1 #>> '{details, attributes, checkIn}')::timestamptz)
AT TIME ZONE
($1 #>> '{details, attributes, destinationTimeZone}')$$;

Then you can use that function in an index:

CREATE INDEX ON reservations (get_checkin(json));

Don't forget to collect statistics:

ANALYZE reservations;

Then query like this:

SELECT count(*)
FROM reservations
WHERE get_checkin(json) >= '2019-01-17'
  AND get_checkin(json) < '2020-04-01';

Postgresql not using created functional index

1 Answers1