Postgres tsrange index for small sliver of time

Question

I have a table (events) that has two timestampz columns (start_time and end_time). My program queries the table every minute for all records that are happening right now:

select * from events
where tstzrange(start_time, end_time, '[)') && tstzrange(:start, :end, '[)')
and status = 'PUBLISHED'

Where the bound variables for start and end are sent by the program tied to the current minute (truncated to the minute) and the next minute (truncated to the minute). Ex: 12:00:00.000000 and 12:00:01.000000.

The index for this, as per this excellent answer, is as follows:

create index concurrently
    events_activity
    on events USING gist
    (tstzrange(start_time, end_time, '[)'))
    where status = 'PUBLISHED';

I believe this should serve my purpose well -- the index has taken 90+ minutes to create so far, so I can't be certain. But assuming it does, I have one concern: it seems really wasteful. I base this on the assumption that it is keeping all records in the index for all of eternity, but my use case only requires the time range query for a small window of time.

So my question is: given that I only query for ranges of time that are really close to "now", is there some way to keep the index much smaller to reflect my narrow needs? Is there some where to constrain the index to just reflect records that I'm expecting to query for? Would adding the following to the end of the index automatically keep it small and constrained to my target window?

tstzrange(start_time, end_time, '[)') 
  && tstzrange(now() - interval '30 minutes', now() + interval '30 minutes', '[)')

UPDATE: No, that won't work... functions in index predicate must be marked IMMUTABLE.

Thank you!

score 2 · Accepted Answer · answered Jan 12 '21 at 07:26

I think that there is no way to do this - making the indexed rows dependent on the current time would mean that the index has to change all the time, and that is something PostgreSQL doesn't do.

You could of course introduce a condition:

CREATE INDEX ON events USING gist
    (tstzrange(start_time, end_time, '[)'))
WHERE status = 'PUBLISHED'
  AND start_time > '2021-01-01 00:00:00';

and drop and re-create the index each year (or month).

But then your queries would have to contain the same condition that changes yearly, which does not seem practical.

Postgres tsrange index for small sliver of time

1 Answers1