agora inbox for pgsql-sql@postgresql.org  

help / color / mirror / Atom feed

From: Tim Dudgeon <tdudgeon.ml@gmail.com>
To: pgsql-sql@postgresql.org
Subject: querying with index on jsonb slower than standard column. Why?
Date: Sun, 07 Dec 2014 19:59:38 -0300
Message-ID: <5484DBDA.6090405@gmail.com> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>

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?

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: <5484DBDA.6090405@gmail.com>
Permalink:  ../5484DBDA.6090405@gmail.com/
Also on:    postgresql.org/message-id/5484DBDA.6090405@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: <5484DBDA.6090405@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