Op 9/15/23 om 22:27 schreef David E. Wheeler:
On Sep 12, 2023, at 21:00, Erik Wienhold <e...@ewie.name> wrote:
That's also my understanding. We had a discussion about the docs on @@, @?, and
jsonb_path_query on -general a while back [1]. Maybe it's useful also.
Okay, I’ll take a pass at expanding the docs on this. I think a little
mini-tutorial on these two operators would be useful.
Meanwhile, I’d like to re-up this question about the index qualification of
non-equality JSON Path operators.
On Sep 12, 2023, at 20:16, David E. Wheeler <da...@justatheory.com> wrote:
Issue 3: Index Use for Comparison
---------------------------------
From the docs
(https://www.postgresql.org/docs/current/datatype-json.html#JSON-INDEXING), I had
assumed any JSON Path query would be able to use the GIN index. However while the
use of the == JSON Path operator is able to take advantage of the GIN index,
apparently the >= operator cannot:
david=# explain analyze select id from movies where movie @? '$ ?($.year >=
2023)';
QUERY PLAN
---------------------------------------------------------------------------------------------------------
Seq Scan on movies (cost=0.00..3741.41 rows=366 width=4) (actual
time=34.815..36.259 rows=192 loops=1)
Filter: (movie @? '$?($."year" >= 2023)'::jsonpath)
Rows Removed by Filter: 36081
Planning Time: 1.864 ms
Execution Time: 36.338 ms
(5 rows)
Is this expected? Originally I tried with json_path_ops, which I can understand
not working, since it stores hashes of paths, which would allow only exact
matches. But a plain old GIN index doesn’t appear to work, either. Should it? Is
there perhaps some other op class that would allow it to work? Or would I have to
create a separate BTREE index on `movie -> 'year'`?
movie @? '$ ?($.year >= 2023)'
I believe it is indeed not possible to have such a unequality-search use
the GIN index. It is another weakness of JSON that can be unexpected to
those not in the fullness of Knowledge of the manual. Yes, this too
would be good to explain in the doc where JSON indexes are explained.
Erik Rijkers
Thanks,
David
