Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1Xxn7a-00021k-Ls for pgsql-sql@arkaria.postgresql.org; Mon, 08 Dec 2014 01:28:18 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1Xxn7a-0007fq-1V for pgsql-sql@arkaria.postgresql.org; Mon, 08 Dec 2014 01:28:18 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1Xxn7Z-0007fQ-CU for pgsql-sql@postgresql.org; Mon, 08 Dec 2014 01:28:17 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by magus.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1Xxn7V-0001gN-Id for pgsql-sql@postgresql.org; Mon, 08 Dec 2014 01:28:15 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.4/8.14.4) with ESMTP id sB81SAIv016148; Sun, 7 Dec 2014 20:28:10 -0500 From: Tom Lane To: Tim Dudgeon cc: pgsql-sql@postgresql.org Subject: Re: querying with index on jsonb slower than standard column. Why? In-reply-to: <5484F437.2080402@gmail.com> References: <5484DBDA.6090405@gmail.com> <5484EEA7.1030403@aklaver.com> <5484F437.2080402@gmail.com> Comments: In-reply-to Tim Dudgeon message dated "Sun, 07 Dec 2014 21:43:35 -0300" Date: Sun, 07 Dec 2014 20:28:10 -0500 Message-ID: <16147.1418002090@sss.pgh.pa.us> X-Pg-Spam-Score: -1.9 (-) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-sql Precedence: bulk Sender: pgsql-sql-owner@postgresql.org Tim Dudgeon 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