RE: Indexing on JSONB field not working

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