From: Zhihong Zhang <zhihong@gmail.com>
To: Jeff Janes <jeff.janes@gmail.com>
Cc: pgsql-bugs <pgsql-bugs@lists.postgresql.org>
Subject: Re: Indexing on JSONB field not working
Date: Fri, 20 Dec 2019 19:39:28 -0500
Message-ID: <9499358C-9E3A-4050-901F-BA988FC286FC@gmail.com> (raw)
In-Reply-To: <CAMkU=1zPbphLfucRN7j7bAwb=9YAGB0cucc_t_MaqKk+rwYTQw@mail.gmail.com>
References: <0cf001d5b77f$bfe47e10$3fad7a30$@gmail.com>
<CAMkU=1zPbphLfucRN7j7bAwb=9YAGB0cucc_t_MaqKk+rwYTQw@mail.gmail.com>
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.janes@gmail.com> wrote:
>
> On Fri, Dec 20, 2019 at 5:12 PM Zhihong Zhang <zhihong@gmail.com <mailto:zhihong@gmail.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-bugs@postgresql.org
Cc: zhihong@gmail.com, jeff.janes@gmail.com
Subject: Re: Indexing on JSONB field not working
In-Reply-To: <9499358C-9E3A-4050-901F-BA988FC286FC@gmail.com>
References: <0cf001d5b77f$bfe47e10$3fad7a30$@gmail.com> <CAMkU=1zPbphLfucRN7j7bAwb=9YAGB0cucc_t_MaqKk+rwYTQw@mail.gmail.com> <9499358C-9E3A-4050-901F-BA988FC286FC@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox