'Django ORM converting date to datetime which is slowing down query 30x

I'm attempting query a table and filter the results by date on a datetime field: .filter(bucket__gte = start_date) where bucket is a datetimefield and start_date is a date object.

However django converts the start_date to a timestamp in the raw sql ex 2020-02-01 00:00:00 when I want it just be a date ex 2020-02-01.

For some reason casting bucket to a date or casting start_time to a timestamp makes the query 30x slower.

When I manually write a query and compare bucket directly to a date ex bucket >= '2020-02-01' the query is blazing fast.

How can I get the django orm to do this?

djangopostgresqldjango-ormtimescaledb


Solution 1:[1]

Seems that its most efficient to convert your date to a datetime in python then do the lookup on the orm since you are filtering on a DateTimeField

from datetime import datetime


.filter(bucket__gte=datetime.combine(start_date, datetime.min.time()))

If the field bucket is indexed, the explain of this query should indicate an Index Scan which would provide desired and most efficient execution plan.

If not it should still be faster since you avoid casting

Solution 2:[2]

For some reason casting bucket to a date or casting start_time to a timestamp makes the query 30x slower.

Yes, casting bucket to a date would prevent use of an index (unless the index was over the casted column). But casting start_time to a timestamp would not. What is it 30 times slower than? You just said python automatically converts it, so, how is that different than casting it? As in, what is your actual code?

When I manually write a query and compare bucket directly to a date ex bucket >= '2020-02-01' the query is blazing fast.

OK, but what is it actually doing?

explain select * from foo where bucket > '2021-03-01';
                               QUERY PLAN                                
-------------------------------------------------------------------------
 Seq Scan on foo  (cost=0.00..38.25 rows=753 width=8)
   Filter: (bucket > '2021-03-01 00:00:00-05'::timestamp with time zone)
(2 rows)

PostgreSQL is also converting it to a timestamp. Does it give the right answer or the wrong answer?

Solution 3:[3]

Try convet your datetime to date in filter:

.filter(bucket__date__gte = start_date)

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 ed_
Solution 2
Solution 3 HudsonBarroso

Related Questions