Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1YGcFX-00078m-TZ for pgsql-performance@arkaria.postgresql.org; Wed, 28 Jan 2015 23:42:20 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1YGcFX-00044f-DR for pgsql-performance@arkaria.postgresql.org; Wed, 28 Jan 2015 23:42:19 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1YGcFW-00044X-6V for pgsql-performance@postgresql.org; Wed, 28 Jan 2015 23:42:18 +0000 Received: from mx-out.zmailcloud.com ([192.198.85.98] helo=mx-out-1.zmailcloud.com) by makus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1YGcFS-0005GS-E1 for pgsql-performance@postgresql.org; Wed, 28 Jan 2015 23:42:15 +0000 Received: from smtp.01.com (smtp.01.com [10.10.0.43]) by mx-out-1.zmailcloud.com (Postfix) with ESMTP id CD184563E3C; Wed, 28 Jan 2015 17:42:13 -0600 (CST) Received: from localhost (localhost [127.0.0.1]) by smtp-out-1.01.com (Postfix) with ESMTP id BCEA8A041E; Wed, 28 Jan 2015 17:42:13 -0600 (CST) X-Virus-Scanned: amavisd-new at smtp-out-1.01.com Received: from smtp.01.com ([127.0.0.1]) by localhost (smtp-out-1.01.com [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 0QTTtDtoJ9aJ; Wed, 28 Jan 2015 17:42:13 -0600 (CST) Received: from smtp.01.com (localhost [127.0.0.1]) by smtp-out-1.01.com (Postfix) with ESMTP id 90B64A0432; Wed, 28 Jan 2015 17:42:13 -0600 (CST) Received: from localhost (localhost [127.0.0.1]) by smtp-out-1.01.com (Postfix) with ESMTP id 6F60BA041E; Wed, 28 Jan 2015 17:42:13 -0600 (CST) X-Virus-Scanned: amavisd-new at smtp-out-1.01.com Received: from smtp.01.com ([127.0.0.1]) by localhost (smtp-out-1.01.com [127.0.0.1]) (amavisd-new, port 10026) with ESMTP id 3PV1vQy14dpp; Wed, 28 Jan 2015 17:42:13 -0600 (CST) Received: from [172.47.23.103] (50-0-89-53.dsl.dynamic.fusionbroadband.com [50.0.89.53]) by smtp-out-1.01.com (Postfix) with ESMTPSA id DD1A5A0432; Wed, 28 Jan 2015 17:42:12 -0600 (CST) Message-ID: <54C973D3.6000808@agliodbs.com> Date: Wed, 28 Jan 2015 15:42:11 -0800 From: Josh Berkus 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 CC: "pgsql-performance@postgresql.org" Subject: Re: working around JSONB's lack of stats? References: <54C738E1.8080405@agliodbs.com> <54C93CF2.3020600@2ndquadrant.com> <54C96AAA.9050901@agliodbs.com> In-Reply-To: Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: 7bit X-Pg-Spam-Score: -1.9 (-) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org On 01/28/2015 03:34 PM, Peter Geoghegan wrote: > On Wed, Jan 28, 2015 at 3:03 PM, Josh Berkus wrote: >> 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. > > Well, the "top-level keys" would still be gathered for expression > indexes. So yeah, maybe it would work alright for arrays of "tags", > and things like that. I tend to think that that's a common enough > use-case. Yah, and even for cases where people have nested structures, currently we require @> to start at the top. So we can at least compare top-level keys to see if the key returned is in the MCEs or not, and take action accordingly. We could start with a constant for anything below the key, where we assume that all values show up 10% of the time. thus: 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? -- 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