Re: Indexing on JSONB field not working
Zhihong Zhang <[email protected]> Thu, 2 Jan 2020 17:30:23 -0500
| Newsgroups | gmane.comp.db.postgresql.bugs |
|---|---|
| Message-ID | <[email protected]> |
> On Jan 2, 2020, at 4:58 PM, Tom Lane <[email protected]> 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)