From: Nikita Glukhov <n.gluhov@postgrespro.ru>
To: Jonathan S. Katz <jkatz@postgresql.org>
To: Alexander Korotkov <a.korotkov@postgrespro.ru>
Cc: Pavel Stehule <pavel.stehule@gmail.com>
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: Mon, 18 Mar 2019 01:57:36 +0300
Message-ID: <aae2a342-28ff-1801-1e8e-c80203001078@postgrespro.ru> (raw)
In-Reply-To: <36067d03-c085-b3ef-f379-a00aa4bff7e5@postgresql.org>
References: <CAPpHfdv2bro1224314aRijKJu1bfwEimKr6hExoOaF4sqSDTQA@mail.gmail.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>
<3247f0b1-ad4a-2d4e-e9b1-1eedc1e7e81a@postgresql.org>
<CAPpHfdurVc7WQQUNOX2QCVEGnD3h5G0_t5prbcFiofBfqq4V-w@mail.gmail.com>
<4d103338-2f0b-d304-ee38-420c0fc973f8@postgresql.org>
<CAPpHfds0OBNnr_uV1Urb1sDTBvyQD7wwX3MO_VgEsXetjrXwZA@mail.gmail.com>
<36067d03-c085-b3ef-f379-a00aa4bff7e5@postgresql.org>
On 17.03.2019 21:29, Jonathan S. Katz wrote:
> On 3/17/19 1:14 PM, Alexander Korotkov wrote:
>> On Sun, Mar 17, 2019 at 8:06 PM Jonathan S. Katz <jkatz@postgresql.org> wrote:
>>> On 3/17/19 1:02 PM, Alexander Korotkov wrote:
>>>> Thank you for the explanation. Is it jsonb_ops or jsonb_path_ops?
>>> I just used "USING gin(col)" so jsonb_ops.
>> I see. So, jsonb_ops extracts from this query only existence of
>> .length key. And I can bet it exists in all (or almost all) the
>> documents. Thus, optimizer thinks index might be useful, while it's
>> useless. There is not much can be done while we don't have statistics
>> for jsonb (and access to it from GIN extract_query). So, for now we
>> can just refuse from extracting only keys from jsonpath in jsonb_ops.
>> But I think it would be better to just document this issue. In future
>> we should improve that with statistics.
> That seems to make sense, especially given how I've typically stored
> JSON documents in PostgreSQL. It sounds like this particular problem
> would be solved appropriately with JSONB statistics.
GIN jsonb_ops extracts from query
data @? '$.length ? (@ < 150)'
the same GIN entries as from queries
data @? '$.length'
data ? 'length'
If you don't want to extract entries from unsupported expressions, you can try
to use another jsonpath operator @@. Queries will also look like a bit simpler:
data @@ '$.length < 150'
data @@ '$.content like_regex "^Start"'
data @@ '$.content like_regex "risk" flag "i"'
All this queries emit no GIN entries. But note that
data @@ '$ ? (@.content == "foo").length < 150'
emits the same entries as
data @@ '$.content == "foo"'
We already have a POC implementation of jsonb statistics that was written
2 years ago. I rebased it onto the current master yesterday. If it is
interesting, you can find it on my GitHub [1]. But note, that there is
no support for jsonpath operators yet, only boolean EXISTS ?, ?|, ?&, and
CONTAINS @> operators are supported. Also there is no docs, and it works
slowly (a more effective storage method for statistics of individual JSON
paths is needed).
Also there is ability to calculate derived statistics of expressions like
js -> 'x' -> 0 -> 'y'
js #> '{x,0,y}'
using jsonb statistics for columns "js". So the selectivity of expressions
js -> 'x' -> 0 -> 'y' = '123'
js #> '{x,0,y}' >= '123'
also can be estimated (but these expressions can't be used by index on "js").
This topic deserves a separate discussion. I hope, we will start the
corresponding thread for PG13 after we find a more effective way of jsonb
statistics storing.
[1] https://github.com/glukhovn/postgres/tree/jsonb_stats
--
Nikita Glukhov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
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: n.gluhov@postgrespro.ru, jkatz@postgresql.org, a.korotkov@postgrespro.ru, pavel.stehule@gmail.com, 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: <aae2a342-28ff-1801-1e8e-c80203001078@postgrespro.ru>
* 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