tstzrange containment check (@>) against timestamptz column does not use index (btree or gist)

by Gajus   Last Updated January 11, 2019 12:06 PM

Schema:

        Column        |           Type           
----------------------+--------------------------
 id                   | integer                  
 event_id             | integer                  
 started_at           | timestamp with time zone 
 ended_at             | timestamp with time zone 
 created_at           | timestamp with time zone 
    "event_seat_state_lookup_pkey" PRIMARY KEY, btree (id)
    "event_seating_lookup_created_at_idx" btree (created_at)
    "event_seating_lookup_created_at_idx2" gist (created_at)

Query:

SELECT id
FROM event_seating_lookup esl1
WHERE
  tstzrange(now() - interval '1 hour', now() + interval '1 hour', '[)') @> esl1.created_at;

Explain analyze:

                                                         QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
 Seq Scan on event_seating_lookup esl1  (cost=0.00..1550.30 rows=148 width=4) (actual time=0.013..19.956 rows=29103 loops=1)
   Filter: (tstzrange((now() - '01:00:00'::interval), (now() + '01:00:00'::interval), '[)'::text) @> created_at)
   Buffers: shared hit=809
 Planning Time: 0.110 ms
 Execution Time: 21.942 ms
(5 rows)

Table size (tried with table containing 1M rows+):

applaudience=> SELECT count(*) FROM event_seating_lookup;
 count
-------
 28003
(1 row)

Troubleshooting:

I have tried:

VACUUM FULL event_seating_lookup;
VACUUM event_seating_lookup;
VACUUM ANALYZE event_seating_lookup;
SET enable_seqscan = OFF;

Problem:

event_seating_lookup_created_at_idx or event_seating_lookup_created_at_idx2 indexes are not being used.



Related Questions





Why PostGIS GIST index is not used?

Updated December 02, 2017 01:06 AM

Postgresql planner btree vs gist few rows on result

Updated February 27, 2018 22:06 PM