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 1ikVA9-0000Ql-OM for pgsql-bugs@arkaria.postgresql.org; Thu, 26 Dec 2019 15:34:57 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1ikVA6-0007i2-VT for pgsql-bugs@arkaria.postgresql.org; Thu, 26 Dec 2019 15:34:54 +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 1ikUtl-0008UG-QF for pgsql-bugs@lists.postgresql.org; Thu, 26 Dec 2019 15:18:01 +0000 Received: from mail-qk1-x741.google.com ([2607:f8b0:4864:20::741]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1ikUtf-0006SJ-5m for pgsql-bugs@lists.postgresql.org; Thu, 26 Dec 2019 15:18:01 +0000 Received: by mail-qk1-x741.google.com with SMTP id c16so19620396qko.6 for ; Thu, 26 Dec 2019 07:17:54 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=mime-version:subject:from:in-reply-to:date:cc :content-transfer-encoding:message-id:references:to; bh=8HFRNjhJuWdle/g/qX0ej3PDOqLHHMAm1zwQElyPItA=; b=d5AWW26R75mMndcSUelktMZ8DFRaWQx8S+mFL0cS2Ev715La1x/jj0I12vOL6SVycW SgrPT918pFKX82SNHTnBzRLdB4zsPGczfrEl+4SLX3SkT6vmvkAhN+WZ2o3cKc/F6IUq YXh1QCucBXgTTv/Id04Xxee+XdSO0Zi4fgNY+NhBPH7YGT1xfivkqlCZ6brveU5mpjdp 2eviawGVGZb/4+Gc51wQwzsybIqhmvdxY8xFr2ncqcS0Sw6Ctb1khABZQuTUAcgryl5v W4FHSZRcNyzAjVgm9PLMXo8CRG5tsyjtkUrRlx0HFHZO981WzEHpHqkkpmDvOQaREw3T nomw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:mime-version:subject:from:in-reply-to:date:cc :content-transfer-encoding:message-id:references:to; bh=8HFRNjhJuWdle/g/qX0ej3PDOqLHHMAm1zwQElyPItA=; b=qoBZOVVQlJUNN1W+TKcUDCBVQiA67OnNtHaCH5lo7mP/djgVkQ9M5FfqAbjbmwi6a2 FagABXMdDMUzg8nBLWj3hnZoRUe9T+VuY2IgjP4PU5pkIrcd9RiFSS07k3fx9UYfs8bu 4aSAIsWzpbajLKdHEBT05whKjdEOmdzo/7CNnVBeQYbnKvNO1aGFVyuKL7RLnCzCIZ9P A/1lgeg3KYtrXs4QDauwMnQJPDzNrunjMhKdUcR+rqoCeVkZWgnlXYtSe80XqyLv+s7C J8S92EajfXW4Q5XES6d8HjloTlKvigcZdawwuLVzsn4/FcZqUciLOYla2t4QH/MrYw1T VZFA== X-Gm-Message-State: APjAAAV4FGc7Mg59BT+qM7fUBFE0b/4X1Zl8pzNyEHvH6oRfoySs02Q+ ec0QhJfryWndwo6p1YWA1no= X-Google-Smtp-Source: APXvYqy7+G2J45hQXJoBBXlgJlwigTtHJ2emGoPtO4nRgk8Th4ZmrcZpng9JtTt59DoPIAmx7g5Rxw== X-Received: by 2002:a37:65c7:: with SMTP id z190mr37083260qkb.261.1577373473195; Thu, 26 Dec 2019 07:17:53 -0800 (PST) Received: from [10.8.0.6] (pool-173-79-120-3.washdc.fios.verizon.net. [173.79.120.3]) by smtp.gmail.com with ESMTPSA id z4sm9697694qta.73.2019.12.26.07.17.52 (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Thu, 26 Dec 2019 07:17:52 -0800 (PST) Content-Type: text/plain; charset=utf-8 Mime-Version: 1.0 (Mac OS X Mail 13.0 \(3608.40.2.2.4\)) Subject: Re: Indexing on JSONB field not working From: Zhihong Zhang In-Reply-To: <21855.1576943356@sss.pgh.pa.us> Date: Thu, 26 Dec 2019 10:17:51 -0500 Cc: Tomas Vondra , pgsql-bugs@lists.postgresql.org Content-Transfer-Encoding: quoted-printable Message-Id: <0AA828F3-5BA5-48CD-B229-8351ACB308D3@gmail.com> References: <0cf001d5b77f$bfe47e10$3fad7a30$@gmail.com> <20191220223005.f6w4ff4kdbgtanpz@development> <20191221114003.yo56lv3o6thdvxtn@development> <21855.1576943356@sss.pgh.pa.us> To: Tom Lane X-Mailer: Apple Mail (2.3608.40.2.2.4) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk Just came back from the long break and I couldn=E2=80=99t quite follow = the threads. Is this a bug or something I am doing wrong? If it=E2=80=99s a bug, can I have a tracking or ticket number? Thanks! Zhihong > On Dec 21, 2019, at 10:49 AM, Tom Lane wrote: >=20 > 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.092..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 = precision < '3'::double precision)" >=20 >> 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 ... >=20 > 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. >=20 > 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: >=20 > regression=3D# select version(); > version = =20 > = --------------------------------------------------------------------------= ------------------------------- > PostgreSQL 11.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 = 20120313 (Red Hat 4.4.7-23), 64-bit > (1 row) >=20 > 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 = =20 > = --------------------------------------------------------------------------= ---------------------- > 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) >=20 > 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 = =20 > = --------------------------------------------------------------------------= -------------------------- > Limit (cost=3D0.29..8.33 rows=3D2 width=3D32) > -> Index Scan using assets_float8_idx on assets (cost=3D0.29..8.33 = rows=3D2 width=3D32) > Index Cond: (((_doc #> '{floatValue}'::text[]))::double = precision < '3'::double precision) > (3 rows) >=20 > The "3808" estimate is just a default for '<' with no stats, but > with stats I get a dead-on estimate. >=20 > regards, tom lane