Re: working around JSONB's lack of stats? - Mailing list pgsql-performance

From Josh Berkus
Subject Re: working around JSONB's lack of stats?
Date
Msg-id 54C973D3.6000808@agliodbs.com
Whole thread Raw
In response to working around JSONB's lack of stats?  (Josh Berkus <josh@agliodbs.com>)
Responses Re: working around JSONB's lack of stats?
List pgsql-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


pgsql-performance by date:

Previous
From: Peter Geoghegan
Date:
Subject: Re: working around JSONB's lack of stats?
Next
From: Peter Geoghegan
Date:
Subject: Re: working around JSONB's lack of stats?