Re: JSON Path and GIN Questions

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