Re: JSON Path and GIN Questions

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Erik Rijkers <er(at)xs4all(dot)nl>
Cc: "David E(dot) Wheeler" <david(at)justatheory(dot)com>, Erik Wienhold <ewie(at)ewie(dot)name>, pgsql-hackers(at)lists(dot)postgresql(dot)org
Subject: Re: JSON Path and GIN Questions
Date: 2023-09-14 04:41:58
Message-ID: 3063408.1694666518@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

>> Where did that come from? Why wouldn’t JSON* functions use indexes? I see that the docs only mention operators; why would the corresponding 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-INDEXING

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Dilip Kumar 2023-09-14 05:07:20 Re: [PoC] pg_upgrade: allow to upgrade publisher node
Previous Message Michael Paquier 2023-09-14 04:33:39 Re: Have better wording for snapshot file reading failure