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 1h5eik-0000Iz-3c for pgsql-hackers@arkaria.postgresql.org; Sun, 17 Mar 2019 22:57:34 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1h5eii-00070B-T7 for pgsql-hackers@arkaria.postgresql.org; Sun, 17 Mar 2019 22:57:32 +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 1h5eii-0006sx-FB for pgsql-hackers@lists.postgresql.org; Sun, 17 Mar 2019 22:57:32 +0000 Received: from cyclops.postgrespro.ru ([93.174.131.138] helo=mail.postgrespro.ru) by magus.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1h5eib-0003aW-5K; Sun, 17 Mar 2019 22:57:32 +0000 Received: from localhost (localhost [127.0.0.1]) by mail.postgrespro.ru (Postfix) with ESMTP id B6DD821C3FB9; Mon, 18 Mar 2019 01:57:23 +0300 (MSK) X-Virus-Scanned: Debian amavisd-new at postgrespro.ru X-Spam-Flag: NO X-Spam-Score: 0 X-Spam-Level: X-Spam-Status: No, score=x tagged_above=-99 required=4 WHITELISTED tests=[] autolearn=unavailable Received: from [10.100.236.31] (unknown [195.82.154.27]) (using TLSv1.2 with cipher ECDHE-RSA-AES128-GCM-SHA256 (128/128 bits)) (Client did not present a certificate) by mail.postgrespro.ru (Postfix) with ESMTPSA id 1FDF221C3FB7; Mon, 18 Mar 2019 01:57:23 +0300 (MSK) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=postgrespro.ru; s=mail; t=1552863443; bh=eq9wHzceLuLmEsVaZxVvexX0gaYP06bKSZo8kzCReeE=; h=Subject:To:Cc:References:From:Date:In-Reply-To; b=jbg3rxy6uPkUOIhctqcZLubvP40ks2KKapzDXxFdsQcH48A0+T7XAj22uBohIPz6j K9goZ1hlMtVxhG7dQZp8CRz3B4dFPOiUL/ZncAYLDynYb3oJK56TMt4AldVdJtgi47 P9Bj0FbcojHmwF2MtWITHILfi1Sa0McuzZzoA1MM= Subject: Re: jsonpath To: "Jonathan S. Katz" , Alexander Korotkov Cc: Pavel Stehule , Tomas Vondra , Andres Freund , Oleg Bartunov , Michael Paquier , Stas Kelvich , PostgreSQL-development , Thomas Munro , David Steele , Tom Lane , Andrew Dunstan , Robert Haas References: <67128b8b-2d80-9e51-20a8-9ac8e0e2243e@2ndquadrant.com> <220821fb-52c0-c277-0959-322c8eef76b2@postgrespro.ru> <04981bad-2156-a927-b04c-50ff2c9709ae@postgresql.org> <3247f0b1-ad4a-2d4e-e9b1-1eedc1e7e81a@postgresql.org> <4d103338-2f0b-d304-ee38-420c0fc973f8@postgresql.org> <36067d03-c085-b3ef-f379-a00aa4bff7e5@postgresql.org> From: Nikita Glukhov Organization: Postgres Professional Message-ID: Date: Mon, 18 Mar 2019 01:57:36 +0300 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:60.0) Gecko/20100101 Thunderbird/60.5.1 MIME-Version: 1.0 In-Reply-To: <36067d03-c085-b3ef-f379-a00aa4bff7e5@postgresql.org> Content-Type: multipart/alternative; boundary="------------333A78A2D468BB3D221D8D13" Content-Language: en-US List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------333A78A2D468BB3D221D8D13 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 7bit 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 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 --------------333A78A2D468BB3D221D8D13 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 7bit


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
--------------333A78A2D468BB3D221D8D13--