From: Zhihong Zhang <zhihong@gmail.com>
To: pgsql-bugs@lists.postgresql.org
Subject: Indexing on JSONB field not working
Date: Fri, 20 Dec 2019 16:52:17 -0500
Message-ID: <0cf001d5b77f$bfe47e10$3fad7a30$@gmail.com> (raw)
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 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.
Let me know if I can provide more information.
Zhihong Zhang
zhihong@gmail.com
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
Subject: Re: Indexing on JSONB field not working
In-Reply-To: <0cf001d5b77f$bfe47e10$3fad7a30$@gmail.com>
References: <0cf001d5b77f$bfe47e10$3fad7a30$@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