What is the best index for postgresql timestamp

Question

I have searched a lot but has not found the answer for the following question. I have a table with lots of record (in the order of 100M) and I want to run the following query on it :

Entity.objects.filter(creation_time__gte=some_date).order_by('id').all()[0]

and my table is something like follows :

class Entity(models.Model):
    creation_time = models.DateTimeField(null=True, blank=True)
    # Other fields

I guess output is not supposed to be precise, so why not to sort by serial id? In simple cases will give almost the same order. After that you may continue filtering by date if you need to. — Ivan Starostin, Aug 21 '18 at 11:33
That should be a simple B-tree index on `creation_time`, right? — Laurenz Albe, Aug 21 '18 at 11:35
I want minimum ID of my objects that are created after some special time. I don't what minimum ID of whole objects — mohammad, Aug 21 '18 at 11:35
@LaurenzAlbe with b_tree query is still very very slow and takes tens of minutes to perform — mohammad, Aug 21 '18 at 11:36
Please describe your task in the question. Not solution. What is the "special time", why "minimum ID" and so on. — Ivan Starostin, Aug 21 '18 at 11:37
Also in my query I'm sorting by id and getting the first one. So minimum ID! @IvanStarostin — mohammad, Aug 21 '18 at 11:40
if you handle a lot of dates check out https://www.timescale.com/ — hansTheFranz, Aug 21 '18 at 11:41
@mohammad Then find out the SQL query, collect `EXPLAIN (ANALYZE, BUFFERS)` output for it and add the result to the question. — Laurenz Albe, Aug 21 '18 at 11:43
@LaurenzAlbe What should I exactly run? running only `EXPLAIN (ANALYZE, BUFFERS)` raises error — mohammad, Aug 21 '18 at 11:49
`EXPLAIN (ANALYZE, BUFFERS) SELECT ...`. For that, you need to figure out the `SELECT` statement. — Laurenz Albe, Aug 21 '18 at 12:08
did you try to remove `all` `Entity.objects.filter(creation_time__gte=some_date).order_by('id')[0]` — Brown Bear, Aug 21 '18 at 12:11

score 1 · Answer 1 · answered Aug 21 '18 at 12:19

No matter what database you run this against and what index that you use on the creation_time field this query is likely to be slow. Why? Because your filter is an inequality and not an equality and you are combining that with a sort, think about it in a worst case scenario you could be sorting 99 million records.

One approach that you could use is to create a non serial primary key that is derived from creation time. Sort of like described here: https://stackoverflow.com/a/37605582/267540

You could create a BTREE creation time and then do a query that filters further with an upper limit

Entity.objects.filter(creation_time__gte=some_date
    ).filter(creation_time__lt=some_other_date).order_by('id').all()[0]

And lastly you could do a query that use 'only' to fetch only the primary key, that would be an index only query for postgresql and could lead to a slight speed up.

With the link you provided (first solution) how can I do my query? — mohammad, Aug 23 '18 at 02:56

What is the best index for postgresql timestamp

1 Answers1