Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1in8zD-0007Hw-9x for pgsql-bugs@arkaria.postgresql.org; Thu, 02 Jan 2020 22:30:35 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1in8zB-0005t3-R5 for pgsql-bugs@arkaria.postgresql.org; Thu, 02 Jan 2020 22:30:33 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1in8zB-0005sv-4K for pgsql-bugs@lists.postgresql.org; Thu, 02 Jan 2020 22:30:33 +0000 Received: from mail-qt1-x836.google.com ([2607:f8b0:4864:20::836]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1in8z3-0006kH-Fe for pgsql-bugs@lists.postgresql.org; Thu, 02 Jan 2020 22:30:31 +0000 Received: by mail-qt1-x836.google.com with SMTP id t3so35662225qtr.11 for ; Thu, 02 Jan 2020 14:30:25 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=from:message-id:mime-version:subject:date:in-reply-to:cc:to :references; bh=xNnLMHWPS1xxieNdA86ZDDwOGOGE1MGq2Oje7WFnTho=; b=VL7qH9LF47ohT0s9X4/z1UbXf3rrEOUX7sVIF7tUfigFkXp/lMRwqhDfpk/BRKtSX0 bHYemDbVhg3XJG2qAq2O+U1lyuzNn+m463bzyvxSiLATbo5XwFKENOn7edm9MDYQkEHM Sr0lKIHZqXO84yXFWIXaeYLSeMeD3Rr6FGlOKRmZ4iPG+ZR3AZunUxxt2qljWedY8kVt PaaMS6e5qdz6X9qndXyrN+YKUl5TBdJS6MKNZacdhc95SvLp12IHQLMtpo3Tbf0viojc PNO2hSkOXlJUMJ4n1U+wyPRm9MAbykSw0Ecq5j6d1qo3pC160Qj4QgWsfyXkESH+bQmw ikdQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:from:message-id:mime-version:subject:date :in-reply-to:cc:to:references; bh=xNnLMHWPS1xxieNdA86ZDDwOGOGE1MGq2Oje7WFnTho=; b=Hd4MXyVIFZbpBoDbLCxSmRPMlx7iBSGOYTUCpQCWwnxerpCCCZXuu9J1qmWg1iYH8Q z46G2QVjb37ZuZKCN7SyaFTOIy6kBaEEhzdqEcrRVdSVLC0pnNCgMi8FB7tVgQrWf54v O1hVcjjR/Ne0W3fXNDUq3Zucpd3JlXPwgnCLUpqpKrlt9LDaDWsmPllUbRenBQBjmn7V b6hemrv2BTPoOz2JLCg/iB5VKVmWthvOtS8diaENVj0z+oClvgwgdFzvrUQLp7cnHbX/ 4D1YOYI0P//+FrNZE/bTdo1nbwZl3R+gCFm5uUWX9Xjri8zx9K47AOUsmf/CXfMNv3X2 QU8A== X-Gm-Message-State: APjAAAXQfF38UzEigUeT7TFPuK4xZTSGClDzhQYgO3lQGbXnNZBh1nwO Pw+X/5hzoabB8Pt3TJMQC7U= X-Google-Smtp-Source: APXvYqx5jxNqSC1AwhzL2ZMet9bmLoYjYieBZmkm/Q1FWdEIqcmhDf/5bB+wK1W/mqhfEfilD3pImg== X-Received: by 2002:ac8:6bc9:: with SMTP id b9mr61126683qtt.108.1578004224542; Thu, 02 Jan 2020 14:30:24 -0800 (PST) Received: from [10.8.0.6] (pool-173-79-105-206.washdc.fios.verizon.net. [173.79.105.206]) by smtp.gmail.com with ESMTPSA id v4sm17350527qtd.24.2020.01.02.14.30.23 (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Thu, 02 Jan 2020 14:30:24 -0800 (PST) From: Zhihong Zhang Message-Id: <690FAE53-5851-4B64-9B3B-1E37A242F440@gmail.com> Content-Type: multipart/alternative; boundary="Apple-Mail=_9798FD3B-7998-4A48-9AF8-F3EE3F79A8AC" Mime-Version: 1.0 (Mac OS X Mail 13.0 \(3608.40.2.2.4\)) Subject: Re: Indexing on JSONB field not working Date: Thu, 2 Jan 2020 17:30:23 -0500 In-Reply-To: <18649.1578002294@sss.pgh.pa.us> Cc: Jeff Janes , Tomas Vondra , pgsql-bugs To: Tom Lane References: <0cf001d5b77f$bfe47e10$3fad7a30$@gmail.com> <20191220223005.f6w4ff4kdbgtanpz@development> <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> <39B1D245-31C9-407E-8136-514890B92CBA@gmail.com> <18649.1578002294@sss.pgh.pa.us> X-Mailer: Apple Mail (2.3608.40.2.2.4) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --Apple-Mail=_9798FD3B-7998-4A48-9AF8-F3EE3F79A8AC Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 > On Jan 2, 2020, at 4:58 PM, Tom Lane wrote: >=20 > 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. =E2=80=9CCreate index=E2=80=9D or auto = vacuum should take care of this, right? 4. Run =E2=80=98analyze=E2=80=99. 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=3D> select * from pg_stats where = tablename=3D'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=3D> 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=3D> select * from pg_stats where = tablename=3D'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=3D> 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=3D> select * from pg_stats where = tablename=3D'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,7643= 9.0416443348,86354.6561449766,96093.4087634087,106792.015489191,117061.504= 628509,125703.127589077,135128.147900105,145247.848238796, = 155961.862299591,166463.26566115,176670.81207037,187789.324205369,197144.4= 83681768,207021.271344274,216668.588574976,227840.67876637,237936.51582673= 2,247922.076378018,257857.841439545,267939.695157111,27702 = 5.00205487,287662.557791919,297872.847877443,309003.66185233,319923.490285= 873,330532.386898994,339468.085207045,349151.492118835,358672.737609595,36= 9132.092688233,378833.52348581,388865.450397134,399013.585 = 876673,407844.387926161,418233.385775238,427318.078000098,438366.677146405= ,448453.094344586,458905.486389995,468482.088763267,478278.840426356,48711= 9.99412626,495660.125277936,505299.935583025,515099.597163 = 498,525450.137443841,536038.665566593,546619.640663266,556582.688819617,56= 6576.57166943,576369.696762413,587215.536739677,597477.190662175,606301.20= 7095385,616701.394319534,626550.197601318,635750.317480415 = ,646002.440713346,655989.156104624,667213.554959744,677401.37828514,687671= .223655343,698001.290205866,707360.081840307,716366.450302303,726345.33606= 4696,735977.729782462,745276.737492532,755845.261737704,76 = 5410.838183016,774972.880259156,785988.09055984,795699.819922447,805251.39= 6726817,815074.041485786,824808.841571212,835754.215717316,846041.25749319= 8,856068.658642471,866092.296782881,876734.56966877,885931 = .862983853,896236.94261536,907013.318967074,916161.817498505,927834.809292= 108,938955.033197999,948850.627522916,958572.782110423,968669.227790087,97= 8541.388176382,989408.961031586,999981.255270541} | -0.01 26945 | | | (1 row) --Apple-Mail=_9798FD3B-7998-4A48-9AF8-F3EE3F79A8AC Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8

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. =E2=80=9CCreate index=E2=80=9D or auto = vacuum should take care of this, right?
4. Run = =E2=80=98analyze=E2=80=99.
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=3D> =
select * from pg_stats where tablename=3D'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=3D> 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=3D> select * = from pg_stats where tablename=3D'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=3D> 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=3D> select * from pg_stats = where tablename=3D'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.2866= 433561,76439.0416443348,86354.6561449766,96093.4087634087,106792.015489191= ,117061.504628509,125703.127589077,135128.147900105,145247.848238796,
155961.862299591,166463.26566115,176670.81207037,187789.3242053= 69,197144.483681768,207021.271344274,216668.588574976,227840.67876637,2379= 36.515826732,247922.076378018,257857.841439545,267939.695157111,27702
5.00205487,287662.557791919,297872.847877443,309003.66185233,31= 9923.490285873,330532.386898994,339468.085207045,349151.492118835,358672.7= 37609595,369132.092688233,378833.52348581,388865.450397134,399013.585
876673,407844.387926161,418233.385775238,427318.078000098,43836= 6.677146405,448453.094344586,458905.486389995,468482.088763267,478278.8404= 26356,487119.99412626,495660.125277936,505299.935583025,515099.597163
498,525450.137443841,536038.665566593,546619.640663266,556582.6= 88819617,566576.57166943,576369.696762413,587215.536739677,597477.19066217= 5,606301.207095385,616701.394319534,626550.197601318,635750.317480415
,646002.440713346,655989.156104624,667213.554959744,677401.3782= 8514,687671.223655343,698001.290205866,707360.081840307,716366.450302303,7= 26345.336064696,735977.729782462,745276.737492532,755845.261737704,76
5410.838183016,774972.880259156,785988.09055984,795699.81992244= 7,805251.396726817,815074.041485786,824808.841571212,835754.215717316,8460= 41.257493198,856068.658642471,866092.296782881,876734.56966877,885931
.862983853,896236.94261536,907013.318967074,916161.817498505,92= 7834.809292108,938955.033197999,948850.627522916,958572.782110423,968669.2= 27790087,978541.388176382,989408.961031586,999981.255270541} | -0.01
26945 | | = |
(1 = row)



= --Apple-Mail=_9798FD3B-7998-4A48-9AF8-F3EE3F79A8AC--