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)
Thanks for all your help! I will keep digging till I find the cause.
Zhihong