pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tomas Vondra <tomas.vondra@2ndquadrant.com>
To: Zhihong Zhang <zhihong@gmail.com>
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: Indexing on JSONB field not working
Date: Fri, 20 Dec 2019 23:30:05 +0100
Message-ID: <20191220223005.f6w4ff4kdbgtanpz@development> (raw)
In-Reply-To: <0cf001d5b77f$bfe47e10$3fad7a30$@gmail.com>
References: <0cf001d5b77f$bfe47e10$3fad7a30$@gmail.com>

On Fri, Dec 20, 2019 at 04:52:17PM -0500, Zhihong Zhang 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,
>
>
>
>explain select id, _doc->>'floatValue' from assets where (_doc #>
>'{floatValue}'::text[])::double precision < 3.0 limit 3;
>
>
>
>Limit  (cost=0.00..3.24 rows=3 width=53)
>
>  ->  Seq Scan on assets  (cost=0.00..936605.40 rows=867607 width=53)
>
>        Filter: (((_doc #> '{floatValue}'::text[]))::double precision <
>'3'::double precision)
>
>

The index scan is likely expected to be more expensive than the plain
sequential scan with the LIMIT interrupting it pretty much right away
(it's expected to scan only ~0.0003% of the table.

You can probably push the database to use the index by disabling
sequential scans, i.e.

   SET enable_seqscan = off;

and then doing the explain again.

The interesting question however is which of the plans is faster. It's
quite possible the database is making the right choice - index scans are
not necessarily faster.

>
>The version of the database,
>
>
>
>"PostgreSQL 11.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.2
>20140120 (Red Hat 4.8.2-16), 64-bit"
>
>
>
>However, the index works for text field in JSONB.
>

Well, the text field probably has different statistics, so the
sequential scan would have so scan much larger part of the table. Who
knows - you haven't shared the execution plans.

>
>
>Let me know if I can provide more information.
>

Show us explain analyze for both queries, with both index-scan and
seq-scan (you'll have to use enable_seqscan and enable_indexscan to
force the plan choice).


regards

-- 
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services 





view thread (43+ messages)  latest in thread

Message-ID: <20191220223005.f6w4ff4kdbgtanpz@development>
Permalink:  ../20191220223005.f6w4ff4kdbgtanpz@development/
Also on:    postgresql.org/message-id/20191220223005.f6w4ff4kdbgtanpz@development

 · 

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: tomas.vondra@2ndquadrant.com, zhihong@gmail.com
  Subject: Re: Indexing on JSONB field not working
  In-Reply-To: <20191220223005.f6w4ff4kdbgtanpz@development>
  References: <0cf001d5b77f$bfe47e10$3fad7a30$@gmail.com> <20191220223005.f6w4ff4kdbgtanpz@development>

* 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