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

Jeff Janes <jeff.janes@gmail.com> writes:
> On Sun, Jun 12, 2022 at 5:34 AM Shaheed Haque <shaheedhaque@gmail.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.

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

            regards, tom lane