Re: working around JSONB's lack of stats?

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