From: Jonathan S. Katz <jkatz@postgresql.org>
To: Alexander Korotkov <a.korotkov@postgrespro.ru>
Cc: Pavel Stehule <pavel.stehule@gmail.com>
Cc: Nikita Glukhov <n.gluhov@postgrespro.ru>
Cc: Tomas Vondra <tomas.vondra@2ndquadrant.com>
Cc: Andres Freund <andres@anarazel.de>
Cc: Oleg Bartunov <obartunov@postgrespro.ru>
Cc: Michael Paquier <michael@paquier.xyz>
Cc: Stas Kelvich <s.kelvich@postgrespro.ru>
Cc: PostgreSQL-development <pgsql-hackers@postgresql.org>
Cc: Thomas Munro <thomas.munro@enterprisedb.com>
Cc: David Steele <david@pgmasters.net>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Andrew Dunstan <andrew.dunstan@2ndquadrant.com>
Cc: Robert Haas <robertmhaas@gmail.com>
Subject: Re: jsonpath
Date: Sun, 17 Mar 2019 13:00:29 -0400
Message-ID: <3247f0b1-ad4a-2d4e-e9b1-1eedc1e7e81a@postgresql.org> (raw)
In-Reply-To: <CAPpHfdtYvL+h5W7NYPVf-CWfVbMyqytNu-jV1xUhbPM6rnN8Xg@mail.gmail.com>
References: <CAPpHfdv2bro1224314aRijKJu1bfwEimKr6hExoOaF4sqSDTQA@mail.gmail.com>
<957ee2ff-f9a6-0ba1-13e2-9c4d320f63ac@2ndquadrant.com>
<CAPpHfdu+y1tkiidwu0i=XyGK5eOTOu1fPbuYs=XT32g7tzGd4Q@mail.gmail.com>
<a3be6a7a-77d3-0e88-4f9d-4f725d11d7cd@postgrespro.ru>
<CAPpHfdsW924B1eYLqgXE0SkH0ym2Zk32CqPwUu_CH65Gk_onrA@mail.gmail.com>
<c25eb8a5-a129-bb7e-c930-761f0e2a6b71@2ndquadrant.com>
<67128b8b-2d80-9e51-20a8-9ac8e0e2243e@2ndquadrant.com>
<220821fb-52c0-c277-0959-322c8eef76b2@postgrespro.ru>
<CAPpHfdsyWVfW7N-T9-H1RjoRZCwQX4w5qRd6SGjbLP=qf_y9iw@mail.gmail.com>
<CAPpHfdsLuSAGHbn06Qq83=XCDhW8RqFe8tFeq+rYhiNWD=bzFA@mail.gmail.com>
<CAPpHfduXPikbeCnaVi1d2GQvKJLJcQ_GEe5KrzSPTV1KD0E8+Q@mail.gmail.com>
<CAFj8pRCkCBQ+ZGJ=1k9LLcyVGQm_RCgmP9zijA3hM9-kjNVSYQ@mail.gmail.com>
<CAPpHfdtYVPNWu5p6P5A11FXB=0pthUgcRS5W30Zz-WSkT3TQRQ@mail.gmail.com>
<04981bad-2156-a927-b04c-50ff2c9709ae@postgresql.org>
<CAPpHfdtYvL+h5W7NYPVf-CWfVbMyqytNu-jV1xUhbPM6rnN8Xg@mail.gmail.com>
On 3/17/19 12:55 PM, Alexander Korotkov wrote:
>
>> 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.
>
> 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=1539.78..1539.79 rows=1 width=8) (actual
time=270.419..270.419 rows=1 loops=1)
-> Bitmap Heap Scan on news_feed (cost=23.24..1538.73 rows=418
width=0) (actual time=84.040..270.407 rows=5 loops=1)
Recheck Cond: (data @? '$."length"?(@ < 150)'::jsonpath)
Rows Removed by Index Recheck: 418360
Heap Blocks: exact=28690
-> Bitmap Index Scan on news_feed_data_gin_idx
(cost=0.00..23.14 rows=418 width=0) (actual time=41.788..41.788
rows=418365 loops=1)
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 = 8):
Finalize Aggregate (cost=30998.07..30998.08 rows=1 width=8) (actual
time=91.062..91.062 rows=1 loops=1)
-> Gather (cost=30997.65..30998.06 rows=4 width=8) (actual
time=90.892..97.739 rows=5 loops=1)
Workers Planned: 4
Workers Launched: 4
-> Partial Aggregate (cost=29997.65..29997.66 rows=1 width=8)
(actual time=76.977..76.977 rows=1 loops=5)
-> Parallel Seq Scan on news_feed (cost=0.00..29997.39
rows=104 width=0) (actual time=39.736..76.964 rows=1 loops=5)
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 to
>> handle inequality, regex exps, etc. And the cases where it really does
>> work well, it's _incredibly_ fast.
>
> 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 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).
>
> Good point. Will do.
Thanks!
Jonathan
Attachments:
[application/pgp-signature] signature.asc (832B, ../3247f0b1-ad4a-2d4e-e9b1-1eedc1e7e81a@postgresql.org/2-signature.asc)
download
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: pgsql-hackers@postgresql.org
Cc: jkatz@postgresql.org, a.korotkov@postgrespro.ru, pavel.stehule@gmail.com, n.gluhov@postgrespro.ru, tomas.vondra@2ndquadrant.com, andres@anarazel.de, obartunov@postgrespro.ru, michael@paquier.xyz, s.kelvich@postgrespro.ru, thomas.munro@enterprisedb.com, david@pgmasters.net, tgl@sss.pgh.pa.us, andrew.dunstan@2ndquadrant.com, robertmhaas@gmail.com
Subject: Re: jsonpath
In-Reply-To: <3247f0b1-ad4a-2d4e-e9b1-1eedc1e7e81a@postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox