Message-ID: <973d6495-cf28-4d06-7d46-758bd2615e34@xs4all.nl>
Date: Sat, 16 Sep 2023 05:59:26 +0200
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101
 Thunderbird/102.11.0
Subject: Re: JSON Path and GIN Questions
Content-Language: en-US
To: "David E. Wheeler" <david@justatheory.com>, Erik Wienhold <ewie@ewie.name>
Cc: pgsql-hackers@lists.postgresql.org
References: <15DD78A5-B5C4-4332-ACFE-55723259C07F@justatheory.com>
 <133696180.303713.1694566807910@office.mailbox.org>
 <729CC2EA-9194-459C-B2A6-BC660F62E6A9@justatheory.com>
From: Erik Rijkers <er@xs4all.nl>
In-Reply-To: <729CC2EA-9194-459C-B2A6-BC660F62E6A9@justatheory.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/973d6495-cf28-4d06-7d46-758bd2615e34%40xs4all.nl>
Precedence: bulk

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