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
wouldthe 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