From: Jonathan S. Katz <jkatz@postgresql.org>
To: Alexander Korotkov <a.korotkov@postgrespro.ru>
To: 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 12:46:01 -0400
Message-ID: <04981bad-2156-a927-b04c-50ff2c9709ae@postgresql.org> (raw)
In-Reply-To: <CAPpHfdtYVPNWu5p6P5A11FXB=0pthUgcRS5W30Zz-WSkT3TQRQ@mail.gmail.com>
References: <CAPpHfdv2bro1224314aRijKJu1bfwEimKr6hExoOaF4sqSDTQA@mail.gmail.com>
<20190130022826.of7w6xv5lbvzqxfp@alap3.anarazel.de>
<CAPpHfdvov6kWfT3hk8KMSah+kzNuN2AXaZfBqnSU0YkzDVkR2Q@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>
On 3/17/19 3:13 AM, Alexander Korotkov wrote:
> On Sat, Mar 16, 2019 at 9:39 PM Pavel Stehule <pavel.stehule@gmail.com> wrote:
>> so 16. 3. 2019 v 10:36 odesÃlatel Alexander Korotkov <a.korotkov@postgrespro.ru> napsal:
>>>
>>> On Thu, Mar 14, 2019 at 12:07 PM Alexander Korotkov
>>> <a.korotkov@postgrespro.ru> wrote:
>>>> On Sun, Mar 10, 2019 at 1:51 PM Alexander Korotkov
>>>> <a.korotkov@postgrespro.ru> wrote:
>>>>> On Wed, Mar 6, 2019 at 12:40 AM Nikita Glukhov <n.gluhov@postgrespro.ru> 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, which is
>>>>>> represented in SQL by NULL.
>>>>>>
>>>>>> JSON path expressions return sequences of SQL/JSON items, which are defined by
>>>>>> SQL/JSON data model. But JSON path predicates (logical expressions), 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
>
> 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 ? (@ == 200)';
SELECT *
FROM news_feed
WHERE data @? '$.id ? (@ == "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
Attachments:
[application/pgp-signature] signature.asc (832B, ../04981bad-2156-a927-b04c-50ff2c9709ae@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: <04981bad-2156-a927-b04c-50ff2c9709ae@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