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 1ikwBm-0007qT-MP for pgsql-bugs@arkaria.postgresql.org; Fri, 27 Dec 2019 20:26:26 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1ikwBl-0000Fe-Dk for pgsql-bugs@arkaria.postgresql.org; Fri, 27 Dec 2019 20:26:25 +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 1ikwBk-0000FX-S5 for pgsql-bugs@lists.postgresql.org; Fri, 27 Dec 2019 20:26:25 +0000 Received: from mail-qk1-x72b.google.com ([2607:f8b0:4864:20::72b]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1ikwBi-0005pE-Hi for pgsql-bugs@lists.postgresql.org; Fri, 27 Dec 2019 20:26:23 +0000 Received: by mail-qk1-x72b.google.com with SMTP id a203so22655086qkc.3 for ; Fri, 27 Dec 2019 12:26:22 -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=/lq0C7h4AhYADAr8IG9aRkO3TkRQwMYmKlwmr7B334A=; b=O/KZe2m/00zQA7cejd3xrUfnxPek413ez+9nniA1+Kgzjp12GCM7QFj9w+VAR/RNbn xiSwSOPb38tBAUZ/H/E7LgrbAr09x0ogHuGGfWJheZme7lAHXY8G8ymgY/AT/WYlT8Td WuOf1R9UqKmrUeT9OPfVo9lfUv6r0sFT7mVk+P5wKMYA+RxjqhcZ0nV//M/JkIl5urz/ M28SEGgKOfsj1qctxHhmBqWWukVKoEM63dASUmjPPWMvNkZel9FYWifCq+ot48nnE9w+ MPLJ4fjFDxKVtTVBWyJVOow9BYWeyXA5x/OPIk3+ItxOg3+dfWeeecR6UC4GMA7fxyCb 0Sjw== 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=/lq0C7h4AhYADAr8IG9aRkO3TkRQwMYmKlwmr7B334A=; b=QfXzjq1KJvnKysQ1XPlmvr/tSKieGGG8vNKf0RHIsF26Sk7EADUKtlvnt2NwagzxDA O/IyEYYvryrvFGxW8/aG5jgPawwEq6znP+1Ampi4/37PyBxb3Oiq0c7Gco08BAPXku08 FMtgt7TUHsp/NuTS4aojt+nnzY4nNFxeJvhpK/+cR6zL7l0zzvD7lzA8uDoBRiuOQ122 Na272HvwCjpArOoOmhL5P1lbaulOma1KzCWWYoeT+tg8GhuZ93boI2OyT4mnugLi85nI S22/KgedSndv16MlpWln69TztUtPfk/EuBOBY3b1tMP5FTdMxWGA8PrD3hEZq+Ef/4uL sZMA== X-Gm-Message-State: APjAAAXRKzhUrhcOh0nVYoG49FFaTAUj5jV/VpfjoEt6QCRgWH5OoTI6 +cweIuehioFXo2+5DsBSiqc= X-Google-Smtp-Source: APXvYqwGkktEwMRsx4NsgN2EfAr2izszeYyaSZau2ADzHqGYX+RGL1M6vlkprTLQF2lx0W9QAkPvaQ== X-Received: by 2002:a37:5f43:: with SMTP id t64mr44462214qkb.68.1577478381512; Fri, 27 Dec 2019 12:26:21 -0800 (PST) Received: from [10.8.0.6] (pool-173-79-120-3.washdc.fios.verizon.net. [173.79.120.3]) by smtp.gmail.com with ESMTPSA id g9sm9936782qkm.9.2019.12.27.12.26.20 (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Fri, 27 Dec 2019 12:26:21 -0800 (PST) From: Zhihong Zhang Message-Id: <202A204F-FE4D-414A-ABD0-B7773A0C7316@gmail.com> Content-Type: multipart/alternative; boundary="Apple-Mail=_DBF45748-2DEA-4B48-ACB6-71953E33345A" Mime-Version: 1.0 (Mac OS X Mail 13.0 \(3608.40.2.2.4\)) Subject: Re: Indexing on JSONB field not working Date: Fri, 27 Dec 2019 15:26:17 -0500 In-Reply-To: <11610.1577468732@sss.pgh.pa.us> Cc: Tomas Vondra , pgsql-bugs@lists.postgresql.org 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> 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=_DBF45748-2DEA-4B48-ACB6-71953E33345A Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 Finally got stats on the new table assets_copy. Still don=E2=80=99t know = what=E2=80=99s the magic. I have 2 indexes in this new table, only this = one got stats. The stats looks the same as the column stats. Zhihong select * from pg_stats where tablename=3D'jsonb_float_value_idx'; -[ RECORD 1 = ]----------+--------------------------------------------------------------= --------------------------------------------------------------------------= --------------------------------------------------------------------------= --------------------------------------------------------------------------= --------------------------------------------------------------------------= --------------------------------------------------------------------------= --------------------------------------------------------------------------= --------------------------------------------------------------------------= --------------------------------------------------------------------------= --------------------------------------------------------------------------= --------------------------------------------------------------------------= --------------------------------------------------------------------------= --------------------------------------------------------------------------= --------------------------------------------------------------------------= --------------------------------------------------------------------------= --------------------------------------------------------------------------= --------------------------------------------------------------------------= --------------------------------------------------------------------------= --------------------------------------------------------------------------= --------------------------------------------------------------------------= --------------------------------------------------------------------------= --------------------------------------------------------------------------= --------------------------------------------------------------------------= ----------------- schemaname | public tablename | jsonb_float_value_idx attname | float8 inherited | f null_frac | 0 avg_width | 8 n_distinct | -1 most_common_vals | most_common_freqs | histogram_bounds | = {11.9074247777462,9841.89659357071,20004.3972581625,30086.5592435002,40730= .0088554621,50593.008287251,58767.0910172164,67316.9470392168,77683.910261= 8396,88379.8920549452,98935.4504272342,109736.490063369,120142.996311188,1= 29663.615487516,139033.944346011,149255.915079266,159277.160651982,168800.= 76052621,178707.448765635,190234.981942922,200536.492280662,209703.1339071= 69,220037.896186113,230601.808056235,239625.298418105,249577.831011266,259= 359.933435917,269170.144107193,279207.427985966,289696.279447526,299808.37= 4606073,309782.688505948,319680.196698755,329276.353120804,339375.47681853= 2,349651.550408453,359881.41130656,369298.731908202,380326.490849257,39057= 8.283462673,400972.062721848,411068.622488528,420153.666753322,430431.8428= 03955,439484.844449908,449541.503563523,459902.50306204,469946.910161525,4= 81110.869906843,491827.534046024,501586.132217199,511425.472330302,522126.= 020863652,532380.575779825,542049.761861563,552384.272217751,562474.489212= 036,573650.59107542,584696.998354048,594522.320665419,604293.577373028,614= 212.908316404,623957.396950573,634345.385245979,644869.87143755,654195.504= 263043,664599.217940122,674438.172951341,683769.1613473,693582.618143409,7= 02202.791813761,712556.770537049,721915.236674249,733364.793937653,743519.= 58045736,753806.136548519,763560.635503381,772320.672404021,781465.6351692= 97,790334.59816128,801681.783981621,812449.718359858,821786.699350923,8322= 79.333844781,841624.361462891,851525.46595782,861103.327944875,871528.4466= 74347,880865.939892828,889850.093051791,899233.678821474,909849.165473133,= 919194.979593158,928674.942348152,939167.145173997,949835.113249719,959980= .852901936,969834.96658504,979897.553101182,990309.921558946,999946.910422= 295} correlation | 0.00552317 most_common_elems | most_common_elem_freqs | elem_count_histogram | --Apple-Mail=_DBF45748-2DEA-4B48-ACB6-71953E33345A Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8 Finally got stats on = the new table assets_copy. Still don=E2=80=99t know what=E2=80=99s the = magic. I have 2 indexes in this new table, only this one got stats.

The stats looks the same = as the column stats.

Zhihong

select * from pg_stats =
where tablename=3D'jsonb_float_value_idx';
-[ RECORD 1 = ]----------+--------------------------------------------------------------= --------------------------------------------------------------------------= --------------------------------------------------------------------------= --------------------------------------------------------------------------= --------------------------------------------------------------------------= --------------------------------------------------------------------------= --------------------------------------------------------------------------= --------------------------------------------------------------------------= --------------------------------------------------------------------------= --------------------------------------------------------------------------= --------------------------------------------------------------------------= --------------------------------------------------------------------------= --------------------------------------------------------------------------= --------------------------------------------------------------------------= --------------------------------------------------------------------------= --------------------------------------------------------------------------= --------------------------------------------------------------------------= --------------------------------------------------------------------------= --------------------------------------------------------------------------= --------------------------------------------------------------------------= --------------------------------------------------------------------------= --------------------------------------------------------------------------= --------------------------------------------------------------------------= -----------------
schemaname = | public
tablename | jsonb_float_value_idx
attname | = float8
inherited = | f
null_frac = | 0
avg_width = | 8
n_distinct = | -1
most_common_vals = |
most_common_freqs = |
histogram_bounds | = {11.9074247777462,9841.89659357071,20004.3972581625,30086.5592435002,40730= .0088554621,50593.008287251,58767.0910172164,67316.9470392168,77683.910261= 8396,88379.8920549452,98935.4504272342,109736.490063369,120142.996311188,1= 29663.615487516,139033.944346011,149255.915079266,159277.160651982,168800.= 76052621,178707.448765635,190234.981942922,200536.492280662,209703.1339071= 69,220037.896186113,230601.808056235,239625.298418105,249577.831011266,259= 359.933435917,269170.144107193,279207.427985966,289696.279447526,299808.37= 4606073,309782.688505948,319680.196698755,329276.353120804,339375.47681853= 2,349651.550408453,359881.41130656,369298.731908202,380326.490849257,39057= 8.283462673,400972.062721848,411068.622488528,420153.666753322,430431.8428= 03955,439484.844449908,449541.503563523,459902.50306204,469946.910161525,4= 81110.869906843,491827.534046024,501586.132217199,511425.472330302,522126.= 020863652,532380.575779825,542049.761861563,552384.272217751,562474.489212= 036,573650.59107542,584696.998354048,594522.320665419,604293.577373028,614= 212.908316404,623957.396950573,634345.385245979,644869.87143755,654195.504= 263043,664599.217940122,674438.172951341,683769.1613473,693582.618143409,7= 02202.791813761,712556.770537049,721915.236674249,733364.793937653,743519.= 58045736,753806.136548519,763560.635503381,772320.672404021,781465.6351692= 97,790334.59816128,801681.783981621,812449.718359858,821786.699350923,8322= 79.333844781,841624.361462891,851525.46595782,861103.327944875,871528.4466= 74347,880865.939892828,889850.093051791,899233.678821474,909849.165473133,= 919194.979593158,928674.942348152,939167.145173997,949835.113249719,959980= .852901936,969834.96658504,979897.553101182,990309.921558946,999946.910422= 295}
correlation = | 0.00552317
most_common_elems |
most_common_elem_freqs |
elem_count_histogram |

= --Apple-Mail=_DBF45748-2DEA-4B48-ACB6-71953E33345A--