querying with index on jsonb slower than standard column. Why?
От | Tim Dudgeon |
---|---|
Тема | querying with index on jsonb slower than standard column. Why? |
Дата | |
Msg-id | 5484DBDA.6090405@gmail.com обсуждение исходный текст |
Ответы |
Re: querying with index on jsonb slower than standard column.
Why?
|
Список | 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
В списке pgsql-sql по дате отправления: