From: Zhihong Zhang <zhihong@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Jeff Janes <jeff.janes@gmail.com>
Cc: Tomas Vondra <tomas.vondra@2ndquadrant.com>
Cc: pgsql-bugs <pgsql-bugs@lists.postgresql.org>
Subject: Re: Indexing on JSONB field not working
Date: Thu, 2 Jan 2020 17:30:23 -0500
Message-ID: <690FAE53-5851-4B64-9B3B-1E37A242F440@gmail.com> (raw)
In-Reply-To: <18649.1578002294@sss.pgh.pa.us>
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>
<11610.1577468732@sss.pgh.pa.us>
<202A204F-FE4D-414A-ABD0-B7773A0C7316@gmail.com>
<CAMkU=1y+D8SyZ+MOQAsJN_4HRm5QaLWFgKe2MyXbkbz0d5fmrA@mail.gmail.com>
<CE20A918-FDF4-4D43-A969-6CBB70B69164@gmail.com>
<CAMkU=1waicvb_FODcbJeZ8sO4EqTu7wZX4rQKu88AKxoCy97Zw@mail.gmail.com>
<A69DA80B-B5A4-4594-8178-55FB5761B386@gmail.com>
<39B1D245-31C9-407E-8136-514890B92CBA@gmail.com>
<18649.1578002294@sss.pgh.pa.us>
> On Jan 2, 2020, at 4:58 PM, Tom Lane <tgl@sss.pgh.pa.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)
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, jeff.janes@gmail.com, tomas.vondra@2ndquadrant.com
Subject: Re: Indexing on JSONB field not working
In-Reply-To: <690FAE53-5851-4B64-9B3B-1E37A242F440@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> <11610.1577468732@sss.pgh.pa.us> <202A204F-FE4D-414A-ABD0-B7773A0C7316@gmail.com> <CAMkU=1y+D8SyZ+MOQAsJN_4HRm5QaLWFgKe2MyXbkbz0d5fmrA@mail.gmail.com> <CE20A918-FDF4-4D43-A969-6CBB70B69164@gmail.com> <CAMkU=1waicvb_FODcbJeZ8sO4EqTu7wZX4rQKu88AKxoCy97Zw@mail.gmail.com> <A69DA80B-B5A4-4594-8178-55FB5761B386@gmail.com> <39B1D245-31C9-407E-8136-514890B92CBA@gmail.com> <18649.1578002294@sss.pgh.pa.us> <690FAE53-5851-4B64-9B3B-1E37A242F440@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