Re: Indexing on JSONB field not working

On Fri, Dec 20, 2019 at 7:39 PM Zhihong Zhang <[email protected]> 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.

Cheers,

Jeff