Message-ID: <54CBE902.6000309@agliodbs.com>
Date: Fri, 30 Jan 2015 12:26:42 -0800
From: Josh Berkus <josh@agliodbs.com>
Organization: PostgreSQL Experts Inc.
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
 rv:31.0) Gecko/20100101 Thunderbird/31.3.0
MIME-Version: 1.0
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?
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>
In-Reply-To: 
 <WM!3d5f0e8f73f829fd0be4b087173dd28def8444243c966ca8f4eff15210278d904f6415ae469ec9928ecc27ddd750a19b!@asav-1.01.com>
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

On 01/28/2015 03:50 PM, Peter Geoghegan wrote:
> On Wed, Jan 28, 2015 at 3:42 PM, Josh Berkus <josh@agliodbs.com> wrote:
>> 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?
> 
> I suspect it makes a lot less sense. The way people seem to want to
> use jsonb is as a document store with a bit of flexibility. Individual
> JSON documents tend to be fairly homogeneous in structure within a
> table, just like with systems like MongoDB. Strings within arrays are
> keys for our purposes, and these are often used for tags and so on.
> But Strings that are the key of an object/pair are much less useful to
> index, in my estimation.

Yeah, I see your point; except for arrays, people are usually searching
for a key:value pair, and the existence of the key is not in doubt.

That would make the "element" the key:value pair, no?  But
realistically, we would only want to do that for simple keys and values.

Although: if you "flatten" a nested JSON structure into just keys with
scalar values (and array items as their own thing), then you could have
a series of expanded key:value pairs to put into MCE.

For example:

{ house : { city : San Francisco,
     sqft: 1200,
     color: blue,
     occupants: [ mom, dad, child1 ]
     }
  occupation: programmer
}

... would get flattened out into the following pairs:

city: san francisco
sqft: 1200
color: blue
occupants: [ mom ]
occupants: [ dad ]
occupants: [ child1 ]
occupation: programmer

This would probably work because there aren't a lot of data structures
where people would have the same key:value pair in different locations
in the JSON, and care about it stats-wise.  Alternatetly, if the same
key-value pair appears multiple times in the same sample row, we could
cut the MC% by that multiple.

No?

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