Re: Indexing on JSONB field not working

Zhihong Zhang <[email protected]> Thu, 2 Jan 2020 17:07:28 -0500

Newsgroups	gmane.comp.db.postgresql.bugs
Message-ID	<[email protected]>

> On Jan 2, 2020, at 4:58 PM, Tom Lane <[email protected]> wrote:
> 
> Zhihong Zhang <[email protected]> writes:
>> So I like to report 2 bugs,
> 
>> 1. “CREATE INDEX” with expression should generate stats automatically. 
> 
> It does.
I created an index with JSONB field yesterday. It still didnn’t have stats today till I ran ‘analyze’. 

> 
>> 2. Auto-analyze should generate stats if it is not available, regardless of the number of records changed.
> 
> This doesn't square with anyone else's reality either.

We have auto-analyze running every night and it didn’t create the stats either. I think it’s probably related to the logic that it skips analyzing if not enough records changed. There was no database changes at all in our case.

I can reproduce those problems easily. Just tell me what information you need to help debugging and I will get it for you. Thanks!

Zhihong