Postgresql не использует созданный функциональный индекс

Question

Для выполнения запроса

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';

Я создал функциональный индекс:

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}')));

Индекс создан успешно, но когда я выполняю EXPLAIN ANALYZE, я не обнаруживаю, что мой индекс используется. Может ли кто-нибудь помочь мне с ошибкой?

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';

Результат объяснения анализа

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

Я использую Postgresql 9.4.8

Answer 1

Вы должны определить свою неизменяемую функцию следующим образом:

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}')$$;

Затем вы можете использовать эту функцию в индексе:

CREATE INDEX ON reservations (get_checkin(json));

Не забываем собирать статистику:

ANALYZE reservations;

Затем запросите вот так:

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