#34840: Django 4.2 casts text fields when testing IS NULL, preventing use of
partial indexes
-------------------------------------+-------------------------------------
Reporter: Alex | Owner: nobody
Vandiver |
Type: | Status: new
Uncategorized |
Component: Database | Version: 4.2
layer (models, ORM) |
Severity: Normal | Keywords:
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
The Zulip project has a model with unique constraints which are expressed
as two non-overlapping partial indexes:
{{{
class UserCount(models.Model):
user = models.ForeignKey(UserProfile, on_delete=models.CASCADE)
realm = models.ForeignKey(Realm, on_delete=models.CASCADE)
property = models.CharField(max_length=32)
subgroup = models.CharField(max_length=16, null=True)
end_time = models.DateTimeField()
value = models.BigIntegerField()
class Meta:
constraints = [
UniqueConstraint(
fields=["user", "property", "subgroup", "end_time"],
condition=Q(subgroup__isnull=False),
name="unique_user_count",
),
UniqueConstraint(
fields=["user", "property", "end_time"],
condition=Q(subgroup__isnull=True),
name="unique_user_count_null_subgroup",
),
]
}}}
However, since commit 09ffc5c1212d4ced58b708cbbf3dfbfb77b782ca in Django
4.2, a query of the form:
{{{
UserCount.objects.get(
property="messages_read::hour",
subgroup=None,
end_time=timezone_now(),
user_id=user_profile.id,
realm_id=realm.id,
)
}}}
...generates this SQL:
{{{
SELECT "analytics_usercount"."id", "analytics_usercount"."property",
"analytics_usercount"."subgroup", "analytics_usercount"."end_time",
"analytics_usercount"."value", "analytics_usercount"."user_id",
"analytics_usercount"."realm_id" FROM "analytics_usercount" WHERE
("analytics_usercount"."end_time" =
'2023-09-13T19:16:34.195355+00:00'::timestamptz AND
"analytics_usercount"."property" = 'messages_read::hour' AND
"analytics_usercount"."realm_id" = 4715 AND
"analytics_usercount"."subgroup"::text IS NULL AND
"analytics_usercount"."user_id" = 428054) LIMIT 21
}}}
The cast of `"analytics_usercount"."subgroup"::text IS NULL` causes
PostgreSQL to not be able to use the unique partial index:
{{{
Limit (cost=48.30..49.42 rows=1 width=61)
-> Bitmap Heap Scan on analytics_usercount (cost=48.30..49.42 rows=1
width=61)
Recheck Cond: (((property)::text = 'messages_read::hour'::text)
AND (realm_id = 4715) AND (end_time = '2023-09-13 19:00:00+00'::timestamp
with time zone) AND (user_id = 428054))
Filter: ((subgroup)::text IS NULL)
-> BitmapAnd (cost=48.30..48.30 rows=1 width=0)
-> Bitmap Index Scan on
analytics_usercount_property_591dbec1_idx (cost=0.00..4.88 rows=158
width=0)
Index Cond: (((property)::text =
'messages_read::hour'::text) AND (realm_id = 4715) AND (end_time =
'2023-09-13 19:00:00+00'::timestamp with time zone))
-> Bitmap Index Scan on analytics_usercount_e8701ad4
(cost=0.00..43.17 rows=3626 width=0)
Index Cond: (user_id = 428054)
}}}
Dropping the explicit cast causes it to use the index:
{{{
Limit (cost=0.57..2.80 rows=1 width=61)
-> Index Scan using unique_user_count_null_subgroup on
analytics_usercount (cost=0.57..2.80 rows=1 width=61)
Index Cond: ((user_id = 428054) AND ((property)::text =
'messages_read::hour'::text) AND (end_time = '2023-09-13
19:00:00+00'::timestamp with time zone))
Filter: (realm_id = 4715)
}}}
...and improving the query runtime significantly.
It's not clear to me from 09ffc5c1212d4ced58b708cbbf3dfbfb77b782ca what
about psycopg3 requires this cast.
--
Ticket URL: <https://code.djangoproject.com/ticket/34840>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/0107018a90021122-80b7551b-4b8d-4be0-b477-fd00b2983ae4-000000%40eu-central-1.amazonses.com.
