From: Zhihong Zhang <zhihong@gmail.com>
To: 'Tom Lane' <tgl@sss.pgh.pa.us>
Cc: 'Tomas Vondra' <tomas.vondra@2ndquadrant.com>
Cc: pgsql-bugs@lists.postgresql.org
Subject: RE: Indexing on JSONB field not working
Date: Fri, 27 Dec 2019 12:28:38 -0500
Message-ID: <167501d5bcdb$13854b20$3a8fe160$@gmail.com> (raw)
In-Reply-To: <0AA828F3-5BA5-48CD-B229-8351ACB308D3@gmail.com>
References: <0cf001d5b77f$bfe47e10$3fad7a30$@gmail.com>
<20191220223005.f6w4ff4kdbgtanpz@development>
<E3D6070E-72E0-4FE5-9E30-D114530F383F@gmail.com>
<20191221114003.yo56lv3o6thdvxtn@development>
<21855.1576943356@sss.pgh.pa.us>
<0AA828F3-5BA5-48CD-B229-8351ACB308D3@gmail.com>
I forgot to emphasize that this problem only happens with JSONB index. The index always works if the same field is copied to a column. That’s how we have been coping with this issue, simply moving the field to a column but now we got too many columns to deal with.
To prove this, I just created a new column 'floatValue' and copied the value from JSONB column (_doc).
alter table assets add column floatValue double precision;
update assets set floatValue = (_doc #> '{floatValue}'::text[])::double precision;
create index floatValue_idx on assets (floatValue);
analyze;
Now the query on the column automatically uses the index,
explain analyze select id, _doc->>'floatValue' from assets where floatValue < 3.0 limit 100;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.43..28.55 rows=6 width=53) (actual time=0.571..19.557 rows=7 loops=1)
-> Index Scan using floatvalue_idx on assets (cost=0.43..28.55 rows=6 width=53) (actual time=0.569..19.551 rows=7 loops=1)
Index Cond: (floatvalue < '3'::double precision)
Planning Time: 0.322 ms
Execution Time: 19.583 ms
(5 rows)
Since I can’t get stats on the index, wonder if the stats on the column is of any help.
select * from pg_stats where tablename='assets' and attname='floatvalue'
;
-[ RECORD 1 ]----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
schemaname | public
tablename | assets
attname | floatvalue
inherited | f
null_frac | 3.33333e-05
avg_width | 8
n_distinct | -0.999967
most_common_vals |
most_common_freqs |
histogram_bounds | {29.3762423098087,11544.743552804,22327.7248442173,31746.9625733793,42020.0857333839,53353.4693531692,62746.0754476488,71412.5884696841,82250.3692470491,92105.7304367423,101821.479853243,111723.829060793,122510.320041329,132316.885981709,142764.132469893,153306.225780398,162952.687591314,174045.353196561,184753.799811006,195106.332655996,205185.326747596,215598.905924708,225703.200791031,235017.722006887,244950.931984931,255437.318701297,265914.741437882,275904.242414981,286097.948905081,296642.523724586,307137.258350849,317328.266333789,328417.54052788,337816.804181784,348164.874594659,358840.349595994,368590.213824064,378130.563534796,387764.143757522,398006.183560938,407868.383917958,417695.778422058,427624.609787017,437821.539118886,447861.317079514,457633.044105023,467341.55761078,477082.30279386,486012.877896428,496032.587718219,507104.239426553,517111.513298005,526737.637352198,536167.487502098,545932.489912957,555894.987657666,565314.109902829,574438.83176893,584489.227738231,593775.179702789,603509.965352714,613848.30949828,624749.070033431,635505.4769665,644715.944770724,654815.05356729,664933.282416314,673889.273777604,683789.047412574,693903.816863894,703375.991433859,713953.300379217,724327.767267823,733478.933107108,744359.858334064,755353.817716241,764262.578450143,773850.545287132,783374.56099689,793155.808001757,803004.199638963,812858.935445547,822967.322077602,832813.539542258,843135.526403785,853274.697437882,862586.844246835,872572.991997004,882436.727173626,891319.798305631,900905.375834554,910936.662461609,921490.86529389,930878.716986626,940496.629569679,949594.719801098,959338.55464682,969633.623026311,980396.131519228,989476.628601551,999952.361918986}
correlation | -0.00371463
most_common_elems |
most_common_elem_freqs |
elem_count_histogram |
Thanks!
Zhihong
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, tgl@sss.pgh.pa.us, tomas.vondra@2ndquadrant.com
Subject: Re: Indexing on JSONB field not working
In-Reply-To: <167501d5bcdb$13854b20$3a8fe160$@gmail.com>
References: <0cf001d5b77f$bfe47e10$3fad7a30$@gmail.com> <20191220223005.f6w4ff4kdbgtanpz@development> <E3D6070E-72E0-4FE5-9E30-D114530F383F@gmail.com> <20191221114003.yo56lv3o6thdvxtn@development> <21855.1576943356@sss.pgh.pa.us> <0AA828F3-5BA5-48CD-B229-8351ACB308D3@gmail.com> <167501d5bcdb$13854b20$3a8fe160$@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