Re: Indexing on JSONB field not working

On Dec 20, 2019, at 5:30 PM, Tomas Vondra <tomas.vondra@2ndquadrant.com> wrote:

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