On Thu, Jan 2, 2020 at 4:49 PM Zhihong Zhang <zhihong@gmail.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.