pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Jim Nasby <Jim.Nasby@BlueTreble.com>
To: 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: Mon, 2 Feb 2015 19:48:19 -0600
Message-ID: <54D028E3.9040502@BlueTreble.com> (raw)
In-Reply-To: <54CE95B1.8020401@agliodbs.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>
List-Unsubscribe:  <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>

On 2/1/15 3:08 PM, Josh Berkus wrote:
> On 01/30/2015 05:34 PM, Jim Nasby wrote:
>> On 1/30/15 2:26 PM, Josh Berkus wrote:
>>> 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.
>>
>> Even if there were multiple occurrences, this would probably still be an
>> improvement.
>>
>> Another idea... at one time in the past when discussing statistics on
>> multiple columns, one idea was to build statistics on indexes. If we
>> built that, we could also do the same thing for at least JSONB (not sure
>> about JSON). Obviously doesn't help for stuff you haven't indexed, but
>> presumably if you care about performance and have any significant size
>> of data you've also indexed parts of the JSON, yes?
>
> 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).

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.
-- 
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.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)  latest in thread

Message-ID: <54D028E3.9040502@BlueTreble.com>
Permalink:  ../54D028E3.9040502@BlueTreble.com/
Also on:    postgresql.org/message-id/54D028E3.9040502@BlueTreble.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: Jim.Nasby@BlueTreble.com, josh@agliodbs.com, peter.geoghegan86@gmail.com
  Subject: Re: working around JSONB's lack of stats?
  In-Reply-To: <54D028E3.9040502@BlueTreble.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