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 1ikx2x-0001EF-3S for pgsql-bugs@arkaria.postgresql.org; Fri, 27 Dec 2019 21:21:23 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1ikx2u-0005RZ-KP for pgsql-bugs@arkaria.postgresql.org; Fri, 27 Dec 2019 21:21:20 +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 1ikx2u-0005RR-Bn for pgsql-bugs@lists.postgresql.org; Fri, 27 Dec 2019 21:21:20 +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 1ikx2o-0003QI-Re for pgsql-bugs@lists.postgresql.org; Fri, 27 Dec 2019 21:21:17 +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 xBRLL9jU003681; Fri, 27 Dec 2019 16:21:09 -0500 From: Tom Lane To: Zhihong Zhang cc: Jeff Janes , Tomas Vondra , pgsql-bugs Subject: Re: Indexing on JSONB field not working In-reply-to: <31E640F0-7115-4157-AED9-8F2D8C622D2B@gmail.com> References: <0cf001d5b77f$bfe47e10$3fad7a30$@gmail.com> <20191220223005.f6w4ff4kdbgtanpz@development> <83E6A3F7-B1B4-4AD6-905A-BE11D244D35A@gmail.com> <31E640F0-7115-4157-AED9-8F2D8C622D2B@gmail.com> Comments: In-reply-to Zhihong Zhang message dated "Fri, 27 Dec 2019 14:43:29 -0500" MIME-Version: 1.0 Content-Type: text/plain; charset="UTF-8" Content-ID: <3679.1577481669.1@sss.pgh.pa.us> Content-Transfer-Encoding: quoted-printable Date: Fri, 27 Dec 2019 16:21:09 -0500 Message-ID: <3680.1577481669@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk Zhihong Zhang writes: >> On Dec 27, 2019, at 12:45 PM, Jeff Janes wrote: >> Can you do `pg_dump -s -t assets ...` to get a full description of the= table? If you don't want to share the full description, make sure whatev= er simplifications you do to it don't prevent the problem from reproducing= . > I created a new table =E2=80=98assets_copy=E2=80=99 with only 3 columns.= I can still reproduce the problem. The pg_dump output is attached. I'm a little suspicious of this because it says the table owner is "postgres", but you say you're not superuser. Does RDS create a non-superuser "postgres" account? This is odd too: > -- Dumped from database version 11.4 > -- Dumped by pg_dump version 11.6 But anyway, I loaded this dump into v11 as a non-superuser, and inserted some data, specifically insert into assets_copy select 'id', ('{"floatValue": ' || random()*1e6 ||= ', "junk": "z"}')::jsonb, random()*1e6 from generate_series(1,100000) x; and I find that either auto-analyze or a manual ANALYZE will create stats that I can see in pg_stats and that lead to sane planner estimates. Now, I'm testing with a recent 11-branch build, so it's theoretically possible that some post-11.4 bug fix has changed the behavior, but I've looked through the git logs quite carefully and I see nothing that would have affected a query referencing the table directly. (If you were going through a view or an inheritance/partitioning parent, that might be a different story.) It seems like the only remaining theory is that the RDS version of Postgres differs from community Postgres in some relevant way. Even that theory has problems explaining why Jeff couldn't duplicate the behavior on his RDS instance, but we're really out of reasons for this not to work. Perhaps it's time for you to take up this question with RDS support. regards, tom lane