[Django] #34840: Django 4.2 casts text fields when testing IS NULL, preventing use of partial indexes

[Django]

#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 django-updates+unsubscr...@googlegroups.com.
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.