[Django] #34107: Django ORM queries do not pick up indexes made on a key in JSONField in Postgres

[Django]

#34107: Django ORM queries do not pick up indexes made on a key in JSONField in
Postgres
-------------------------------------+-------------------------------------
               Reporter:  Tadek      |          Owner:  nobody
  Teleżyński                         |
                   Type:  Bug        |         Status:  new
              Component:  Database   |        Version:  3.2
  layer (models, ORM)                |       Keywords:  json, jsonfield,
               Severity:  Normal     |  postgres, index
           Triage Stage:             |      Has patch:  0
  Unreviewed                         |
    Needs documentation:  0          |    Needs tests:  0
Patch needs improvement:  0          |  Easy pickings:  0
                  UI/UX:  0          |
-------------------------------------+-------------------------------------
 Hey folks!

 Been developing in Django for the past 5 years, excited to post my first
 ticket :)
 Thank you from the bottom of my heart for developing and maintaining this
 amazing framework.
 If you feel like the below is more of a feature request than a bug, feel
 free to recategorize it.

 Consider a following setup (assuming Postgres database, version 13.8):
 {{{
 class Example(models.Model):
     data = models.JSONField(null=False, blank=True, default=dict)
 }}}
 And the following migration adding two indexes (note the difference in
 "->" and "->>") on data__category key:

 {{{
 class Migration(migrations.Migration):

     dependencies = [
         (...),
     ]
     atomic = False
     operations = [
         migrations.RunSQL(
             "CREATE INDEX CONCURRENTLY CategoryIndex_default_cast ON
 app_example USING BTREE((data->>'category'));",
             reverse_sql="DROP INDEX IF EXISTS CategoryIndex_default_cast;"
         ),
         migrations.RunSQL(
             "CREATE INDEX CONCURRENTLY CategoryIndex ON app_example USING
 BTREE (((data->'category')::TEXT));",
             reverse_sql="DROP INDEX IF EXISTS CategoryIndex;"
         ),
     ]
 }}}

 Now if I use a regular filtering syntax on a query none of the indexes is
 used:
 {{{
 >>> print(Example.objects.filter(data__category='dog').explain())
 Seq Scan on app_example  (cost=0.00..29.05 rows=6 width=36)
   Filter: ((data -> 'category'::text) = '"dog"'::jsonb)
 }}}

 In order to pick up the index I need to write a bit more complex query:
 {{{
 >>> print(Example.objects.annotate(_category=KeyTextTransform('category',
 'data')).filter(_category=Value('dog')).explain())
 Bitmap Heap Scan on app_example  (cost=4.20..13.70 rows=6 width=68)
   Recheck Cond: ((data ->> 'category'::text) = 'dog'::text)
   ->  Bitmap Index Scan on categoryindex_default_cast  (cost=0.00..4.20
 rows=6 width=0)
         Index Cond: ((data ->> 'category'::text) = 'dog'::text)
 }}}

 To be honest I wasn't able to figure out a query that would pick up the
 second index ("CategoryIndex") that's using explicit type casting.

 It would be very neat if the ORM could figure the type casting
 automatically.

-- 
Ticket URL: <https://code.djangoproject.com/ticket/34107>
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/01070183f0f8861d-9c687354-657f-4284-9c76-43c97fd34d7b-000000%40eu-central-1.amazonses.com.