On 12/08/2014 01:39 PM, Tim Dudgeon wrote: > 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? Yeah, I believe the core problem is that Postgres currently doesn't have any way to have variadic return times from a function which don't match variadic input types. Returning a value as an actual numeric from JSONB would require returning a numeric from a function whose input type is text or json. So a known issue but one which would require a lot of replumbing to fix. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
