A few clarifications,
1. The index is very effective. If I treat it as text, the index works. With index, the result returns in 1 second. Otherwise, it’s 5 minutes.
2. Removing limit doesn’t change the behavior.
3. I ran Analyze multiple times after indexing.
Zhihong
I have an index on JSONB fields like this,
CREATE INDEX float_number_index_path2
ON public.assets USING btree
(((_doc #> '{floatValue}'::text[])::double precision) ASC NULLS LAST)
TABLESPACE pg_default;
However query doesn’t use it,
Did you analyze the table after building the index? Expression indexes have their own statistics, but they don't get populated until the table is analyzed.
Cheers,
Jeff