From: | "Zhihong Zhang" <zhihong(at)gmail(dot)com> |
---|---|
To: | <pgsql-bugs(at)lists(dot)postgresql(dot)org> |
Subject: | Indexing on JSONB field not working |
Date: | 2019-12-20 21:52:17 |
Message-ID: | 0cf001d5b77f$bfe47e10$3fad7a30$@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
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(at)gmail(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Tomas Vondra | 2019-12-20 22:30:05 | Re: Indexing on JSONB field not working |
Previous Message | Tomas Zubiri | 2019-12-20 21:48:56 | Re: psql: Buggy interactions with wraparound in maximized gnome terminals. |