Postgresql best index for datetime ranges

Question

I have a Postgre table “tasks” with the fields “start”:timestamptz, “finish”:timestamptz, “type”:int (and a lot of others). It contains about 200m records. Start, finish and type fields have a separate b-tree indexes. I’d like to build a report “Tasks for a period” and need to get all tasks which lay (fully or partially) inside the reporting period. Report could be built for all task types or for the specific one. So I wrote the SQL:

SELECT * FROM tasks 
WHERE start<={report_to} 
AND finish>={report_from}
AND ({report_tasktype} IS NULL OR type={report_tasktype})

and it runs for ages even on short reporting periods. Please advice if there a way to improve performance by altering the query or by creating new indexes on the table? For some reasons I can’t change the structure of the “tasks” table

@bigbounty depending on the report parameters. When report_tasktype provided, it uses Index scan on “type” with filter on “start” and “finish” clauses. When the report period is like previous month, it uses Index scan on “finish” with filter on “start”. Otherwise it uses seqscan — clappa
– clappa, Commented May 30, 2021 at 13:37

jjanes · Accepted Answer · 2021-05-30 13:54:04Z

2

You would want a GiST index on the range. Since you already have it stored as two end points rather than as a range, you could do a functional index to convert them on the fly.

ON task USING GIST (tstzrange(start,finish))

And then compare the ranges for overlap with &&

It may also improve things to add "type" as a second column to the index, which would require the btree_gist extension.

answered May 30, 2021 at 13:54

jjanes

44.9k5 gold badges39 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

clappa Over a year ago

Thank you! GIST keyword brings me here dba.stackexchange.com/questions/39589/…

clappa Over a year ago

(and yes, I promise to use search before asking questions)

Collectives™ on Stack Overflow

Postgresql best index for datetime ranges

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related