Index JSON in SQLAlchemy on Postgresql

Question

I am using JSON in my SQLAlchemy DB model:

from sqlalchemy.dialects.postgresql import JSON

class Customer(db.Model):
  id = db.Column(db.Integer, primary_key=True, autoincrement=True)
  custcontext_json = db.Column(JSON, default=lambda: {})

Now I have this query that is getting too slow:

customers = Customer.query.filter(
  Customer.clientid == clientid,
  or_(
    func.lower(Customer.custcontext_json['cinfo', 'userName'].astext.cast(Unicode)).contains(searchterm.lower()),
    func.lower(Customer.custcontext_json['cinfo', 'home', 'sign'].astext.cast(Unicode)).contains(searchterm.lower())
  ),

  or_(
    Customer.custcontext_json['cinfo', 'home', 'status'].astext == 'pre',
    Customer.custcontext_json['cinfo', 'home', 'status'].astext == 'during',
    Customer.custcontext_json['cinfo', 'lastChange'].astext.cast(DateTime) > pendulum.now('UTC').subtract(days=14)
  )
  ).all()

Is it possible to index the table for this query. Or at least parts of it?

Can you please get sample query and its query plan. You can make use of JSONBform and GIN indexes which can speed up things for you. — Raj Verma
– Raj Verma, Commented Feb 11, 2020 at 5:37
-- use jsonb form of the method instead of the json form as well. -- if you have tags use this " ->" instead of "->>" cos "->" will return the json object and "->>" will return the tag/ property as an integer or text. — Raj Verma
– Raj Verma, Commented Feb 11, 2020 at 5:42
While my background is in MySQL, I assume that even in Postgresql the use of a function on a column will prevent your database from using an index on that column. That is backed up by comments here although it sounds like you can create indexes on function results too based on one of the answers. Ignoring that potential option for a second, indexing a text-based column will only be useful if you are searching from the start of the string. Perhaps you could extract these attributes into separate columns that you index. — totalhack
– totalhack, Commented Feb 14, 2020 at 21:42
Doing contains - even in normal SQL is a performance killer. If you did starts with you may yields slighly improved performance. — JGFMK
– JGFMK, Commented Feb 15, 2020 at 10:51

Michał Zaborowski · Accepted Answer · 2020-02-15 20:36:30Z

0

I would use JSONB as format with higher density. But that is not an answer.

Please look here. Operators for JSON type in PostgreSQL, you can get from here. I would like to get your create table, to reproduce something at my side. Any way. I would start with something like this:

create table customer (clientid serial, custcontext_json jsonb);

create index on customer (
  (CAST((custcontext_json->'cinfo'->'userName') AS TEXT)),
  (CAST((custcontext_json->'cinfo'->'home'->'sign') AS TEXT)),
  (CAST((custcontext_json->'cinfo'->'home'->'status') AS TEXT)));

answered Feb 15, 2020 at 20:36

Michał Zaborowski

4,4052 gold badges23 silver badges42 bronze badges

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

Collectives™ on Stack Overflow

Index JSON in SQLAlchemy on Postgresql

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related