Re: querying with index on jsonb slower than standard column. Why? - Mailing list pgsql-sql
From | Tom Lane |
---|---|
Subject | Re: querying with index on jsonb slower than standard column. Why? |
Date | |
Msg-id | 16147.1418002090@sss.pgh.pa.us Whole thread Raw |
In response to | Re: querying with index on jsonb slower than standard column. Why? (Tim Dudgeon <tdudgeon.ml@gmail.com>) |
Responses |
Re: querying with index on jsonb slower than standard column.
Why?
|
List | pgsql-sql |
Tim Dudgeon <tdudgeon.ml@gmail.com> writes: > 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. I don't see any particular difference ... regression=# explain analyze select count(*) from json_test where (data->>'assay1_ic50')::float > 90 and (data->>'assay2_ic50')::float < 10; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Aggregate (cost=341613.79..341613.80 rows=1 width=0) (actual time=901.207..901.208 rows=1 loops=1) -> Bitmap Heap Scan on json_test (cost=123684.69..338836.02 rows=1111111 width=0) (actual time=497.982..887.128 rows=100690 loops=1) RecheckCond: ((((data ->> 'assay2_ic50'::text))::double precision < 10::double precision) AND (((data ->> 'assay1_ic50'::text))::doubleprecision > 90::double precision)) Heap Blocks: exact=77578 -> BitmapAnd (cost=123684.69..123684.69rows=1111111 width=0) (actual time=476.585..476.585 rows=0 loops=1) -> Bitmap IndexScan on idx_data_json_assay2_ic50 (cost=0.00..61564.44 rows=3333333 width=0) (actual time=219.287..219.287 rows=999795loops=1) Index Cond: (((data ->> 'assay2_ic50'::text))::double precision < 10::double precision) -> Bitmap Index Scan on idx_data_json_assay1_ic50 (cost=0.00..61564.44 rows=3333333 width=0) (actualtime=208.197..208.197 rows=1000231 loops=1) Index Cond: (((data ->> 'assay1_ic50'::text))::doubleprecision > 90::double precision)Planning time: 0.128 msExecution time: 904.196 ms (11 rows) regression=# explain analyze select count(*) from json_test where assay1_ic50 > 90 and assay2_ic50 < 10; QUERY PLAN -----------------------------------------------------------------------------------------------------------------------------------------------------------------Aggregate (cost=197251.24..197251.25 rows=1 width=0) (actual time=895.238..895.238 rows=1 loops=1) -> Bitmap Heap Scan on json_test (cost=36847.25..197003.24 rows=99197 width=0) (actual time=495.427..881.033 rows=100690 loops=1) RecheckCond: ((assay2_ic50 < 10::double precision) AND (assay1_ic50 > 90::double precision)) Heap Blocks: exact=77578 -> BitmapAnd (cost=36847.25..36847.25 rows=99197 width=0) (actual time=474.201..474.201 rows=0 loops=1) -> Bitmap Index Scan on idx_data_col_assay2_ic50 (cost=0.00..18203.19 rows=985434 width=0) (actualtime=219.060..219.060 rows=999795 loops=1) Index Cond: (assay2_ic50 < 10::double precision) -> Bitmap Index Scan on idx_data_col_assay1_ic50 (cost=0.00..18594.21 rows=1006637 width=0) (actual time=206.066..206.066rows=1000231 loops=1) Index Cond: (assay1_ic50 > 90::double precision)Planning time:0.129 msExecution time: 898.237 ms (11 rows) regression=# \timing Timing is on. regression=# select count(*) from json_test where (data->>'assay1_ic50')::float > 90 and (data->>'assay2_ic50')::float < 10;count --------100690 (1 row) Time: 882.607 ms regression=# select count(*) from json_test where assay1_ic50 > 90 and assay2_ic50 < 10;count --------100690 (1 row) Time: 881.071 ms regards, tom lane