Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1iih0U-0004c2-9F for pgsql-bugs@arkaria.postgresql.org; Sat, 21 Dec 2019 15:49:30 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1iih0S-0003Pc-Cw for pgsql-bugs@arkaria.postgresql.org; Sat, 21 Dec 2019 15:49:28 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1iih0S-0003OB-4H for pgsql-bugs@lists.postgresql.org; Sat, 21 Dec 2019 15:49:28 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1iih0L-0007v8-OU for pgsql-bugs@lists.postgresql.org; Sat, 21 Dec 2019 15:49:27 +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 xBLFnGHn021856; Sat, 21 Dec 2019 10:49:17 -0500 From: Tom Lane To: Tomas Vondra cc: Zhihong Zhang , pgsql-bugs@lists.postgresql.org Subject: Re: Indexing on JSONB field not working In-reply-to: <20191221114003.yo56lv3o6thdvxtn@development> References: <0cf001d5b77f$bfe47e10$3fad7a30$@gmail.com> <20191220223005.f6w4ff4kdbgtanpz@development> <20191221114003.yo56lv3o6thdvxtn@development> Comments: In-reply-to Tomas Vondra message dated "Sat, 21 Dec 2019 12:40:03 +0100" MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-ID: <21854.1576943356.1@sss.pgh.pa.us> Content-Transfer-Encoding: quoted-printable Date: Sat, 21 Dec 2019 10:49:16 -0500 Message-ID: <21855.1576943356@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk Tomas Vondra writes: > On Fri, Dec 20, 2019 at 08:15:39PM -0500, Zhihong Zhang wrote: >> "Limit (cost=3D0.43..313.25 rows=3D100 width=3D53) (actual time=3D0.09= 2..0.236 rows=3D7 loops=3D1)" >> " -> Index Scan using assets_floatvalue_idx on assets (cost=3D0.43..= 2714072.57 rows=3D867607 width=3D53) (actual time=3D0.089..0.230 rows=3D7 = loops=3D1)" >> " Index Cond: (((_doc #> '{floatValue}'::text[]))::double precis= ion < '3'::double precision)" > Well, this confirms what I suspected before - the optimizer believes the > seqscan plan is a bit cheaper compared to index scan (107 vs. 313) but > that mismatches the actual performance. The question is why ... The planner evidently believes that 867607 rows will match the query condition, so it expects that the scan will stop (after collecting 100 rows) very quickly. In reality only 7 rows match, so the scan has to run to completion. This is what's bollixing the plan choice. I suspect that 867607 is just a default estimate, but if ANALYZE has been run then there should be stats for the index column, so why isn't it doing better? When I try a similar case here, I get good estimates: regression=3D# select version(); version = = --------------------------------------------------------------------------= ------------------------------- PostgreSQL 11.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 20120= 313 (Red Hat 4.4.7-23), 64-bit (1 row) regression=3D# create table public.assets(_doc jsonb); CREATE TABLE regression=3D# CREATE INDEX on assets (((_doc #> '{floatValue}'::text[])::= double precision) ASC NULLS LAST); CREATE INDEX regression=3D# insert into assets select ('{"floatValue": ' || x || '}')::= jsonb from generate_series(1,10000) x; INSERT 0 10000 regression=3D# explain select _doc->>'floatValue' from assets where (_doc = #> '{floatValue}'::text[])::double precision < 3.0 limit 100; QUERY PLAN = = --------------------------------------------------------------------------= ---------------------- Limit (cost=3D0.00..7.71 rows=3D100 width=3D32) -> Seq Scan on assets (cost=3D0.00..293.44 rows=3D3808 width=3D32) Filter: (((_doc #> '{floatValue}'::text[]))::double precision < '= 3'::double precision) (3 rows) regression=3D# analyze assets; ANALYZE regression=3D# explain select _doc->>'floatValue' from assets where (_doc = #> '{floatValue}'::text[])::double precision < 3.0 limit 100; QUERY PLAN = = --------------------------------------------------------------------------= -------------------------- Limit (cost=3D0.29..8.33 rows=3D2 width=3D32) -> Index Scan using assets_float8_idx on assets (cost=3D0.29..8.33 ro= ws=3D2 width=3D32) Index Cond: (((_doc #> '{floatValue}'::text[]))::double precision= < '3'::double precision) (3 rows) The "3808" estimate is just a default for '<' with no stats, but with stats I get a dead-on estimate. regards, tom lane