Re: working around JSONB's lack of stats?

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

 



On 01/28/2015 11:48 AM, Tomas Vondra wrote:
> On 27.1.2015 08:06, Josh Berkus wrote:
>> Folks,
>>
> ...
>>
>> On a normal column, I'd raise n_distinct to reflect the higher
>> selecivity of the search terms.  However, since @> uses contsel,
>> n_distinct is ignored.  Anyone know a clever workaround I don't
>> currently see?
> 
> I don't see any reasonable workaround :-(
> 
> ISTM we'll have to invent a way to collect useful stats about contents
> of JSON/JSONB documents. JSONB is cool, but at the moment we're mostly
> relying on defaults that may be reasonable, but still misfire in many
> cases. Do we have any ideas of how that might work?
> 
> We're already collecting stats about contents of arrays, and maybe we
> could do something similar for JSONB? The nested nature of JSON makes
> that rather incompatible with the flat MCV/histogram stats, though.

Well, I was thinking about this.

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.

-- 
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