From: Tom Lane <tgl@sss.pgh.pa.us>
To: Shaheed Haque <shaheedhaque@gmail.com>
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: Unable to make use of "deep" JSONB index
Date: Thu, 02 Jun 2022 10:31:00 -0400
Message-ID: <967134.1654180260@sss.pgh.pa.us> (raw)
In-Reply-To: <CAHAc2jdaD63ANRFuQpzZ13XibyP1QPqRUUDHTznRVep48e=7nA@mail.gmail.com>
References: <CAHAc2jdaD63ANRFuQpzZ13XibyP1QPqRUUDHTznRVep48e=7nA@mail.gmail.com>
Shaheed Haque <shaheedhaque@gmail.com> writes:
> -- Create index designed to match the query.
> --
> create index idx1 on payrun using gin ((snapshot->'$.employee.* ?
> (@.pay_graph <> 0 || @.last_run_of_employment == true ||
> @.state.employment[last][2] == 0)'));
But that doesn't match the query; it's not even the same topmost
operator:
> explain analyse SELECT id,snapshot #>'{employee,999,state,employment}' FROM
> "payrun" WHERE (snapshot @? '$.employee."999" ? (@.pay_graph <> 0 ||
> @.last_run_of_employment
> == true || @.state.employment[last][2] == 0)');
In general you seem to have much too high an opinion of what PG's
index machinery can cope with. The general pattern is that it can
use a query WHERE clause with an index if the clause is of the form
"indexed-column indexable-operator constant". There's a small number
of special cases where it can transform things that don't initially
look like that into the right form, but AFAIR we don't have any
such special cases for any json-related operators.
The one saving grace is that "indexed-column" can be an expression
appearing in an index, so in some cases you can finesse things
that way. But you won't find any deep knowledge of jsonpath
expressions in there.
Having said that, @? is reported as an indexable operator in v14:
regression=# \dAo gin jsonb*
List of operators of operator families
AM | Operator family | Operator | Strategy | Purpose
-----+-----------------+--------------------+----------+---------
gin | jsonb_ops | @>(jsonb,jsonb) | 7 | search
gin | jsonb_ops | @?(jsonb,jsonpath) | 15 | search
gin | jsonb_ops | @@(jsonb,jsonpath) | 16 | search
gin | jsonb_ops | ?(jsonb,text) | 9 | search
gin | jsonb_ops | ?|(jsonb,text[]) | 10 | search
gin | jsonb_ops | ?&(jsonb,text[]) | 11 | search
gin | jsonb_path_ops | @>(jsonb,jsonb) | 7 | search
gin | jsonb_path_ops | @?(jsonb,jsonpath) | 15 | search
gin | jsonb_path_ops | @@(jsonb,jsonpath) | 16 | search
(9 rows)
so it seems like you ought to get some benefit for this query
from just a plain GIN index on "snapshot".
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, shaheedhaque@gmail.com, pgsql-bugs@lists.postgresql.org
Subject: Re: Unable to make use of "deep" JSONB index
In-Reply-To: <967134.1654180260@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox