Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1Xy61h-0002st-7v for pgsql-performance@arkaria.postgresql.org; Mon, 08 Dec 2014 21:39:29 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1Xy61g-0002q3-CN for pgsql-performance@arkaria.postgresql.org; Mon, 08 Dec 2014 21:39:28 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1Xy61f-0002pj-FP; Mon, 08 Dec 2014 21:39:27 +0000 Received: from mail-qc0-x229.google.com ([2607:f8b0:400d:c01::229]) by magus.postgresql.org with esmtps (TLS1.0:RSA_AES_256_CBC_SHA1:256) (Exim 4.80) (envelope-from ) id 1Xy61X-0007Ri-Sl; Mon, 08 Dec 2014 21:39:26 +0000 Received: by mail-qc0-f169.google.com with SMTP id w7so4162832qcr.0 for ; Mon, 08 Dec 2014 13:39:17 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=message-id:date:from:user-agent:mime-version:cc:subject:references :in-reply-to:content-type; bh=VxBn2Fnnok8AXyVzuT4kT+6ZajpYug0EWKJg5DOKysA=; b=wgkjDLqAR7gWeSjStOnXsEYH1/uJyFTijpyqko1loBYAhip+g7z5T8m+NCLxcQn0QX r8f7zQd+WK4JQVYKzvUA/YQpn1CI8Lxml6Fg/FvTiOURfM8s+DuRjmlANdDfW0I+GQXI Y3GwX9pf0WJ6ylFbBpHEt6V4sHpiQSzDAIpYuy+dVtbDbsSvn7KCJZlyhrPu5ajQzoA9 9yUM/SgDmkZBD5ds+pY3emTleJUtQHbfgpqYC4ykWFGpN1rb2UOD1mN8N7cnAE3aYji0 P8o7XAsjc1+mWY+rDOvT6QBnu7eVy9/IWgIsL/iNb5W0sePV/XAJOs3Fa8vOzYyC2N0N cVtA== X-Received: by 10.140.30.163 with SMTP id d32mr55563338qgd.105.1418074757872; Mon, 08 Dec 2014 13:39:17 -0800 (PST) Received: from timbomac-2.local (host114.190-226-95.telecom.net.ar. [190.226.95.114]) by mx.google.com with ESMTPSA id f105sm38881877qge.1.2014.12.08.13.39.16 for (version=TLSv1 cipher=ECDHE-RSA-RC4-SHA bits=128/128); Mon, 08 Dec 2014 13:39:17 -0800 (PST) Message-ID: <54861A81.8030509@gmail.com> Date: Mon, 08 Dec 2014 18:39:13 -0300 From: Tim Dudgeon User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.9; rv:24.0) Gecko/20100101 Thunderbird/24.6.0 MIME-Version: 1.0 CC: pgsql-sql@postgresql.org, pgsql-performance@postgresql.org Subject: Re: [SQL] querying with index on jsonb slower than standard column. Why? References: <5484DBDA.6090405@gmail.com> <5484EEA7.1030403@aklaver.com> <5484F437.2080402@gmail.com> <16147.1418002090@sss.pgh.pa.us> <5485C449.4020204@aklaver.com> <26002.1418053572@sss.pgh.pa.us> <5485C8BA.7040704@aklaver.com> <5485D584.8080105@aklaver.com> <27877.1418057764@sss.pgh.pa.us> <3936.1418071989@sss.pgh.pa.us> <548614C8.90203@aklaver.com> In-Reply-To: <548614C8.90203@aklaver.com> Content-Type: multipart/alternative; boundary="------------010608040708050002030806" X-Pg-Spam-Score: 1.3 (+) 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 This is a multi-part message in MIME format. --------------010608040708050002030806 Content-Type: text/plain; charset=windows-1252; format=flowed Content-Transfer-Encoding: 7bit On 08/12/2014 18:14, Adrian Klaver wrote: > Recheck Cond: ((((data ->> 'assay1_ic50'::text))::double precision > 90::double precision) AND (((data ->> 'assay2_ic50'::text))::double precision < 10::double precision)) > > > >which means we have to pull the JSONB value out of the tuple, search > >it to find the 'assay1_ic50' key, convert the associated value to text > >(which is not exactly cheap because*the value is stored as a numeric*), > >then reparse that text string into a float8, after which we can use > >float8gt. And then probably do an equivalent amount of work on the way > >to making the other comparison. > > > >So this says nothing much about the lossy-bitmap code, and a lot about > >how the JSONB code isn't very well optimized yet. In particular, the > >decision not to provide an operator that could extract a numeric field > >without conversion to text is looking pretty bad here. Yes, that bit seemed strange to me. As I understand the value is stored internally as numeric, but the only way to access it is as text and then cast back to numeric. I *think* this is the only way to do it presently? Tim --------------010608040708050002030806 Content-Type: text/html; charset=windows-1252 Content-Transfer-Encoding: 7bit On 08/12/2014 18:14, Adrian Klaver wrote:
Recheck Cond: ((((data ->> 'assay1_ic50'::text))::double precision > 90::double precision) AND (((data ->> 'assay2_ic50'::text))::double precision < 10::double precision))
> 
> which means we have to pull the JSONB value out of the tuple, search
> it to find the 'assay1_ic50' key, convert the associated value to text
> (which is not exactly cheap because *the value is stored as a numeric*),
> then reparse that text string into a float8, after which we can use
> float8gt.  And then probably do an equivalent amount of work on the way
> to making the other comparison.
> 
> So this says nothing much about the lossy-bitmap code, and a lot about
> how the JSONB code isn't very well optimized yet.  In particular, the
> decision not to provide an operator that could extract a numeric field
> without conversion to text is looking pretty bad here.
Yes, that bit seemed strange to me. As I understand the value is stored internally as numeric, but the only way to access it is as text and then cast back to numeric.
I *think* this is the only way to do it presently?

Tim
--------------010608040708050002030806--