Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1Xzd1S-0002IO-H2 for pgsql-performance@arkaria.postgresql.org; Sat, 13 Dec 2014 03:05:34 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1Xzd1R-00046U-Ui for pgsql-performance@arkaria.postgresql.org; Sat, 13 Dec 2014 03:05:33 +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 1Xzd1P-00043y-Vu; Sat, 13 Dec 2014 03:05:32 +0000 Received: from relay5-d.mail.gandi.net ([2001:4b98:c:538::197]) by magus.postgresql.org with esmtps (TLS1.0:DHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.80) (envelope-from ) id 1Xzd1L-0001s8-T9; Sat, 13 Dec 2014 03:05:30 +0000 Received: from mfilter8-d.gandi.net (mfilter8-d.gandi.net [217.70.178.137]) by relay5-d.mail.gandi.net (Postfix) with ESMTP id 2038441C054; Sat, 13 Dec 2014 04:05:25 +0100 (CET) X-Virus-Scanned: Debian amavisd-new at mfilter8-d.gandi.net Received: from relay5-d.mail.gandi.net ([217.70.183.197]) by mfilter8-d.gandi.net (mfilter8-d.gandi.net [10.0.15.180]) (amavisd-new, port 10024) with ESMTP id 1vtbmBnlPu0a; Sat, 13 Dec 2014 04:05:23 +0100 (CET) X-Originating-IP: 98.27.58.255 Received: from [192.168.10.146] (cpe-098-027-058-255.nc.res.rr.com [98.27.58.255]) (Authenticated sender: adsend@dunslane.net) by relay5-d.mail.gandi.net (Postfix) with ESMTPSA id 391F041C04F; Sat, 13 Dec 2014 04:05:21 +0100 (CET) Message-ID: <548BACF0.5040909@dunslane.net> Date: Fri, 12 Dec 2014 22:05:20 -0500 From: Andrew Dunstan User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:31.0) Gecko/20100101 Thunderbird/31.2.0 MIME-Version: 1.0 To: Tom Lane CC: Josh Berkus , Tim Dudgeon , pgsql-sql@postgresql.org, pgsql-performance@postgresql.org Subject: Re: 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> <54861A81.8030509@gmail.com> <548B5CF4.4030009@agliodbs.com> <8867.1418420650@sss.pgh.pa.us> <548B79E3.5070301@dunslane.net> <15863.1418433637@sss.pgh.pa.us> In-Reply-To: <15863.1418433637@sss.pgh.pa.us> Content-Type: text/plain; charset=windows-1252; format=flowed 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 12/12/2014 08:20 PM, Tom Lane wrote: > We can't just add the operator and worry about usability later; > if we're thinking we might want to introduce such an automatic > transformation, we have to be sure the new operator is defined in a > way that allows the transformation to not change any semantics. > What that means in this case is that if (jsonb ->> 'foo')::numeric > would have succeeded, (jsonb ->># 'foo') has to succeed; which means > it'd better be willing to attempt conversion of string values to > numeric, not just throw an error on sight. > > Well, I'm not 100% convinced about the magic transformation being a good thing. Json numbers are distinct from strings, and part of the justification for this is to extract a numeric datum from jsonb exactly as stored, on performance grounds. So turning round now and making that turn a string into a number if possible seems to me to be going in the wrong direction. cheers andrew -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance