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 1iktgG-00025T-CH for pgsql-bugs@arkaria.postgresql.org; Fri, 27 Dec 2019 17:45:44 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1iktgF-0003VX-4s for pgsql-bugs@arkaria.postgresql.org; Fri, 27 Dec 2019 17:45:43 +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 1iktgE-0003VQ-Sl for pgsql-bugs@lists.postgresql.org; Fri, 27 Dec 2019 17:45:42 +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 1iktg9-0001l3-0z for pgsql-bugs@lists.postgresql.org; Fri, 27 Dec 2019 17:45:42 +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 xBRHjW0P011611; Fri, 27 Dec 2019 12:45:32 -0500 From: Tom Lane To: "Zhihong Zhang" cc: "'Tomas Vondra'" , pgsql-bugs@lists.postgresql.org Subject: Re: Indexing on JSONB field not working In-reply-to: <167501d5bcdb$13854b20$3a8fe160$@gmail.com> References: <0cf001d5b77f$bfe47e10$3fad7a30$@gmail.com> <20191220223005.f6w4ff4kdbgtanpz@development> <20191221114003.yo56lv3o6thdvxtn@development> <21855.1576943356@sss.pgh.pa.us> <0AA828F3-5BA5-48CD-B229-8351ACB308D3@gmail.com> <167501d5bcdb$13854b20$3a8fe160$@gmail.com> Comments: In-reply-to "Zhihong Zhang" message dated "Fri, 27 Dec 2019 12:28:38 -0500" MIME-Version: 1.0 Content-Type: text/plain; charset="UTF-8" Content-ID: <11609.1577468732.1@sss.pgh.pa.us> Content-Transfer-Encoding: quoted-printable Date: Fri, 27 Dec 2019 12:45:32 -0500 Message-ID: <11610.1577468732@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk "Zhihong Zhang" writes: > I forgot to emphasize that this problem only happens with JSONB index. T= he index always works if the same field is copied to a column. That=E2=80=99= s how we have been coping with this issue, simply moving the field to a co= lumn but now we got too many columns to deal with. Well, we're still up against the question of why this doesn't work for you when it does work for everyone else (or at least, everybody who's responded to this thread). You have yet to show us anything concrete about your table design, and it seems likely that the explanation is in some detail that you haven't mentioned. One thought that comes to mind is an aspect of the permissions issue: the optimizer won't use index stats unless you have permissions to select the whole table: * For simplicity, we insist on the whole * table being selectable, rather than try= ing * to identify which column(s) the index * depends on. Also require all rows to b= e * selectable --- there must be no * securityQuals from security barrier vie= ws * or RLS policies. I think we can eliminate the RLS-is-blocking-it idea, because that would also apply to stats on simple columns. But maybe your user only has select privilege on some columns of the table in question? regards, tom lane