Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1h5YvX-00014N-Sd for pgsql-hackers@arkaria.postgresql.org; Sun, 17 Mar 2019 16:46:24 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1h5YvU-0003MW-RE for pgsql-hackers@arkaria.postgresql.org; Sun, 17 Mar 2019 16:46:20 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1h5YvU-0003GI-7H for pgsql-hackers@lists.postgresql.org; Sun, 17 Mar 2019 16:46:20 +0000 Received: from meldrar.postgresql.org ([2a02:c0:301:0:ffff::31]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1h5YvR-0000du-9h for pgsql-hackers@postgresql.org; Sun, 17 Mar 2019 16:46:19 +0000 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=postgresql.org; s=20171124; h=Content-Type:In-Reply-To:MIME-Version:Date: Message-ID:From:References:Cc:To:Subject:Sender:Reply-To: Content-Transfer-Encoding:Content-ID:Content-Description; bh=/fAg/34+ndNCdHZG6Tagfl0WDd3e9+qXfwk9gnMHPKc=; b=RVtpr+Mw5AM7/G/ljWXy0BSr0j tw1Jp7ko8g7oHlQVj36v87YqYKdVIeODq3ymeail4p3SqCsRs23/WF6g6FVE9hPSBmnc7XLpH4N7D dpioMWkcV+cFc0pthbnuy9t/aFspRIlcxFDbzxrHe1cShlT0YqePvRlzXM/Lb2oCjuhQJjgfgjj6p kPbPjnokfVtl1t6md142taGHKRe6VpYkVFnxX4gUd6HXtfhC0KB4nFU6DuqZ8faMr+d63Is0A05iJ bzlmPk0p4KEH2WmGYbkjTokvjId/MWP8kDXNPtN0bXtpHhB1qS10yvshZkiBRba1BS31cvBY+O1Q1 epNNqd1w==; Received: from pool-96-250-83-19.nycmny.fios.verizon.net ([96.250.83.19] helo=Ph33r-Retina.home) by meldrar.postgresql.org with esmtpsa (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1h5YvL-0005Xz-Ry; Sun, 17 Mar 2019 16:46:14 +0000 Subject: Re: jsonpath To: Alexander Korotkov , Pavel Stehule Cc: Nikita Glukhov , Tomas Vondra , Andres Freund , Oleg Bartunov , Michael Paquier , Stas Kelvich , PostgreSQL-development , Thomas Munro , David Steele , Tom Lane , Andrew Dunstan , Robert Haas References: <20190130022826.of7w6xv5lbvzqxfp@alap3.anarazel.de> <957ee2ff-f9a6-0ba1-13e2-9c4d320f63ac@2ndquadrant.com> <67128b8b-2d80-9e51-20a8-9ac8e0e2243e@2ndquadrant.com> <220821fb-52c0-c277-0959-322c8eef76b2@postgrespro.ru> From: "Jonathan S. Katz" Openpgp: preference=signencrypt Autocrypt: addr=jkatz@postgresql.org; keydata= mQINBFtShwABEAC7PNHDUOTYuifpUCk23KqfxdQQkn4nkoxOXRK0+rAj36FiwqGB4TJFuOVZ sDFAEiSlC8Jt4y5Cs7B5tetT8JNd2cs6zp/udMJJDz9d65O9PDpdlMgAmIiTzpLlSdx8FG56 DTksaDv1d8j3cTJPSE4/fWSxqzA7o3Y9UuL7atZPrzfImgpRKs0of7elIHwOa8GucjyhYqcR h60wFBJc2KXqQdDYRTZy43DSnY/0VNc0omiH355fustvpm+m5HjD3w7qZyfN3fpKJpnX1LCF f3MnPHaDGITIYGRCBXvf0UqUtD6OEVWPv2C2gyqWMIpWmZTOgDufltKyIByKBoS9x0PlFkij 04X3KODCngt+N8Ssc9OICc6QSxhjoP48PYPdmiTmkrGuf0LX084wj1xeo1NX7XxZK39F6dTJ DhsIiW0sNS0xMxQHLHG9VLbPjx3SANQBh6BuryPz5ZupW9/TIDmkvprtU/oXfKgtfYm3fxmk EctxbWrEPsFTFPyuMqQu6l+xyQv0s1VLZfjNWaua6H1/gGoIt6kRnn5qMXDVVpijuWkHbv7G ngaQMd258UrrOEHnnjzhQ7jxMWV9D+emxbAtlIxnYvCWlV4IwAQhEHfvudqYaIY3hNWrvQ6H GB2KXoTZYN9g5djm14/5nj1IU5zOcovkjJnKhoo9iStnpFF2cwARAQABtCdKb25hdGhhbiBT LiBLYXR6IDxqa2F0ekBwb3N0Z3Jlc3FsLm9yZz6JAlQEEwEIAD4CGwMFCQeGH4AFCwkIBwIG FQoJCAsCBBYCAwECHgECF4AWIQT6hLaVryv3miBkP/HxBJxynxxlJwUCW1KJJQAKCRDxBJxy nxxlJwjrD/kBgqsW4QpNpTFw7ifRokZV08CCX4huPBJQ91rrv+UEWlEcotFBHVkYyHnpzARl tcZxhJ9CbFxjniH9cOTty5T/O1yolbOHtZSW8Z8aWV6BVEbjMb+BFxSSLm7RnvJdzQbGCZq2 ZZvfVpB6z3EHYph4KDdVKvMFjoLskxmdS1DE0tE3zTxvoQsi24Q+HOS07kUjs6fsu/WICMfz mgO++AWG9Y0CvN0mm4TkujESzyKM9E5irD+leEMIcddl51Aa2c/VMfBXQbRmpHIgUFTmuHQD CnQih+9i3OJAksDg66SP8a7yiXv5mwvyDi1EfTGVKYR2j+pwyjwnC3oIbvDMmB3uTn2JIjnT iZKPVtAcAylXjubFltihQgNyuShdP4W+kBwZizhUFqUVL8Anx+KoytYmJPfMRFLGuK4obXKq a2ZS3k9KB+H+isOx2nFJOsc7V360Zp1DVaNmuiK10TT6QndShSPaqkJqFtCb6r92rZ9sZM/L 3vtCI4Rrl3Pt1MgtENXupS8gZpJnAYS0j5A1PAZ09r6ANoaeMHspF+5J5fOHeEvqphXr36mm a83Vl1t4orPb0+QmmijmlpseDU63M88Aw5p3c4qj7t8Qr2EZ5zrn7/sFn5wOfbs8Nymxafif QCnlV2vg9p0m7vSk/yLJ4PFZvs52FgqAGRCdRn0s2EC99bkCDQRbUocAARAAv8ho/toQ9DG3 j4f9h9n1aRHr2FlviN2Utpy6L8+dfDggO0geilmkGQOolZ2E60gGfye/kUtF9W3NByO4hxDR 9u6qbOXcdqnuA+cc68EfqlWFJrVtYFxt0h4ElWYOYnIezKthriWch/FY70FGrxs3z8UHOHq5 0wBW433eTvZm90WixBiXEt2v1DgW4Vr3ymfO7Aap/IYyPuE4JzgudAuAl0HKPyNEHWHG1dAb jX1RiCw9gknIDWQOF0B4UAaJctWGVcnZ3A2ULwNGMa1P9ZJlBWf1vcj01aiHMU0yQ7JjJiSp vfm9eM0uSLwRdDrJjyl5ZZqVumjdv2SMNQ8GvYRbEMys3GGDSt9zXgfCSUnPnJfYxjzBHRI6 x44Wfsx8S6hWxepOogCJJ/g67Bk9mY8YV4klWIXDJVOL5jnBC09DbsZG81JaE2QxB8Y7W36Z Mroi9XMxg3s805hQAQUvdG/poU8hN8BWdrnTm/+4eQQp7gDY1ePDmGM6bJC+OHOSnFtR/f+7 0zpKJ10cc7cBygGnl1yR3KjhFyAWUFvP4ZGziKCcpMwXZfe9PGuyA/YOubMphxIn3YsK2wrd faKZYX2GMZCZhMMcvx9IpQrxIJgU+VlwXu/O+Lk10VIPcxPJJwmpdI6HzcS8ZgG6IMcC444X XTuLaP8j2mgcMvYak3ScCykAEQEAAYkCPAQYAQgAJhYhBPqEtpWvK/eaIGQ/8fEEnHKfHGUn BQJbUocAAhsMBQkHhh+AAAoJEPEEnHKfHGUnReMP/RA2UhGQj+G4uBshkRLjpRysabdPqgQB dEBk6wYbio88Wg/2/hgY7UzmDDEwX3sZfQDcrI6+vIobI8uqstZID+WgAAa1JLfChMyVQnSy 0zfWMOABXscc5tGuvFRZvJklTissMFjXUwaREEKp4ZikTvJ/62MCjSdtrUhnPLvoTsHTKRKD ichE+b5A54alwsubTijw12O6N22r5IjZiiSZV0u7dsShyKw+7wCSax9fuBoE43NMYf+dnjMK nerAQYUcZWYMnk+EC8RaqYAxv6XZ2tKx1AkGGktwkQIBwrz9IlDSvJ3LWJ2UIIuLRTdngNgg GIL4zzuUa2F56FqskQIuYMaNETk6LYfalBDQ6TVLAcgCPQxp4k4i/PRsZ3lZ2ZhRHRYciOvm kp+I6EfHwllQpYrWs1thluBGqlJSVJgKl0IOFvKLsQ6KKfqzAwh6FxrO5qajp4viNIgtWoFw O5Bp0jgFTbH2OrMWIRfUdUCH1Djbuo65svhNj7FNsQVYzHDI+Nd1I/LOBoPc3UorRMF2M4JV kUR7skOHWUbPTSNUr7qc14NSMY3PKjGeVGAsBVHBPvmRx/Ss2tW/TpJWpxg4pmquFQSXuaYh Yf4FN8Sxy320pcr+FqN8AhZkYJcNY82OTtR5VEKRC/mYcyq61qXIKqngydn79bJjGxHipxyF FmEC Message-ID: <04981bad-2156-a927-b04c-50ff2c9709ae@postgresql.org> Date: Sun, 17 Mar 2019 12:46:01 -0400 User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.12; rv:60.0) Gecko/20100101 Thunderbird/60.5.3 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/signed; micalg=pgp-sha256; protocol="application/pgp-signature"; boundary="dBjcnGt5YyLVSZi55UIANIejfyaze04CL" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is an OpenPGP/MIME signed message (RFC 4880 and 3156) --dBjcnGt5YyLVSZi55UIANIejfyaze04CL Content-Type: multipart/mixed; boundary="gNgNCCw0OICTkRIgMOlflI9yLM1oB8FIo"; protected-headers="v1" From: "Jonathan S. Katz" To: Alexander Korotkov , Pavel Stehule Cc: Nikita Glukhov , Tomas Vondra , Andres Freund , Oleg Bartunov , Michael Paquier , Stas Kelvich , PostgreSQL-development , Thomas Munro , David Steele , Tom Lane , Andrew Dunstan , Robert Haas Message-ID: <04981bad-2156-a927-b04c-50ff2c9709ae@postgresql.org> Subject: Re: jsonpath References: <9f5f1247-29f4-17cd-edd9-38e9bcd95b14@postgrespro.ru> <20190130022826.of7w6xv5lbvzqxfp@alap3.anarazel.de> <957ee2ff-f9a6-0ba1-13e2-9c4d320f63ac@2ndquadrant.com> <67128b8b-2d80-9e51-20a8-9ac8e0e2243e@2ndquadrant.com> <220821fb-52c0-c277-0959-322c8eef76b2@postgrespro.ru> In-Reply-To: --gNgNCCw0OICTkRIgMOlflI9yLM1oB8FIo Content-Type: text/plain; charset=utf-8 Content-Language: en-US Content-Transfer-Encoding: quoted-printable On 3/17/19 3:13 AM, Alexander Korotkov wrote: > On Sat, Mar 16, 2019 at 9:39 PM Pavel Stehule = wrote: >> so 16. 3. 2019 v 10:36 odes=C3=ADlatel Alexander Korotkov napsal: >>> >>> On Thu, Mar 14, 2019 at 12:07 PM Alexander Korotkov >>> wrote: >>>> On Sun, Mar 10, 2019 at 1:51 PM Alexander Korotkov >>>> wrote: >>>>> On Wed, Mar 6, 2019 at 12:40 AM Nikita Glukhov wrote: >>>>>> Attached 36th version of the patches. >>>>> >>>>> Thank yo for the revision! >>>>> >>>>> In the attached revision following changes are made: >>>>> >>>>>> "unknown" refers here to ordinary three-valued logical Unknown, wh= ich is >>>>>> represented in SQL by NULL. >>>>>> >>>>>> JSON path expressions return sequences of SQL/JSON items, which ar= e defined by >>>>>> SQL/JSON data model. But JSON path predicates (logical expression= s), which are >>>>>> used in filters, return three-valued logical values: False, True, = or Unknown. >>>>> >>>>> * I've added short explanation of this to the documentation. >>>>> * Removed no longer present data structures from typedefs.list of = the >>>>> first patch. >>>>> * Moved GIN support patch to number 3. Seems to be well-isolated = and >>>>> not very complex patch. I propose to consider this to 12 too. I >>>>> added high-level comment there, commit message and made some code >>>>> beautification. >>>> >>>> I think patches 1 and 2 are in committable shape (I reached Tomas >>>> off-list, he doesn't have more notes regarding them). While patch 3= >>>> requires more review. >>>> >>>> I'm going to push 1 and 2 if no objections. >>> >>> So, pushed. Many thanks to reviewers and authors! >>> >>> Remaining part I'm proposing for 12 is attached. I appreciate review= of it. >> >> >> I tested this patch and I didn't find any issue - just I tested basic= functionality and regress tests. >> >> looks well >=20 > Thank you for your feedback! Like Pavel, I did some basic testing of the patch (on current HEAD 4178d8b91c) trying out various JSON path expressions, and yes, it all worked. I had a brief scare while testing on 4178d8b91c where initdb was failing on the bootstrapping step, but after doing a thorough wipe of build files and my output directory, it seems to be initializing okay. I also did some testing of the GIN patch upthread, as the quickness of retrieval of the data using JSON path is of course important as well. Using a schema roughly like this: CREATE TABLE news_feed ( id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, data jsonb NOT NULL ); CREATE INDEX news_feed_data_gin_idx ON news_feed USING gin(data); I loaded in a data set of roughly 420,000 rows. Each row had all the same keys but differing values (e.g. "length" and "content" as keys) I tested a few different JSON path scenarios. Some of the index scans performed way better than the equivalent sequential scans, for instance: SELECT count(*) FROM news_feed WHERE data @? '$.length ? (@ =3D=3D 200)'; SELECT * FROM news_feed WHERE data @? '$.id ? (@ =3D=3D "22613cbc-d83e-4a29-8b59-3b9f5cd61825")';= Using the index outperformed the sequential scan (and parallel seq scan) by ~10-100x based on my config + laptop hardware! However, when I did something a little more complex, like the below: SELECT count(*) FROM news_feed WHERE data @? '$.length ? (@ < 150)'; SELECT count(*) FROM news_feed WHERE data @? '$.content ? (@ like_regex "^Start")'; SELECT id, jsonb_path_query(data, '$.content') FROM news_feed WHERE data @? '$.content ? (@ like_regex "risk" flag "i")'; I would find that the index scan performed as well as the sequential scan. Additionally, on my laptop, the parallel sequential scan would beat the index scan by ~2.5x in some cases. Reading up on what the GIN patch does, this all makes sense: it's optimized for equality, I understand there are challenges to be able to handle inequality, regex exps, etc. And the cases where it really does work well, it's _incredibly_ fast. My suggestion would be adding some additional guidance in the user documentation around how GIN works with the @@ and @? operators so they can understand where GIN will work very well with JSON path + their data and not be surprised when other types of JSON path queries are performing on par with a sequential scan (or worse than a parallel seq scan). Thanks, Jonathan --gNgNCCw0OICTkRIgMOlflI9yLM1oB8FIo-- --dBjcnGt5YyLVSZi55UIANIejfyaze04CL Content-Type: application/pgp-signature; name="signature.asc" Content-Description: OpenPGP digital signature Content-Disposition: attachment; filename="signature.asc" -----BEGIN PGP SIGNATURE----- iQIzBAEBCAAdFiEE+oS2la8r95ogZD/x8QSccp8cZScFAlyOecoACgkQ8QSccp8c ZSdhzg//YHze1xus5X8+5gkIPgRKqpn8OcVt11im/GtYr+dmUrTEoUvfXfO5oTCX Co8dYlgTVsvERUpJbkaTp557GhG2efjVA1d7mEHxO8/KUPPVotKCLoXOM7n/1Gmp WAJZMld8PfaOQvhpye//KCbciTamjA2ZJtgN/f/Y3Deblc6HkMbaJ4DVS58NHPO2 e0/uyLUzEVxUmSSgXf9zx+7DIYEg2sDX1FN2YHDrvU0tWzRtFT171gt0TZN3e1jT ZlFDPvxVCx10sRKhLw1eRZqfvjVn9I/mFeDOS5C06bg/pgqSWb8z0AGUjKaCwL6G R9ML2Nj2GriRTKp5Fx8UH25GFfuJ7oub+66Kx85bnMX4bmcuyxGHTyyfeA/2GYmi m0JYKfSRCtrRcwULalfmd8FiT5+2zjrSOFkf4jCsWeO8L/g5nD1yTxp1p4dKbEwV taOZCMYEMvDA5gjvveDgzn8dVatSRrf3KK5puze68Odf1PPpExK+V5cp8zWT0FO4 T3zdoXMv4TRYjVGt1V7iY30gbhpClu+yM1BbhKitum/bmUo2CtGlvqlvO4LTGaz4 jRInLON6QrPxrF2nlolz7xKkrZsaGEYQzOk9h6ZtXAtiWQlvfTeuvIj9t0Scqd/s 7rjvjzMUzyAPs62fvJaNuUPDC+AeZnBgdWCLPXCkWehQza8zElA= =HXir -----END PGP SIGNATURE----- --dBjcnGt5YyLVSZi55UIANIejfyaze04CL--