pg.ddx.io  pgsql-hackers@postgresql.org mailing list archive  

help / color / mirror / Atom feed
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 14:29:44 -0400
Message-ID: <36067d03-c085-b3ef-f379-a00aa4bff7e5@postgresql.org> (raw)
In-Reply-To: <CAPpHfds0OBNnr_uV1Urb1sDTBvyQD7wwX3MO_VgEsXetjrXwZA@mail.gmail.com>
References: <CAPpHfdv2bro1224314aRijKJu1bfwEimKr6hExoOaF4sqSDTQA@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>
	<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>

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.

Thanks,

Jonathan

Attachments:

  [application/pgp-signature] signature.asc (832B, ../36067d03-c085-b3ef-f379-a00aa4bff7e5@postgresql.org/2-signature.asc)
  download

view thread (198+ messages)  latest in thread

Message-ID: <36067d03-c085-b3ef-f379-a00aa4bff7e5@postgresql.org>
Permalink:  ../36067d03-c085-b3ef-f379-a00aa4bff7e5@postgresql.org/
Also on:    postgresql.org/message-id/36067d03-c085-b3ef-f379-a00aa4bff7e5@postgresql.org

 · 

reply
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: <36067d03-c085-b3ef-f379-a00aa4bff7e5@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