Re: Indexing on JSONB field not working

> On Jan 2, 2020, at 4:58 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> You haven't given us one single bit of information about what you are
> doing differently that might trigger such unexpected behavior. But without
> that, there's not much we can do to investigate this report.

Attached is the screen capture of my test sequence. This is what I did,

1. Start with the table without the JSONB index. Run select, no stats as expected.
2. Create index
3. Wait days, I actually did step 2 last year :)
4. Run select and still no stats. “Create index” or auto vacuum should take care of this, right?
4. Run ‘analyze’.
5. Run select again and the stats shows up.

Let me know if I should have done anything differently to get the desired result.

Thanks!

Zhihong

catalog_load_test=> select * from pg_stats where tablename='float_number_index_path2';
schemaname | tablename | attname | inherited | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation | most_common_elems | most_common_elem_freqs | elem
_count_histogram
------------+-----------+---------+-----------+-----------+-----------+------------+------------------+-------------------+------------------+-------------+-------------------+------------------------+-----
-----------------
(0 rows)
catalog_load_test=> CREATE INDEX float_number_index_path2
catalog_load_test-> ON public.assets USING btree
catalog_load_test-> (((_doc #> '{floatValue}'::text[])::double precision) ASC NULLS LAST)
catalog_load_test-> TABLESPACE pg_default;
CREATE INDEX
------------------ Days later -------------------------
catalog_load_test=> select * from pg_stats where tablename='float_number_index_path2';
schemaname | tablename | attname | inherited | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation | most_common_elems | most_common_elem_freqs | elem
_count_histogram
------------+-----------+---------+-----------+-----------+-----------+------------+------------------+-------------------+------------------+-------------+-------------------+------------------------+-----
-----------------
(0 rows)
catalog_load_test=> analyze;
WARNING: skipping "pg_authid" --- only superuser can analyze it
WARNING: skipping "pg_subscription" --- only superuser can analyze it
WARNING: skipping "pg_database" --- only superuser can analyze it
WARNING: skipping "pg_db_role_setting" --- only superuser can analyze it
WARNING: skipping "pg_tablespace" --- only superuser can analyze it
WARNING: skipping "pg_pltemplate" --- only superuser can analyze it
WARNING: skipping "pg_auth_members" --- only superuser can analyze it
WARNING: skipping "pg_shdepend" --- only superuser can analyze it
WARNING: skipping "pg_shdescription" --- only superuser can analyze it
WARNING: skipping "pg_replication_origin" --- only superuser can analyze it
WARNING: skipping "pg_shseclabel" --- only superuser can analyze it
NOTICE: no non-null/empty features, unable to compute statistics
NOTICE: no non-null/empty features, unable to compute statistics
ANALYZE
catalog_load_test=> select * from pg_stats where tablename='float_number_index_path2';
schemaname | tablename | attname | inherited | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs |
histogram_bounds
| correl
ation | most_common_elems | most_common_elem_freqs | elem_count_histogram
------------+--------------------------+---------+-----------+-----------+-----------+------------+------------------+-------------------+--------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------
------+-------------------+------------------------+----------------------
public | float_number_index_path2 | float8 | f | 0 | 8 | -0.994275 | | | {67.9828226566315,9880.58233633637,19484.4105280936,29129.853006452
3,38355.4133586586,47072.1824094653,57639.1913928092,67335.2866433561,76439.0416443348,86354.6561449766,96093.4087634087,106792.015489191,117061.504628509,125703.127589077,135128.147900105,145247.848238796,
155961.862299591,166463.26566115,176670.81207037,187789.324205369,197144.483681768,207021.271344274,216668.588574976,227840.67876637,237936.515826732,247922.076378018,257857.841439545,267939.695157111,27702
5.00205487,287662.557791919,297872.847877443,309003.66185233,319923.490285873,330532.386898994,339468.085207045,349151.492118835,358672.737609595,369132.092688233,378833.52348581,388865.450397134,399013.585
876673,407844.387926161,418233.385775238,427318.078000098,438366.677146405,448453.094344586,458905.486389995,468482.088763267,478278.840426356,487119.99412626,495660.125277936,505299.935583025,515099.597163
498,525450.137443841,536038.665566593,546619.640663266,556582.688819617,566576.57166943,576369.696762413,587215.536739677,597477.190662175,606301.207095385,616701.394319534,626550.197601318,635750.317480415
,646002.440713346,655989.156104624,667213.554959744,677401.37828514,687671.223655343,698001.290205866,707360.081840307,716366.450302303,726345.336064696,735977.729782462,745276.737492532,755845.261737704,76
5410.838183016,774972.880259156,785988.09055984,795699.819922447,805251.396726817,815074.041485786,824808.841571212,835754.215717316,846041.257493198,856068.658642471,866092.296782881,876734.56966877,885931
.862983853,896236.94261536,907013.318967074,916161.817498505,927834.809292108,938955.033197999,948850.627522916,958572.782110423,968669.227790087,978541.388176382,989408.961031586,999981.255270541} | -0.01
26945 | | |
(1 row)