From: Tom Lane <tgl@sss.pgh.pa.us>
To: Jeff Janes <jeff.janes@gmail.com>
Cc: Shaheed Haque <shaheedhaque@gmail.com>
Cc: pgsql-bugs <pgsql-bugs@lists.postgresql.org>
Subject: Re: Unable to make use of "deep" JSONB index
Date: Sun, 12 Jun 2022 17:55:47 -0400
Message-ID: <3543018.1655070947@sss.pgh.pa.us> (raw)
In-Reply-To: <CAMkU=1zHod+UktMioLywae=NsEtAmFe1S1VRqSqSqin4NGXH6Q@mail.gmail.com>
References: <CAHAc2jdaD63ANRFuQpzZ13XibyP1QPqRUUDHTznRVep48e=7nA@mail.gmail.com>
<967134.1654180260@sss.pgh.pa.us>
<CAHAc2jfjbuHW5g=b81sHPE4RcU_Lw7bi92jW+-Do4bKePsfq3g@mail.gmail.com>
<CAHAc2jfokgv5GZ-eLrAJ8G52gLqSRy_VR-s0R90uJxui-YSQsQ@mail.gmail.com>
<CAMkU=1zHod+UktMioLywae=NsEtAmFe1S1VRqSqSqin4NGXH6Q@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, jeff.janes@gmail.com, shaheedhaque@gmail.com, pgsql-bugs@lists.postgresql.org
Subject: Re: Unable to make use of "deep" JSONB index
In-Reply-To: <3543018.1655070947@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox