agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Tom Lane <tgl@sss.pgh.pa.us>
To: Tim Dudgeon <tdudgeon.ml@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: querying with index on jsonb slower than standard column. Why?
Date: Sun, 07 Dec 2014 20:28:10 -0500
Message-ID: <16147.1418002090@sss.pgh.pa.us> (raw)
In-Reply-To: <5484F437.2080402@gmail.com>
References: <5484DBDA.6090405@gmail.com>
<5484EEA7.1030403@aklaver.com>
<5484F437.2080402@gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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)
Recheck Cond: ((((data ->> 'assay2_ic50'::text))::double precision < 10::double precision) AND (((data ->> 'assay1_ic50'::text))::double precision > 90::double precision))
Heap Blocks: exact=77578
-> BitmapAnd (cost=123684.69..123684.69 rows=1111111 width=0) (actual time=476.585..476.585 rows=0 loops=1)
-> Bitmap Index Scan on idx_data_json_assay2_ic50 (cost=0.00..61564.44 rows=3333333 width=0) (actual time=219.287..219.287 rows=999795 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.44 rows=3333333 width=0) (actual time=208.197..208.197 rows=1000231 loops=1)
Index Cond: (((data ->> 'assay1_ic50'::text))::double precision > 90::double precision)
Planning time: 0.128 ms
Execution 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)
Recheck Cond: ((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) (actual time=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.066 rows=1000231 loops=1)
Index Cond: (assay1_ic50 > 90::double precision)
Planning time: 0.129 ms
Execution 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
--
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: <16147.1418002090@sss.pgh.pa.us>
Permalink: ../16147.1418002090@sss.pgh.pa.us/
Also on: postgresql.org/message-id/16147.1418002090@sss.pgh.pa.us
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: tgl@sss.pgh.pa.us, tdudgeon.ml@gmail.com
Subject: Re: querying with index on jsonb slower than standard column. Why?
In-Reply-To: <16147.1418002090@sss.pgh.pa.us>
* 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