Content-Type: multipart/signed;
	boundary="Apple-Mail=_0F412960-2E89-48F6-8D2A-FB825E4B1ECB";
	protocol="application/pgp-signature";
	micalg=pgp-sha256
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3731.700.6\))
Subject: Re: JSON Path and GIN Questions
From: "David E. Wheeler" <david@justatheory.com>
In-Reply-To: <133696180.303713.1694566807910@office.mailbox.org>
Date: Fri, 15 Sep 2023 16:27:37 -0400
Cc: pgsql-hackers@lists.postgresql.org
Message-Id: <729CC2EA-9194-459C-B2A6-BC660F62E6A9@justatheory.com>
References: <15DD78A5-B5C4-4332-ACFE-55723259C07F@justatheory.com>
 <133696180.303713.1694566807910@office.mailbox.org>
To: Erik Wienhold <ewie@ewie.name>
Archived-At: <https://www.postgresql.org/message-id/729CC2EA-9194-459C-B2A6-BC660F62E6A9%40justatheory.com>
Precedence: bulk


--Apple-Mail=_0F412960-2E89-48F6-8D2A-FB825E4B1ECB
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain;
	charset=utf-8

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=E2=80=99ll take a pass at expanding the docs on this. I think a =
little mini-tutorial on these two operators would be useful.

Meanwhile, I=E2=80=99d 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
> ---------------------------------
>=20
> =46rom 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 =3D=3D JSON Path operator is able to take =
advantage of the GIN index, apparently the >=3D operator cannot:
>=20
> david=3D# explain analyze select id from movies where movie @? '$ =
?($.year >=3D 2023)';
>                                               QUERY PLAN               =
                       =
--------------------------------------------------------------------------=
-------------------------------
> Seq Scan on movies  (cost=3D0.00..3741.41 rows=3D366 width=3D4) =
(actual time=3D34.815..36.259 rows=3D192 loops=3D1)
>   Filter: (movie @? '$?($."year" >=3D 2023)'::jsonpath)
>   Rows Removed by Filter: 36081
> Planning Time: 1.864 ms
> Execution Time: 36.338 ms
> (5 rows)
>=20
> 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=E2=80=99t =
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


--Apple-Mail=_0F412960-2E89-48F6-8D2A-FB825E4B1ECB
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
	filename=signature.asc
Content-Type: application/pgp-signature;
	name=signature.asc
Content-Description: Message signed with OpenPGP

-----BEGIN PGP SIGNATURE-----

iQIzBAEBCAAdFiEEd+KfmbOQis6ZycGGkt9idPjIgfEFAmUEvjkACgkQkt9idPjI
gfFWNw/+OYDVMubUJVGiSqD2fKAowo+/MWN2g/4CiC3JvGKkQ5SIiggjt6LLH92H
3Q72Aa1oLg2U/P2ME73V/Axq2/xYLd+eQJ7QWoaIzga939Sz5DgyPuZg9ld4AFbl
dwQvk6Q4C+2MW7ZNkATA1pU51OHOaL7sWZMXqcmJz2xJBGVaN93jIPQ1aBa9y6iJ
b7MoGjATw3Pev5juVLZqV7yK1GbMzPIOdqoLzEK53e/Cg0EEO0wr7jippumgZP9e
r8gbfRHrLqqpNUOT1amkcRk85ny/l+u7DAuy97Lh09HsMyuv0c7Fw1IEMurzRtSz
wd6PA8O55pXwKyNn0hfycQAvFomOk4tJJxiAXF/WxbgvoGEjsbu4mApPjFNfFrnp
ZPfyCq523gpUPNaOPqFEMdAAZNBI1lbp6Trs7nzdnKduTiqKquN6RNB0omiI1mFJ
etaTt6y1Trkp0RXCYWd76bmMFSSIyz5ajgK0YszMHMcILY6/xoBzCbl25GSQfYLH
k+arQY6BQCpMFLdNkJeAUfHPbnX2o0AXRqdUf2bl718/gSmnHma0OZgikXDjfVrj
XAj/VWsc0x0NuedDtpVNaE7H1CaShPUMHxYQRw5jn1oEmjKgs2uaBmuxQqem9YdT
MetBic2tgezRsvkedr6uAwZfkTbzETWtDOxWbwrZEYSY6c9/Prc=
=r7Sf
-----END PGP SIGNATURE-----

--Apple-Mail=_0F412960-2E89-48F6-8D2A-FB825E4B1ECB--