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

On Thu, 2 Jun 2022 at 15:31, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> Shaheed Haque <shaheedhaque(at)gmail(dot)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