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
In-reply-to: 
 <CAHAc2jdaD63ANRFuQpzZ13XibyP1QPqRUUDHTznRVep48e=7nA@mail.gmail.com>
References: 
 <CAHAc2jdaD63ANRFuQpzZ13XibyP1QPqRUUDHTznRVep48e=7nA@mail.gmail.com>
Comments: In-reply-to Shaheed Haque <shaheedhaque@gmail.com>
	message dated "Thu, 02 Jun 2022 11:27:26 +0100"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <967133.1654180260.1@sss.pgh.pa.us>
Date: Thu, 02 Jun 2022 10:31:00 -0400
Message-ID: <967134.1654180260@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/967134.1654180260%40sss.pgh.pa.us>
Precedence: bulk

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