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 <jeff(dot)janes(at)gmail(dot)com> wrote:
>
> On Fri, Dec 20, 2019 at 5:12 PM Zhihong Zhang <zhihong(at)gmail(dot)com <mailto:zhihong(at)gmail(dot)com>> 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