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 1ilNZP-0007Gm-7U for pgsql-bugs@arkaria.postgresql.org; Sun, 29 Dec 2019 01:40:39 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1ilNZM-0004XY-2t for pgsql-bugs@arkaria.postgresql.org; Sun, 29 Dec 2019 01:40:36 +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 1ilNZL-0004XR-Ha for pgsql-bugs@lists.postgresql.org; Sun, 29 Dec 2019 01:40:35 +0000 Received: from mail-qv1-xf42.google.com ([2607:f8b0:4864:20::f42]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1ilNZE-00026W-CK for pgsql-bugs@lists.postgresql.org; Sun, 29 Dec 2019 01:40:33 +0000 Received: by mail-qv1-xf42.google.com with SMTP id y8so11300134qvk.6 for ; Sat, 28 Dec 2019 17:40:28 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=content-transfer-encoding:from:mime-version:subject:date:message-id :references:cc:in-reply-to:to; bh=i7rIwV1zzkonJgY9NuYGJEBINd2lmQQ1JoiKaKx61wo=; b=E+f7KGAIJvCYh3KnvMrlfSllgqEP4yXHt+3aIJOhzzKyY+jgJQ0Jxw2zgisKtUVR0X DPpw5n0AOm/u3YGgHQgrPcGNQNG5q8OgUf5AxaDLJ1l8FLUGivuqgUuzJxgIg4Hykwz1 0j7BvbsuwM+fz22wR67OorKhbni871JGwmUt1speEL087JhRpTRs3UDoTaNLoFgbBwR4 G6IMYOLGy4wB3XTI4MIgpZePoPOknjJamSsH6OTP0qJEt9D01owB0Ewc+YoCW2xCEs6a Hg+b218KECA8VKUIvRRVH4VnACg1viQKIBYo5AehAzKsw3DVfpfgsjr19bNH9zdTA79r 5ejg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:content-transfer-encoding:from:mime-version :subject:date:message-id:references:cc:in-reply-to:to; bh=i7rIwV1zzkonJgY9NuYGJEBINd2lmQQ1JoiKaKx61wo=; b=XELIpKOrVi2bszblcn7lK8IYiwVTbaz6xPdAefNRcj9rO/XDnu3RuXbxmNpzmyEpmH ETbyWWcGbhV6HuNiO3LcT//96Xn1Ur4DeaBvE4NCvwNtaEvIxra4HX13UzpAdYfV4H/m cD8z86YYB2hVS4vS9MgdajJyIPvooqR5f5+qfgPAfwMku0lLLmfIUqwk3uwE/lEqk+qk LA46NwN9D9ROPEpOwGcleIGsZ+9WaCLmecAchZHjmj+s3NbZTekZ8VSOM5WOkVNd+Uqu 1dtdNTmDhzT398g+RBbeU+HGc22xOIxTRtPMm5X7aEI2FJLRVI/K6vEPVqewVRyyQJgS Ji2Q== X-Gm-Message-State: APjAAAVkpi2KJ+9gB8sTAzj+OBVq5oQWPuOesg/KX2lnWSNplw7+ZOoo qFfFOA0sWJzufIWNNmHLT5E= X-Google-Smtp-Source: APXvYqyOV6l7nHKtHGNNgbM3GWa3EDfFrwmN8AqK2ybotNozAxRcfPZuEc77zcrlkLeERCih4vJDDw== X-Received: by 2002:ad4:40cb:: with SMTP id x11mr45791988qvp.167.1577583627127; Sat, 28 Dec 2019 17:40:27 -0800 (PST) Received: from ?IPv6:2600:380:187b:6abb:a860:5f33:d8d9:a392? ([2600:380:187b:6abb:a860:5f33:d8d9:a392]) by smtp.gmail.com with ESMTPSA id d23sm12488363qte.32.2019.12.28.17.40.26 (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Sat, 28 Dec 2019 17:40:26 -0800 (PST) Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable From: Zhihong Zhang Mime-Version: 1.0 (1.0) Subject: Re: Indexing on JSONB field not working Date: Sat, 28 Dec 2019 20:40:24 -0500 Message-Id: <23FF633F-FFF8-4ABE-BCE6-4FF1E1772A26@gmail.com> References: <20191228235604.g75ymd7dvc5nwxgt@development> Cc: Jeff Janes , Tom Lane , pgsql-bugs In-Reply-To: <20191228235604.g75ymd7dvc5nwxgt@development> To: Tomas Vondra X-Mailer: iPhone Mail (17C54) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk The stats was sent in a previous email. Let me know if you can=E2=80=99t fin= d it. Zhihong > On Dec 28, 2019, at 6:56 PM, Tomas Vondra w= rote: >=20 > =EF=BB=BFOn Sat, Dec 28, 2019 at 02:59:07PM -0500, Zhihong Zhang wrote: >>>=20 >>> Only one of your indexes is over an expression. The index over a >>> plain column will never have stats, as column stats are stored with >>> the table, not the index. >>>=20 >>=20 >> Oh. That explains it. I tweaked a few server parameters, wonder if that >> made the difference. I changed, >>=20 >> pg_stat_statements.track: ALL =20 >>=20 >> track_activity_query_size: 2048 >>=20 >> Now we have stats, can anyone see why the index is not used? >>=20 >=20 > I don't follow. Neither of these configuration options should affect > which indexes have stats. The expression index should have stats no > matter how these two GUCs are set. >=20 > But once again, you're making claims without actually showing us the > data. How do you know we have stats now? Show us pg_stats data and > the explain plans. >=20 >=20 > regards >=20 > --=20 > Tomas Vondra http://www.2ndQuadrant.com > PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services=20