Message-ID: <aab40650-d292-3ece-e398-af2da8888e08@xs4all.nl>
Date: Thu, 14 Sep 2023 06:04:28 +0200
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101
 Thunderbird/102.11.0
Subject: Re: JSON Path and GIN Questions
To: "David E. Wheeler" <david@justatheory.com>
Cc: Erik Wienhold <ewie@ewie.name>, pgsql-hackers@lists.postgresql.org
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>
Content-Language: en-US
From: Erik Rijkers <er@xs4all.nl>
In-Reply-To: <C4BBA1CB-9EA1-4124-8CAD-8DDD646C453F@justatheory.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/aab40650-d292-3ece-e398-af2da8888e08%40xs4all.nl>
Precedence: bulk

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."
>>
>> That sentence is missing from the documentation.
> 
> 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?
> 
> D

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.
Compare these two statements:

select count(id) from movies where
movie @? '$ ? (@.year == 2023)'
Time: 1.259 ms
   (index used)

select count(id) from movies where
jsonb_path_match(movie, '$.year == 2023');
Time: 17.260 ms
   (no index used - unexpectedly slower)

With these two indexes available:
   using gin (movie);
   using gin (movie jsonb_path_ops);

(REL_15_STABLE; but it's the same in HEAD and
the not-yet-committed SQL/JSON patches.)

Erik Rijkers