public inbox for [email protected]  

help / color / mirror / Atom feed

From: Erik Rijkers <[email protected]>
To: David E. Wheeler <[email protected]>
Cc: Erik Wienhold <[email protected]>
Cc: [email protected]
Subject: Re: JSON Path and GIN Questions
Date: Thu, 14 Sep 2023 06:04:28 +0200
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>

p 9/13/23 om 22:01 schreef David E. Wheeler:
> On Sep 13, 2023, at 01:11, Erik Rijkers <[email protected]> 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

view thread (2+ messages)

reply

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: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected]
  Subject: Re: JSON Path and GIN Questions
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox