pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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

view thread (43+ messages)  latest in thread

Message-ID: <9499358C-9E3A-4050-901F-BA988FC286FC@gmail.com>
Permalink:  ../9499358C-9E3A-4050-901F-BA988FC286FC@gmail.com/
Also on:    postgresql.org/message-id/9499358C-9E3A-4050-901F-BA988FC286FC@gmail.com

 · 

reply

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