public inbox for [email protected]  

help / color / mirror / Atom feed

From: Erik Rijkers <[email protected]>
To: David E. Wheeler <[email protected]>
To: Erik Wienhold <[email protected]>
Cc: [email protected]
Subject: Re: JSON Path and GIN Questions
Date: Sat, 16 Sep 2023 05:59:26 +0200
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
	<[email protected]>
	<[email protected]>

Op 9/15/23 om 22:27 schreef David E. Wheeler:
> On Sep 12, 2023, at 21:00, Erik Wienhold <[email protected]> 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 <[email protected]> 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
> 

view thread (11+ messages)  latest in thread

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