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

On Sun, 12 Jun 2022 at 22:55, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> Jeff Janes <jeff(dot)janes(at)gmail(dot)com> writes:
> > On Sun, Jun 12, 2022 at 5:34 AM Shaheed Haque <shaheedhaque(at)gmail(dot)com>
> > wrote:
> >> OK, I have corrected and simplified the test case (including switching
> >> to a btree index). The WHERE clause and the inex now look like this:
> >>
> >> ...WHERE ((snapshot -> 'employee' -> '999' ->>
> >> 'pay_graph')::integer != 0);
> >> ...USING btree (((snapshot -> 'employee' -> '$.*' ->>
> >> 'pay_graph')::integer != 0));
>
> > But, this is not a correction. You are still trying to use -> as if it
> > were @?, and that is still not going to work.
>
> In hopes of clarifying some more: all that index does is to record
> the boolean result of
> (snapshot -> 'employee' -> '$.*' ->> 'pay_graph')::integer != 0
> at each row. We could use it for a query that contains *exactly*
> that condition as a WHERE clause. We cannot use it for a query that
> contains some other condition, even if that other condition looks
> related to you.

OK, I see that I got myself all confused and the @? form is needed. So:

...USING btree ((snapshot -> 'employee' @? '$.* ? (@.pay_graph != 0)'));
...WHERE ((snapshot -> 'employee' @? '$."999" ? (@.pay_graph != 0)'))

> > You are indexing the part of snapshot which has the employee number of
> > '$.*', which is a weird employee number for anyone to have. You might want
> > to represent a wildcard but that is not what -> does.
>
> Yeah, there's also the problem that the semantics of this particular
> expression aren't really useful.

Does the switch back to @? address this point? If not, please clarify.

> But even if they were, PG's index
> machinery is not smart enough to pick apart the contents of an index
> expression. If the index expression *exactly* matches some sub-expression

To my inexpert eye, given the presence of the wildcard, the above look
like an exact match. What have I missed?

> of a WHERE clause, and what's above that sub-expression is an operator
> that's indexable according to the index opclass, then we have a chance
> of using it. This example is not that.

Because I switched to btree, and btree cannot search on "@?"? So, what
should the index+query look like using gin? (I am trying to address a
niche case, and can easily arrange for them to match if I knew what
was needed).

Thanks, Shaheed

>
> regards, tom lane