Re: working around JSONB's lack of stats?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On 01/28/2015 03:34 PM, Peter Geoghegan wrote:
> On Wed, Jan 28, 2015 at 3:03 PM, Josh Berkus <josh@xxxxxxxxxxxx> 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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance




[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux