Re: Indexing on JSONB field not working

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.

So I like to report 2 bugs,

1. “CREATE INDEX” with expression should generate stats automatically.
2. Auto-analyze should generate stats if it is not available, regardless of the number of records changed.

We have a large JSONB column. We don’t really know which fields are used by customer so we create indexes for those fields on demand when there is enough usage but it never worked. We were on the brink of switching to another database. Now with those findings, I can implement some workaround. Thanks for your help!

Zhihong

> On Dec 30, 2019, at 11:31 AM, Zhihong Zhang <zhihong(at)gmail(dot)com> wrote:
>
>>
>>
>> You will have to redo the EXPLAIN (ANALYZE, BUFFERS) now that you have stats, under both settings of enable_seqscan, and show us those.
>
>
> It’s working now! I really don’t know what’s changed. Other than tweaking a few unrelated parameters, all I did was bouncing the server a few times.
>
> The missing stats for the jsonb index is back also.
>
> We had the same problem on another RDS server in a different cloud also. Let me see if I can get access there and do some comparison.
>
> Here is the explain for the same query that took minutes before,
> SET enable_seqscan = on;
> SET
> catalog_load_test=> explain analyze select id, _doc->>'floatValue' from assets where (_doc #> '{floatValue}'::text[])::double precision < 3.0 limit 100;
> QUERY PLAN
> -----------------------------------------------------------------------------------------------------------------------------------------
> Limit (cost=0.43..28.55 rows=6 width=53) (actual time=0.046..0.177 rows=7 loops=1)
> -> Index Scan using float_number_index_path2 on assets (cost=0.43..28.55 rows=6 width=53) (actual time=0.045..0.172 rows=7 loops=1)
> Index Cond: (((_doc #> '{floatValue}'::text[]))::double precision < '3'::double precision)
> Planning Time: 0.274 ms
> Execution Time: 0.205 ms
> (5 rows)
>
> SET enable_seqscan = on;
> SET
> catalog_load_test=> explain analyze select id, _doc->>'floatValue' from assets where (_doc #> '{floatValue}'::text[])::double precision < 3.0 limit 100;
> QUERY PLAN
> -----------------------------------------------------------------------------------------------------------------------------------------
> Limit (cost=0.43..28.55 rows=6 width=53) (actual time=0.046..0.177 rows=7 loops=1)
> -> Index Scan using float_number_index_path2 on assets (cost=0.43..28.55 rows=6 width=53) (actual time=0.045..0.172 rows=7 loops=1)
> Index Cond: (((_doc #> '{floatValue}'::text[]))::double precision < '3'::double precision)
> Planning Time: 0.274 ms
> Execution Time: 0.205 ms
> (5 rows)
>
>
> Thanks for all your help! I will keep digging till I find the cause.
>
> Zhihong
>
>
>
>