On Fri, Dec 20, 2019 at 7:39 PM Zhihong Zhang <zhihong@gmail.com> wrote:
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.
An index is over a datatype. You can't just "treat an index like text". Either it is an index over text, or it is not. It you cast an expression to text while defining the index, then that is a different index than if you defined it with a cast to double precision.
2. Removing limit doesn’t change the behavior.
I have no problem getting your index to be used with or without the LIMIT , as long as the expression "(_doc #> '{floatValue}'::text[])::double precision < 3.0" is selective enough that it appears to be worth using an index for it. For example:
insert into assets (id,_doc) select x, jsonb_build_object('floatValue',random()*1000) from generate_series(1,1000000) f(x);
We can't comment on how two queries might differ, then we have only seen one of them.