Re: working around JSONB's lack of stats?

pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed

From: Josh Berkus <josh@agliodbs.com>
To: Jim Nasby <Jim.Nasby@BlueTreble.com>
To: Peter Geoghegan <peter.geoghegan86@gmail.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: working around JSONB's lack of stats?
Date: Tue, 03 Feb 2015 11:07:21 -0800
Message-ID: <54D11C69.1040406@agliodbs.com> (raw)
In-Reply-To: <WM!62bb2c5698af3323a7a4539d4181e641c3cd59bade3805128918db17c8db3f004959b1c2a1039fcee8e6a901e34a9d42!@asav-3.01.com>
References: <54C738E1.8080405@agliodbs.com>
	<54C93CF2.3020600@2ndquadrant.com>
	<WM!fb1fda62b61c38532ff12bae335fdc1e6218b492b648ceba142e41b3ce665583fa172958b52364d2a68ef4bed1be16b1!@asav-2.01.com>
	<54C96AAA.9050901@agliodbs.com>
	<CAEYLb_VvYFj4WO4nmHHiz_JP_0qoaJ0mCejLews3oNajDr6vkg@mail.gmail.com>
	<WM!be5f258f0fcfaaf6ecbd8bb544b558bc4effd0300f7478938307ada8127dd4a4435b8de56b589eeba21c1645148ef1de!@asav-1.01.com>
	<54C973D3.6000808@agliodbs.com>
	<CAEYLb_VsR0GDpFkuYKCa6srN4yk2K+NkLUi0a5heXtksAd00ag@mail.gmail.com>
	<WM!3d5f0e8f73f829fd0be4b087173dd28def8444243c966ca8f4eff15210278d904f6415ae469ec9928ecc27ddd750a19b!@asav-1.01.com>
	<54CBE902.6000309@agliodbs.com>
	<54CC3130.5030604@BlueTreble.com>
	<WM!36dcc2ccf651b83c72477bae595880baeee5337932c2ff56b8345031747db1239403aea15a96388ffd1915af872b08f3!@asav-3.01.com>
	<54CE95B1.8020401@agliodbs.com>
	<54D028E3.9040502@BlueTreble.com>
	<WM!62bb2c5698af3323a7a4539d4181e641c3cd59bade3805128918db17c8db3f004959b1c2a1039fcee8e6a901e34a9d42!@asav-3. 01.com>
List-Unsubscribe:  <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>

On 02/02/2015 05:48 PM, Jim Nasby wrote:
> On 2/1/15 3:08 PM, Josh Berkus wrote:
>> I'm not clear on what you're suggesting here.  I'm discussing how the
>> stats for a JSONB field would be stored and accessed; I don't understand
>> what that has to do with indexing.
> 
> The JSON problem is similar to the problem of doing multi-column
> statistics: there's no way to simply try to keep statistics on all
> possible combinations because that's something that's can be extremely
> large.
> 
> Something that's been proposed is only trying to keep multi-column stats
> on column combinations that we have an index on (which in a way is
> really just keeping stats on the index itself).

The difficulty with column correlation (as with value correlation for
JSONB) is the combination of *values*, not the combination of *columns*.
 Via the brute force method, imagine you have one column with cardinalty
100, and another with cardinality 100,000.  This would require you do
keep 10 million different correlation coefficients in order to be able
to estimate correctly.  Even correlating MCVs would add up to quite a
bit of stats in short order; these days people frequently set statistics
to 1000.  The same goes for JSON keys.

> If we built that, we could use the same technique for JSON by simply
> defining the indexes you needed for whatever you were searching on.
> 
> Obviously that's not as ideal is simply keeping full statistics on
> everything in a JSON document, but it might be a heck of a lot easier to
> accomplish.

Walk before trying to run, let alone fly, please.  Right now we don't
have selectivity estimation for a *single* key; let's do that before we
start talking about better estimation for combinations of keys.

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

view thread (13+ messages)

Message-ID: <54D11C69.1040406@agliodbs.com>
Permalink:  ../54D11C69.1040406@agliodbs.com/
Also on:    postgresql.org/message-id/54D11C69.1040406@agliodbs.com

 ·

reply

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, Jim.Nasby@BlueTreble.com, peter.geoghegan86@gmail.com
  Subject: Re: working around JSONB's lack of stats?
  In-Reply-To: <54D11C69.1040406@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