Django filter is not using the index on Postgresql JSONField

Question

I have a Django model that contains a PostgreSQL jsonb field:

class SocialUser(models.Model):
    id = models.BigIntegerField(primary_key=True)
    data = JSONField(blank=True, null=True, db_index=True)

The data field contains a username attribute.

I have indexed this attribute by

CREATE INDEX ON users_socialuser ((data->>'username'));

When I query it via Django ORM with the id,

SocialUser.objects.get(id=123)

and via the pgAdmin

SELECT * FROM users_socialuser WHERE id = 123

they are both fast.

But when I query with the JSONField's attribute username, pgAdmin SQL query

SELECT * FROM users_socialuser WHERE data->>'username' = 'abc'

is still equally fast, while

SocialUser.objects.get(data__username='abc')

is terribly slow.

It seems that the Django ORM is not using the index on the username attribute.

Why is that so? Can I explicitly force an index in Django ORM? Is there a workaround for this?

onurmatik · Accepted Answer · 2019-08-07 22:45:56Z

3

Exploring the query generated by the Django ORM, I've noticed that the WHERE clause is like

data -> 'screen_name' = 'abc'

Notice the single arrow -> instead of ->>.

So, I built an index with the single arrow

CREATE INDEX ON users_socialuser ((data->'username'));

and the ORM queries are fast now too.

answered Aug 7, 2019 at 22:45

onurmatik

5,3457 gold badges45 silver badges68 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1