From: Josh Berkus <josh@agliodbs.com>
To: Peter Geoghegan <peter.geoghegan86@gmail.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: working around JSONB's lack of stats?
Date: Wed, 28 Jan 2015 15:42:11 -0800
Message-ID: <54C973D3.6000808@agliodbs.com> (raw)
In-Reply-To: <WM!be5f258f0fcfaaf6ecbd8bb544b558bc4effd0300f7478938307ada8127dd4a4435b8de56b589eeba21c1645148ef1de!@asav-1.01.com>
References: <54C738E1.8080405@agliodbs.com>
<54C93CF2.3020600@2ndquadrant.com>
<WM!fb1fda62b61c38532ff12bae335fdc1e6218b492b648ceba142e41b3ce665583fa172958b52364d2a68ef4bed1be16b1!@asav-2.01.com>
<54C96AAA.9050901@agliodbs.com>
<CAEYLb_VvYFj4WO4nmHHiz_JP_0qoaJ0mCejLews3oNajDr6vkg@mail.gmail.com>
<WM!be5f258f0fcfaaf6ecbd8bb544b558bc4effd0300f7478938307ada8127dd4a4435b8de56b589eeba21c1645148ef1de!@asav-1.01.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
On 01/28/2015 03:34 PM, Peter Geoghegan wrote:
> On Wed, Jan 28, 2015 at 3:03 PM, Josh Berkus <josh@agliodbs.com> wrote:
>> We already have most_common_elem (MCE) for arrays and tsearch. What if
>> we put JSONB's most common top-level keys (or array elements, depending)
>> in the MCE array? Then we could still apply a simple rule for any path
>> criteria below the top-level keys, say assuming that any sub-key
>> criteria would match 10% of the time. While it wouldn't be perfect, it
>> would be better than what we have now.
>
> Well, the "top-level keys" would still be gathered for expression
> indexes. So yeah, maybe it would work alright for arrays of "tags",
> and things like that. I tend to think that that's a common enough
> use-case.
Yah, and even for cases where people have nested structures, currently
we require @> to start at the top. So we can at least compare top-level
keys to see if the key returned is in the MCEs or not, and take action
accordingly.
We could start with a constant for anything below the key, where we
assume that all values show up 10% of the time.
thus:
jsonb_col @> '[ "key1" ]'
or jsonb_col ? 'key1'
if in MCE, assign % from MCE
otherwise assign 1% of non-MCE %
jsonb_col @> '{ "key1": "value1" }'
if in MCE, assign MCE% * 0.1
otherwise assign 0.01 of non-MCE %
Does that make sense?
--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
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-performance@postgresql.org
Cc: josh@agliodbs.com, peter.geoghegan86@gmail.com
Subject: Re: working around JSONB's lack of stats?
In-Reply-To: <54C973D3.6000808@agliodbs.com>
* 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