From: Tom Lane <tgl@sss.pgh.pa.us>
To: Zhihong Zhang <zhihong@gmail.com>
Cc: 'Tomas Vondra' <tomas.vondra@2ndquadrant.com>
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: Indexing on JSONB field not working
Date: Fri, 27 Dec 2019 12:45:32 -0500
Message-ID: <11610.1577468732@sss.pgh.pa.us> (raw)
In-Reply-To: <167501d5bcdb$13854b20$3a8fe160$@gmail.com>
References: <0cf001d5b77f$bfe47e10$3fad7a30$@gmail.com>
<20191220223005.f6w4ff4kdbgtanpz@development>
<E3D6070E-72E0-4FE5-9E30-D114530F383F@gmail.com>
<20191221114003.yo56lv3o6thdvxtn@development>
<21855.1576943356@sss.pgh.pa.us>
<0AA828F3-5BA5-48CD-B229-8351ACB308D3@gmail.com>
<167501d5bcdb$13854b20$3a8fe160$@gmail.com>
"Zhihong Zhang" <zhihong@gmail.com> writes:
> I forgot to emphasize that this problem only happens with JSONB index. The index always works if the same field is copied to a column. That’s how we have been coping with this issue, simply moving the field to a column 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 trying
* to identify which column(s) the index
* depends on. Also require all rows to be
* selectable --- there must be no
* securityQuals from security barrier views
* 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, zhihong@gmail.com, tomas.vondra@2ndquadrant.com
Subject: Re: Indexing on JSONB field not working
In-Reply-To: <11610.1577468732@sss.pgh.pa.us>
References: <0cf001d5b77f$bfe47e10$3fad7a30$@gmail.com> <20191220223005.f6w4ff4kdbgtanpz@development> <E3D6070E-72E0-4FE5-9E30-D114530F383F@gmail.com> <20191221114003.yo56lv3o6thdvxtn@development> <21855.1576943356@sss.pgh.pa.us> <0AA828F3-5BA5-48CD-B229-8351ACB308D3@gmail.com> <167501d5bcdb$13854b20$3a8fe160$@gmail.com> <11610.1577468732@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox