From: Josh Berkus <josh@agliodbs.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: Fri, 30 Jan 2015 12:26:42 -0800
Message-ID: <54CBE902.6000309@agliodbs.com> (raw)
In-Reply-To: <WM!3d5f0e8f73f829fd0be4b087173dd28def8444243c966ca8f4eff15210278d904f6415ae469ec9928ecc27ddd750a19b!@asav-1.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>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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
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, peter.geoghegan86@gmail.com
Subject: Re: working around JSONB's lack of stats?
In-Reply-To: <54CBE902.6000309@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