Re: Indexing on JSONB field not working

On Dec 30, 2019, at 11:31 AM, Zhihong Zhang <zhihong@gmail.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