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 1h5Z9O-0001gb-Tc for pgsql-hackers@arkaria.postgresql.org; Sun, 17 Mar 2019 17:00:43 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1h5Z9N-0000Pq-Dr for pgsql-hackers@arkaria.postgresql.org; Sun, 17 Mar 2019 17:00:41 +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 1h5Z9N-0000PY-59 for pgsql-hackers@lists.postgresql.org; Sun, 17 Mar 2019 17:00:41 +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 1h5Z9K-0000yE-Ey for pgsql-hackers@postgresql.org; Sun, 17 Mar 2019 17:00:40 +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=sGXGxFPriFD9g3YNm3awx+sKLAMDagggZNnxVvWUDTc=; b=6mnJK2gmm7c54bYPpBl9OgUTsv SVy74Sa+7HwH8HeDP/IO38lzjX7SC1fDtJxuESGj4wiFkgm8NBy2pdi21nUxA7SKTq2D3DRSyxRrZ MndT9kiRe+bYHYzJPRnP2YGpVqD72B+6sxXz9eLdBz83qN7xCCUCUVFUsVpvg1FrDzalKFrlouYbY QZ0Uw4rG0fpnaqte+xd5b1mZFSy95BLcTKRQVb6OwrkUhK46AYyjYqFJH/sMnknQ13fBiDf9LYqA3 /t8KpbMTxYh/4VyXm2/9GHTOhp5elUjmbgMiIq7+9GSBOYaerwqRTnySfxUkfPqPdbpZ7/4gpAOyH Fx6ufFSg==; 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 1h5Z9G-0006Gy-Go; Sun, 17 Mar 2019 17:00:36 +0000 Subject: Re: jsonpath To: Alexander Korotkov Cc: Pavel Stehule , 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: <957ee2ff-f9a6-0ba1-13e2-9c4d320f63ac@2ndquadrant.com> <67128b8b-2d80-9e51-20a8-9ac8e0e2243e@2ndquadrant.com> <220821fb-52c0-c277-0959-322c8eef76b2@postgrespro.ru> <04981bad-2156-a927-b04c-50ff2c9709ae@postgresql.org> 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: <3247f0b1-ad4a-2d4e-e9b1-1eedc1e7e81a@postgresql.org> Date: Sun, 17 Mar 2019 13:00:29 -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="wO3vXP1fbC6CghWbE0N7uDFdy2Y49Pqe6" 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) --wO3vXP1fbC6CghWbE0N7uDFdy2Y49Pqe6 Content-Type: multipart/mixed; boundary="OM3lBZ5McyfsERNKSrRbWWa42vZsKmtHd"; protected-headers="v1" From: "Jonathan S. Katz" To: Alexander Korotkov Cc: Pavel Stehule , 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: <3247f0b1-ad4a-2d4e-e9b1-1eedc1e7e81a@postgresql.org> Subject: Re: jsonpath 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> <04981bad-2156-a927-b04c-50ff2c9709ae@postgresql.org> In-Reply-To: --OM3lBZ5McyfsERNKSrRbWWa42vZsKmtHd Content-Type: text/plain; charset=utf-8 Content-Language: en-US Content-Transfer-Encoding: quoted-printable On 3/17/19 12:55 PM, Alexander Korotkov wrote: >=20 >> 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. >=20 > Yeah, this cases are not supported. Did optimizer automatically > select sequential scan in this case (if not touching enable_* > variables)? It should, because optimizer understands that GIN scan > will be bad if extract_query method failed to extract anything. It did not - it was doing a bitmap heap scan. I have default costs setup. Example output from EXPLAIN ANALYZE with the index available: Aggregate (cost=3D1539.78..1539.79 rows=3D1 width=3D8) (actual time=3D270.419..270.419 rows=3D1 loops=3D1) -> Bitmap Heap Scan on news_feed (cost=3D23.24..1538.73 rows=3D418 width=3D0) (actual time=3D84.040..270.407 rows=3D5 loops=3D1) Recheck Cond: (data @? '$."length"?(@ < 150)'::jsonpath) Rows Removed by Index Recheck: 418360 Heap Blocks: exact=3D28690 -> Bitmap Index Scan on news_feed_data_gin_idx (cost=3D0.00..23.14 rows=3D418 width=3D0) (actual time=3D41.788..41.788 rows=3D418365 loops=3D1) Index Cond: (data @? '$."length"?(@ < 150)'::jsonpath) Planning Time: 0.168 ms Execution Time: 271.105 ms And for arguments sake, after I dropped the index (and max_parallel_workers =3D 8): Finalize Aggregate (cost=3D30998.07..30998.08 rows=3D1 width=3D8) (actu= al time=3D91.062..91.062 rows=3D1 loops=3D1) -> Gather (cost=3D30997.65..30998.06 rows=3D4 width=3D8) (actual time=3D90.892..97.739 rows=3D5 loops=3D1) Workers Planned: 4 Workers Launched: 4 -> Partial Aggregate (cost=3D29997.65..29997.66 rows=3D1 width= =3D8) (actual time=3D76.977..76.977 rows=3D1 loops=3D5) -> Parallel Seq Scan on news_feed (cost=3D0.00..29997.39= rows=3D104 width=3D0) (actual time=3D39.736..76.964 rows=3D1 loops=3D5) Filter: (data @? '$."length"?(@ < 150)'::jsonpath) Rows Removed by Filter: 83672 Planning Time: 0.127 ms Execution Time: 97.801 ms >> 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 t= o >> handle inequality, regex exps, etc. And the cases where it really does= >> work well, it's _incredibly_ fast. >=20 > Yes, for more complex cases, we need different opclasses. For > instance, we can consider porting jsquery opclasses to PG 13. And it > become even more important to get parametrized opclasses, because we > don't necessary want to index all the json fields in this same way. > That's another challenge for future releases. But what we have now is > just support for some of jsonpathes for existing opclasses. Yeah, that makes sense, and seems to be my recollection from the several years of presentations I've seen on the topic ;) >> My suggestion would be adding some additional guidance in the user >> documentation around how GIN works with the @@ and @? operators so the= y >> can understand where GIN will work very well with JSON path + their da= ta >> 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). >=20 > Good point. Will do. Thanks! Jonathan --OM3lBZ5McyfsERNKSrRbWWa42vZsKmtHd-- --wO3vXP1fbC6CghWbE0N7uDFdy2Y49Pqe6 Content-Type: application/pgp-signature; name="signature.asc" Content-Description: OpenPGP digital signature Content-Disposition: attachment; filename="signature.asc" -----BEGIN PGP SIGNATURE----- iQIzBAEBCAAdFiEE+oS2la8r95ogZD/x8QSccp8cZScFAlyOfS0ACgkQ8QSccp8c ZSeu8w//a0AHvpiYjYyT7TGqhDqXKPLdyGKgq3I7il+697LdsGNbphA47a+H/yNL xTiYAKSjt4BJcPHFlbTPtvJqM9mwz99hkGyZhuC25Te/WpGpbe+zuk5vYKF4HPwH 04qzW2Tm6hGvfdvU3lA7BgP5+f/HzvOqkuYwoEgnh3cgiwR0NjqToKegav31N0+b b65Weyi6i7Jf1PfXf4L2lEaO4OKM195wnP7xIrm37oQPpAohTw1k68YrfjqeuQwq bKvhthfEXoIL06+Nqn/nO4G67M8ghdgort1hxUrMFJ+lcSEmtr3CGTLmrr8FzFBP oV3Fvysumj91UIbOZAvKXIOH7Dte0L61EqRhau/TAVEOCRuauZLESIpylHUQw3/l sJOkjDXTPGPzeV2i2t2qN0xrU9vXmHIh3F5d8EnTcw5CiI+QHNHM8ZIVg4M7Fcu0 wBmLgHgDbQ0+i/OPLnys/ypt85J0MufYbBjsfCEflCvhILmVEKCDkqdk8so7lpuS fKsGtFK5LwVIG/RikMIH6ze3VRRiE/5rymgMayXXxpWXztTBiBFgerz+kaPzsxh3 OTfewcM7VfKF5xBEoQqFvpm2lvoQsHyMZ3chUmH4a3UEdmOHzyK6razryZpQxe0/ +EpKFbEtTScl2C5Z31kgUebSubRnwgPw/1+bUnueDN3NyhA0lgM= =W9It -----END PGP SIGNATURE----- --wO3vXP1fbC6CghWbE0N7uDFdy2Y49Pqe6--