Message-ID: <5484EEA7.1030403@aklaver.com>
Date: Sun, 07 Dec 2014 16:19:51 -0800
From: Adrian Klaver <adrian.klaver@aklaver.com>
User-Agent: Mozilla/5.0 (X11; Linux i686;
 rv:31.0) Gecko/20100101 Thunderbird/31.3.0
MIME-Version: 1.0
To: Tim Dudgeon <tdudgeon.ml@gmail.com>, pgsql-sql@postgresql.org
Subject: Re: querying with index on jsonb slower than standard column.
 Why?
References: <5484DBDA.6090405@gmail.com>
In-Reply-To: <5484DBDA.6090405@gmail.com>
Content-Type: text/plain; charset=windows-1252; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

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

>
> 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
>
>
>


-- 
Adrian Klaver
adrian.klaver@aklaver.com


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql