From: Josh Berkus <josh@agliodbs.com>
To: pgsql-performance@postgresql.org
Subject: Re: working around JSONB's lack of stats?
Date: Wed, 28 Jan 2015 15:03:06 -0800
Message-ID: <54C96AAA.9050901@agliodbs.com> (raw)
In-Reply-To: <WM!fb1fda62b61c38532ff12bae335fdc1e6218b492b648ceba142e41b3ce665583fa172958b52364d2a68ef4bed1be16b1!@asav-2.01.com>
References: <54C738E1.8080405@agliodbs.com>
<54C93CF2.3020600@2ndquadrant.com>
<WM!fb1fda62b61c38532ff12bae335fdc1e6218b492b648ceba142e41b3ce665583fa172958b52364d2a68ef4bed1be16b1!@asav-2.01.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: josh@agliodbs.com
Subject: Re: working around JSONB's lack of stats?
In-Reply-To: <54C96AAA.9050901@agliodbs.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox