From: Tom Lane <tgl@sss.pgh.pa.us>
To: Erik Rijkers <er@xs4all.nl>
cc: "David E. Wheeler" <david@justatheory.com>, Erik Wienhold <ewie@ewie.name>,
        pgsql-hackers@lists.postgresql.org
Subject: Re: JSON Path and GIN Questions
In-reply-to: <aab40650-d292-3ece-e398-af2da8888e08@xs4all.nl>
References: <15DD78A5-B5C4-4332-ACFE-55723259C07F@justatheory.com> <133696180.303713.1694566807910@office.mailbox.org> <640942d7-c996-0562-62c9-d69a4c1f9b1e@xs4all.nl> <C4BBA1CB-9EA1-4124-8CAD-8DDD646C453F@justatheory.com> <aab40650-d292-3ece-e398-af2da8888e08@xs4all.nl>
Comments: In-reply-to Erik Rijkers <er@xs4all.nl>
	message dated "Thu, 14 Sep 2023 06:04:28 +0200"
MIME-Version: 1.0
Content-Type: text/plain; charset="UTF-8"
Content-ID: <3063407.1694666518.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Thu, 14 Sep 2023 00:41:58 -0400
Message-ID: <3063408.1694666518@sss.pgh.pa.us>
Archived-At: <https://www.postgresql.org/message-id/3063408.1694666518%40sss.pgh.pa.us>
Precedence: bulk

Erik Rijkers <er@xs4all.nl> writes:
> p 9/13/23 om 22:01 schreef David E. Wheeler:
>> On Sep 13, 2023, at 01:11, Erik Rijkers <er@xs4all.nl> wrote:
>>> "All use of json*() functions preclude index usage."

>> Where did that come from? Why wouldn=E2=80=99t JSON* functions use inde=
xes? I see that the docs only mention operators; why would the correspondi=
ng functions behave the same?

> Sorry, perhaps my reply was a bit off-topic.
> But you mentioned perhaps touching the docs and
> the not-use-of-index is just so unexpected.

Unexpected to who?  I think the docs make it pretty plain that only
operators on indexed columns are considered as index qualifications.
Admittedly, 11.2 Index Types [1] makes the point only by not
discussing any other case, but when you get to 11.10 Operator Classes
and Operator Families [2] and discover that the entire index definition
mechanism is based around operators not functions, you should be able
to reach that conclusion.  The point is made even more directly in
38.16 Interfacing Extensions to Indexes [3], though I'll concede
that that's not material I'd expect the average PG user to read.
As far as json in particular is concerned, 8.14.4 jsonb Indexing [4]
is pretty clear about what is or is not supported.

			regards, tom lane

[1] https://www.postgresql.org/docs/current/indexes-types.html
[2] https://www.postgresql.org/docs/current/indexes-opclass.html
[3] https://www.postgresql.org/docs/current/xindex.html
[4] https://www.postgresql.org/docs/current/datatype-json.html#JSON-INDEXI=
NG