[Django] #34107: Django ORM queries do not pick up indexes made on a key in JSONField in Postgres
Django
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.
Django
Postgres
-------------------------------------+-------------------------------------
Type: Bug | Status: new
Component: Database layer | Version: 3.2
(models, ORM) |
Severity: Normal | Resolution:
Keywords: json, jsonfield, | Triage Stage:
postgres, index | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Old description:
New description:
Hey folks!
{{{
class Migration(migrations.Migration):
--
--
Ticket URL: <https://code.djangoproject.com/ticket/34107#comment:1>
Django
Old description:
New description:
Hey folks!
{{{
class Migration(migrations.Migration):
--
--
Ticket URL: <https://code.djangoproject.com/ticket/34107#comment:2>
Django
Postgres
-------------------------------------+-------------------------------------
Reporter: Tadek Teleżyński | Owner: nobody
Component: Database layer | Version: 3.2
(models, ORM) |
Keywords: json, jsonfield, | Triage Stage:
postgres, index | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: new => closed
* resolution: => invalid
Comment:
I don't think there is anything that Django could do better here. In most
of cases creating indexes via `Meta.indexes` helps to avoid small
differences between defined indexes and used filters.
> 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.
You can try to `Cast(F("data__category"), output_field=TextField())`.
By the way, have you tried to create these indexes via `Meta.indexes`? You
can create them
[https://docs.djangoproject.com/en/4.1/ref/contrib/postgres/operations
/#concurrent-index-operations concurrently] without writing raw SQLs.
--
Ticket URL: <https://code.djangoproject.com/ticket/34107#comment:3>
Django
Postgres
-------------------------------------+-------------------------------------
Reporter: Tadek Teleżyński | Owner: nobody
Type: Bug | Status: closed
Component: Database layer | Version: 3.2
(models, ORM) |
Severity: Normal | Resolution: invalid
Keywords: json, jsonfield, | Triage Stage:
postgres, index | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Tadek Teleżyński):
Replying to [comment:3 Mariusz Felisiak]:
> By the way, have you tried to create these indexes via `Meta.indexes`?
You can create them
[https://docs.djangoproject.com/en/4.1/ref/contrib/postgres/operations
/#concurrent-index-operations concurrently] without writing raw SQLs.
I couldn't figure out a syntax for the {{{fields}}} key when I want to do
an index on a key inside the json structure.
{{{
BTreeIndex(fields=['data__category'])
}}}
won't do...
--
Ticket URL: <https://code.djangoproject.com/ticket/34107#comment:4>
Django
Postgres
-------------------------------------+-------------------------------------
Reporter: Tadek Teleżyński | Owner: nobody
Type: Bug | Status: closed
Component: Database layer | Version: 3.2
(models, ORM) |
Severity: Normal | Resolution: invalid
Keywords: json, jsonfield, | Triage Stage:
postgres, index | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Mariusz Felisiak):
> I couldn't figure out a syntax for the {{{fields}}} key when I want to
do an index on a key inside the json structure.
> {{{
> BTreeIndex(fields=['data__category'])
> }}}
> won't do...
`BTreeIndex` supports expressions, so you can use
`BTreeIndex(F("data__category"), name="...")`.
--
Ticket URL: <https://code.djangoproject.com/ticket/34107#comment:5>