public inbox for [email protected]
help / color / mirror / Atom feedFrom: David E. Wheeler <[email protected]>
To: Erik Wienhold <[email protected]>
Cc: [email protected]
Subject: Re: JSON Path and GIN Questions
Date: Fri, 15 Sep 2023 16:27:37 -0400
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
<[email protected]>
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'`?
Thanks,
David
Attachments:
[application/pgp-signature] signature.asc (833B, ../[email protected]/2-signature.asc)
download
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]
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