In one of my models I have the following fields:
creation_time = models.DateTimeField(auto_now_add=True)
creation_date = models.DateField(db_index=True, auto_now_add=True)
If I do the following filter using the creation time (DateTimeField field):
User.objects.filter(id__in=user_ids, creation_time__range=[start_date, end_date]).count()
It takes few ms to run, as expected. But if I do the same filter on the creation date (DateField field with db index):
User.objects.filter(id__in=user_ids, creation_date__range=[start_date, end_date]).count()
It takes few minutes to finish... Eventually I get almost the same result as above, but after a long time.
I though both fields are stored the same way in the DB and the only difference is how django serialize / deserialize them into Python objects (and ofc date field getting default midnight time).
So why using the date field is so much slower? Is it because of the db_index (if so, why)?
EDIT: their SQL query looks essentially the same btw:
>>> print User.objects.filter(id__in=user_ids, creation_time__range=[start_date, end_date]).query
SELECT "users_user"."id", "users_user"."uuid", "users_user"."creation_time", "users_user"."ip", "users_user"."user_agent", "users_user"."hash", "users_user"."creation_date", "users_user"."is_bot" FROM "users_user" WHERE ("users_user"."id" IN (0, 1, 2, 3, 4, 5, 6, 7, 8, 9) AND "users_user"."creation_time" BETWEEN 2017-07-18 00:00:00 AND 2017-07-19 00:00:00)
>>> print User.objects.filter(id__in=user_ids, creation_date__range=[start_date, end_date]).query
SELECT "users_user"."id", "users_user"."uuid", "users_user"."creation_time", "users_user"."ip", "users_user"."user_agent", "users_user"."hash", "users_user"."creation_date", "users_user"."is_bot" FROM "users_user" WHERE ("users_user"."id" IN (0, 1, 2, 3, 4, 5, 6, 7, 8, 9) AND "users_user"."creation_date" BETWEEN 2017-07-18 AND 2017-07-19)
Thanks,