CODEWITHSUNDEEP
postgresqlindexing
clappa
clappa

Reputation: 3

Postgresql best index for datetime ranges

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

Upvotes: 0

Views: 4270

Answers (1)

jjanes
jjanes

Reputation: 44202

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.

Upvotes: 2

Related Questions