https://www.postgresql.org/message-id/5484DBDA.6090405%40gmail.com

jsonb.sql

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 assay1_ic50 > 90 and assay2_ic50 < 10;


explain analyze select count(*) from json_test where (data->>'assay1_ic50')::float > 90 and (data->>'assay2_ic50')::float < 10;
 Aggregate  (cost=341613.78..341613.79 rows=1 width=8) (actual time=549884.712..549884.712 rows=1 loops=1)
   ->  Bitmap Heap Scan on json_test  (cost=123684.67..338836.00 rows=1111111 width=0) (actual time=1900.530..549806.446 rows=99983 loops=1)
         Recheck Cond: ((((data ->> 'assay2_ic50'::text))::double precision < '10'::double precision) AND (((data ->> 'assay1_ic50'::text))::double precision > '90'::double precision))
         Rows Removed by Index Recheck: 7236640
         Heap Blocks: exact=30072 lossy=131932
         ->  BitmapAnd  (cost=123684.67..123684.67 rows=1111111 width=0) (actual time=1894.230..1894.230 rows=0 loops=1)
               ->  Bitmap Index Scan on idx_data_json_assay2_ic50  (cost=0.00..61564.43 rows=3333333 width=0) (actual time=734.325..734.325 rows=999767 loops=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.43 rows=3333333 width=0) (actual time=1144.057..1144.057 rows=999057 loops=1)
                     Index Cond: (((data ->> 'assay1_ic50'::text))::double precision > '90'::double precision)
 Planning time: 8.817 ms
 Execution time: 549886.001 ms


explain analyze select count(*) from json_test where assay1_ic50 > 90 and assay2_ic50 < 10;
 Aggregate  (cost=200512.25..200512.26 rows=1 width=8) (actual time=547038.541..547038.541 rows=1 loops=1)
   ->  Bitmap Heap Scan on json_test  (cost=37564.74..200254.45 rows=103118 width=0) (actual time=2407.613..546975.642 rows=99983 loops=1)
         Recheck Cond: ((assay2_ic50 < '10'::double precision) AND (assay1_ic50 > '90'::double precision))
         Rows Removed by Index Recheck: 7236640
         Heap Blocks: exact=30072 lossy=131932
         ->  BitmapAnd  (cost=37564.74..37564.74 rows=103118 width=0) (actual time=2399.966..2399.966 rows=0 loops=1)
               ->  Bitmap Index Scan on idx_data_col_assay2_ic50  (cost=0.00..18736.24 rows=1014374 width=0) (actual time=1571.646..1571.646 rows=999767 loops=1)
                     Index Cond: (assay2_ic50 < '10'::double precision)
               ->  Bitmap Index Scan on idx_data_col_assay1_ic50  (cost=0.00..18776.69 rows=1016568 width=0) (actual time=810.540..810.540 rows=999057 loops=1)
                     Index Cond: (assay1_ic50 > '90'::double precision)
 Planning time: 12.616 ms
 Execution time: 547039.910 ms


create index idx_data_json_assay1_ic50_j on json_test (((data -> 'assay1_ic50')::float));
ERROR:  cannot cast type jsonb to double precision
create index idx_data_json_assay2_ic50_j on json_test (((data -> 'assay2_ic50')::float));