Re: Performance on JSONB select - Mailing list pgsql-general

From Michael Lewis
Subject Re: Performance on JSONB select
Date
Msg-id CAHOFxGrqwJYg=iuyjqt9Tf6twb1h3aA13+c0sOWY1SESbT9gXw@mail.gmail.com
Whole thread Raw
In response to Re: Performance on JSONB select  (PegoraroF10 <marcos@f10.com.br>)
Responses Re: Performance on JSONB select
List pgsql-general
My Json has always a status

Why declare the "where" clause when creating the index? It would not seem needed if status is always set and so your index will reference all rows in the table.

Thanks for sharing the trick of having the second column in the index determine the key based on the first column. I don't know if/when I might need this, but an interesting solution. Generically, it seems like the below-

create index idx_mytable_jsonb_dependent_fields on public.mytable ( (JsonBField->>'primary_field_to_filter_on'), JsonBField->(JsonBField->>'primary_field_to_filter_on')->>'secondary_field' );

pgsql-general by date:

Previous
From: Tomas Vondra
Date:
Subject: Re: Table locking during backup
Next
From: Michael Lewis
Date:
Subject: Re: JSONB maximal length ?