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'`? Thanks, David
signature.asc
Description: Message signed with OpenPGP
