agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Tim Dudgeon <tdudgeon.ml@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Re: querying with index on jsonb slower than standard column. Why?
Date: Sun, 07 Dec 2014 21:43:35 -0300
Message-ID: <5484F437.2080402@gmail.com> (raw)
In-Reply-To: <5484EEA7.1030403@aklaver.com>
References: <5484DBDA.6090405@gmail.com>
<5484EEA7.1030403@aklaver.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
On 07/12/2014 21:19, Adrian Klaver wrote:
> On 12/07/2014 02:59 PM, Tim Dudgeon wrote:
>> I was doing some performance profiling regarding querying against jsonb
>> columns and found something I can't explain.
>> I created json version and standard column versions of some data, and
>> indexed the json 'fields' and the normal columns and executed equivalent
>> queries against both.
>> I find that the json version is quite a bit (approx 3x) slower which I
>> can't explain as both should (and are according to plans are) working
>> against what I would expect are equivalent indexes.
>>
>> Can anyone explain this?
>
> The docs can:
>
> http://www.postgresql.org/docs/9.4/interactive/datatype-json.html#JSON-INDEXING
>
If so them I'm missing it.
The index created is not a gin index. Its a standard btree index on the
data extracted from the json. So the indexes on the standard columns and
the ones on the 'fields' extracted from the json seem to be equivalent.
But perform differently.
Tim
>
>>
>> Example code is here:
>>
>>
>> create table json_test (
>> id SERIAL,
>> assay1_ic50 FLOAT,
>> assay2_ic50 FLOAT,
>> data JSONB
>> );
>>
>> DO
>> $do$
>> DECLARE
>> val1 FLOAT;
>> val2 FLOAT;
>> BEGIN
>> for i in 1..10000000 LOOP
>> val1 = random() * 100;
>> val2 = random() * 100;
>> INSERT INTO json_test (assay1_ic50, assay2_ic50, data) VALUES
>> (val1, val2, ('{"assay1_ic50": ' || val1 || ', "assay2_ic50": ' ||
>> val2 || ', "mod": "="}')::jsonb);
>> end LOOP;
>> END
>> $do$
>>
>> create index idx_data_json_assay1_ic50 on json_test (((data ->>
>> 'assay1_ic50')::float));
>> create index idx_data_json_assay2_ic50 on json_test (((data ->>
>> 'assay2_ic50')::float));
>>
>> create index idx_data_col_assay1_ic50 on json_test (assay1_ic50);
>> create index idx_data_col_assay2_ic50 on json_test (assay2_ic50);
>>
>> select count(*) from json_test;
>> select * from json_test limit 10;
>>
>> select count(*) from json_test where (data->>'assay1_ic50')::float > 90
>> and (data->>'assay2_ic50')::float < 10;
>> select count(*) from json_test where assay1_ic50 > 90 and assay2_ic50
>> < 10;
>>
>>
>>
>> Thanks
>> Tim
>>
>>
>>
>
>
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
view thread (24+ messages) latest in thread
Message-ID: <5484F437.2080402@gmail.com>
Permalink: ../5484F437.2080402@gmail.com/
Also on: postgresql.org/message-id/5484F437.2080402@gmail.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-sql@postgresql.org
Cc: tdudgeon.ml@gmail.com
Subject: Re: querying with index on jsonb slower than standard column. Why?
In-Reply-To: <5484F437.2080402@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox