Re: Indexing on JSONB field not working

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


> On Dec 20, 2019, at 5:57 PM, Jeff Janes <[email protected]> wrote:
> 
> On Fri, Dec 20, 2019 at 5:12 PM Zhihong Zhang <[email protected] <mailto:[email protected]>> wrote:
> 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