Date: Sat, 16 Sep 2023 22:50:13 +0200 (CEST)
From: Erik Wienhold <ewie@ewie.name>
To: "David E. Wheeler" <david@justatheory.com>, Erik Rijkers <er@xs4all.nl>
Cc: pgsql-hackers@lists.postgresql.org
Message-ID: <164182692.82468.1694897413058@office.mailbox.org>
In-Reply-To: <B5F73DF7-A0CF-4EA2-AF5D-800BFEA67D47@justatheory.com>
References: <15DD78A5-B5C4-4332-ACFE-55723259C07F@justatheory.com>
 <133696180.303713.1694566807910@office.mailbox.org>
 <729CC2EA-9194-459C-B2A6-BC660F62E6A9@justatheory.com>
 <973d6495-cf28-4d06-7d46-758bd2615e34@xs4all.nl>
 <B5F73DF7-A0CF-4EA2-AF5D-800BFEA67D47@justatheory.com>
Subject: Re: JSON Path and GIN Questions
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit
Importance: Normal
Archived-At: <https://www.postgresql.org/message-id/164182692.82468.1694897413058%40office.mailbox.org>
Precedence: bulk

On 16/09/2023 22:19 CEST David E. Wheeler <david@justatheory.com> wrote:

> On Sep 15, 2023, at 23:59, Erik Rijkers <er@xs4all.nl> wrote:
>
> > movie @? '$ ?($.year >= 2023)'
> >
> > I believe it is indeed not possible to have such a unequality-search use
> > the GIN index.  It is another weakness of JSON that can be unexpected to
> > those not in the fullness of Knowledge of the manual. Yes, this too would
> > be good to explain in the doc where JSON indexes are explained.
>
> Is that a limitation of GIN indexes in general? Or could there be opclass
> improvements in the future that would enable such comparisons?

This detail is mentioned in docs [1]:

"For these operators, a GIN index extracts clauses of the form
 **accessors_chain = constant** out of the jsonpath pattern, and does the
 index search based on the keys and values mentioned in these clauses."

I don't know if this is a general limitation of GIN indexes or just how these
operators are implemented right now.

[1] https://www.postgresql.org/docs/current/datatype-json.html#JSON-INDEXING

--
Erik