Re: Unable to make use of "deep" JSONB index

On Thu, 2 Jun 2022 at 15:31, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> 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)');

I assume you are referring to the difference between "snapshot @?" and
"snapshot ->"? If so, apologies: too much cutting and pasting from too
many experiments. I did in fact also try the "using btree ((snapshot
@?" form but it gave the same results.

> 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.

LOL. I'm pretty much a noob here, so that's very possible.

> 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.

I was basing my efforts on this statement in the docs
https://www.postgresql.org/docs/14/datatype-json.html#JSON-INDEXING:

    GIN index extracts statements of following form out of jsonpath:
accessors_chain = const. Accessors chain may consist of .key, [*], and
[index] accessors. jsonb_ops additionally supports .* and .**
accessors.

Did I mis-implement, misunderstand or read too much into this?

> 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".

Interesting. I'm pretty sure I started there a few days ago without
any luck but I'll give it another spin (having learnt quite a bit
since then).

>
>                         regards, tom lane