Re: working around JSONB's lack of stats?

On 01/28/2015 03:34 PM, Peter Geoghegan wrote:
> On Wed, Jan 28, 2015 at 3:03 PM, Josh Berkus <josh(at)agliodbs(dot)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