Re: Indexing on JSONB field not working

On Thu, Jan 2, 2020 at 4:49 PM Zhihong Zhang <zhihong(at)gmail(dot)com> wrote:

> I think the root cause of my problem is that the “CREATE INDEX” with
> expression wouldn’t generate stats automatically. Running ‘Analyze’
> manually solves the problem in most cases but sometimes I have to restart
> the server.
>

You told us vociferously that ANALYZE was run after the index was created.

> So I like to report 2 bugs,
>
> 1. “CREATE INDEX” with expression should generate stats automatically.
>

I thought we did do something about that in a recent release, having the
CREATE INDEX send some message to the stats collector which would cause the
autovacuum launcher to analyze it the next time nap-time was up. But I
can't find it in the commit log, nor find the behavior. Maybe this was
discussed but not implemented.

>
> 2. Auto-analyze should generate stats if it is not available, regardless
> of the number of records changed.
>

If the table gets auto-analyzed, then it does generate the stats. But it
doesn't fire auto-analyze just to generate those stats. Is your complaint
that it doesn't run, or that it does run but doesn't collect the stats? If
the latter, then no one can reproduce it, other than through permissions
issues which apparently don't apply to you.

Cheers,

Jeff