"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
copiedto a column. That’s how we have been coping with this issue, simply moving the field to a column but now we got
toomany 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
