From: | Tim Dudgeon <tdudgeon(dot)ml(at)gmail(dot)com> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | querying with index on jsonb slower than standard column. Why? |
Date: | 2014-12-07 22:59:38 |
Message-ID: | 5484DBDA.6090405@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance pgsql-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
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2014-12-08 00:19:51 | Re: querying with index on jsonb slower than standard column. Why? |
Previous Message | Tomas Vondra | 2014-12-07 01:54:14 | PATCH: adaptive ndistinct estimator v3 (WAS: Re: [PERFORM] Yet another abort-early plan disaster on 9.3) |
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2014-12-08 00:19:51 | Re: querying with index on jsonb slower than standard column. Why? |
Previous Message | David G Johnston | 2014-12-05 07:35:28 | Re: rolling window without aggregation |