CODEWITHSUNDEEP
sqlpostgresqldatabase-indexes
Syed Mohammad Sualeh Ali
Syed Mohammad Sualeh Ali

Reputation: 21

Postgres: How to setup an index on timestamptz using a time interval?

I have a query that I need to optimize using indexes. Is there any way I can index the column assigned_at which is a timestamptz type. I was thinking of using a GIST index initally of assigned_at, is there any way I can incorporate that given I will have to use now() or pass in the current date time which is mutable and cant be indexed.

select id from foo f where 
f.context_id = $1 
and (f.assigned_at <= (now() - interval '60 second') or f.assigned_at is null)

Open for suggestions to setup an index for this query! Thanks in advance!!

Upvotes: 1

Views: 1125

Answers (2)

Jim Jones
Jim Jones

Reputation: 19643

Considering the query you posted, a composite index using context_id and assigned_at should do the trick:

CREATE INDEX idx_foo ON foo (context_id,assigned_at);

Depending on your data, the OR conditional might slow down your query a bit. An alternative is to split this conditional into two different queries:

SELECT * FROM foo f 
WHERE f.context_id = 1 AND f.assigned_at <= (now() - interval '60 second')
UNION ALL
SELECT * FROM foo f WHERE f.context_id = 1 AND f.assigned_at IS NULL;

As always, play with both queries and test which approach fits your data best.

Demo: db<>fiddle

Upvotes: 1

jjanes
jjanes

Reputation: 44257

A simple btree index on (context_id, assigned_at) is usable for that query. But to fully use all parts, it would need to read the index twice and do a BitmapOr between them. Depending on how selective it thinks each term will be, it might choose to take a different approach, for example using the index only for context_id and applying the time part as a filter.

explain analyze select id from foo f where 
f.context_id = 7 
and (f.assigned_at <= (now() - interval '60 second') or f.assigned_at is null);
                                                                 QUERY PLAN                                                                  
---------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on foo f  (cost=8.88..12.90 rows=1 width=4) (actual time=0.031..0.041 rows=7 loops=1)
   Recheck Cond: (((context_id = 7) AND (assigned_at <= (now() - '00:01:00'::interval))) OR ((context_id = 7) AND (assigned_at IS NULL)))
   Filter: ((assigned_at <= (now() - '00:01:00'::interval)) OR (assigned_at IS NULL))
   Heap Blocks: exact=4
   ->  BitmapOr  (cost=8.88..8.88 rows=1 width=0) (actual time=0.024..0.025 rows=0 loops=1)
         ->  Bitmap Index Scan on foo_context_id_assigned_at_idx  (cost=0.00..4.44 rows=1 width=0) (actual time=0.016..0.016 rows=0 loops=1)
               Index Cond: ((context_id = 7) AND (assigned_at <= (now() - '00:01:00'::interval)))
         ->  Bitmap Index Scan on foo_context_id_assigned_at_idx  (cost=0.00..4.43 rows=1 width=0) (actual time=0.007..0.007 rows=7 loops=1)
               Index Cond: ((context_id = 7) AND (assigned_at IS NULL))

Upvotes: 1

Related Questions