Re: Indexing on JSONB field not working

Zhihong Zhang <zhihong(at)gmail(dot)com> writes:
>> On Dec 27, 2019, at 12:45 PM, Jeff Janes <jeff(dot)janes(at)gmail(dot)com> 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 whatever simplifications you do to it don't prevent the problem from reproducing.

> I created a new table ‘assets_copy’ 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