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 1ilIF1-00008J-Dh for pgsql-bugs@arkaria.postgresql.org; Sat, 28 Dec 2019 19:59:15 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1ilIEz-0008CZ-TK for pgsql-bugs@arkaria.postgresql.org; Sat, 28 Dec 2019 19:59:13 +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 1ilIEz-0008CS-Ey for pgsql-bugs@lists.postgresql.org; Sat, 28 Dec 2019 19:59:13 +0000 Received: from mail-qk1-x735.google.com ([2607:f8b0:4864:20::735]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1ilIEv-00080W-T9 for pgsql-bugs@lists.postgresql.org; Sat, 28 Dec 2019 19:59:11 +0000 Received: by mail-qk1-x735.google.com with SMTP id a203so24189713qkc.3 for ; Sat, 28 Dec 2019 11:59:09 -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=kQvXDVhf6cn6A3jdy87Xjh/hiOsYki46Lx9JpXiosy8=; b=Limhkg79TyGVO2ly6WzB8Nf6b3kFMvv05TN6H+drENUjV2wDrcsENWobZ0kly2+WQo GeIYry6UqRF0QMd1srTLXY+eQli1Iq4+cUSJslUoG/FW7oSlsBbldI5x9T2WloT/AIKP RFjZa1xdatOjkJ9gR5rk1lPPtmJlTWoPInGX5J2F1RILi2csXlYRKmjK3wqaJ/NIu0CE 2jjPtz9FigmURtRwANYpDNalDxhAn/2O8h/75Bivcm+CVPnRw7tSKbuL0jldcu/fp+sk sobNVYzeqT2zcqBeazSqz/KZr+YWFSspJ1FpR62sSNoNO/vrsSyR+rmpOEJ0QMVz+3ZQ ba9w== 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=kQvXDVhf6cn6A3jdy87Xjh/hiOsYki46Lx9JpXiosy8=; b=gMcR5jwpqjZbx0NErCGSOMv7C8awFjFHUj15DCR05di6WdNpnaqD+DyveiXzjPa2HO 68Gi6t7S5qwi2pFQ6rQ/nd07J+VDDeVixdrl37+idx9dC3Vynm62Qxhitehim3RObdZU vh4UxmM08jNu0gZm5Bx+eQmvKpmjw1t0oOWF23uZBKKyFklnCgUGyOK2ip6qFW0rqupF 46zeOz5KzAtPIi7BwYBXizA+04pRHm+jcBW7mcUR5Nc+Y1mFGO5/icFW1p1CUCIN/OXP G+l+aAw9XKJxTUvUZzQnypRQacbSsYANAqPvY1te9NoBmnP8oabRgBpaSFLyyq5W7X4V Zx0A== X-Gm-Message-State: APjAAAU8xkPNGBfVqgs6mN6jH3Py69FmnjN+cKPyiwmETOtDeVEA4WS9 0v5uVYtglOBcRtC3w7xS2O8= X-Google-Smtp-Source: APXvYqz23V1qrEoBSnkHlJRVpiodNbMB6gIcw7AamNuoy9ryBkcLURlk2i1Zz2wCXUC5xgCWAxwMMA== X-Received: by 2002:a05:620a:849:: with SMTP id u9mr49079623qku.414.1577563148930; Sat, 28 Dec 2019 11:59:08 -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 m21sm10924027qka.117.2019.12.28.11.59.08 (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Sat, 28 Dec 2019 11:59:08 -0800 (PST) From: Zhihong Zhang Message-Id: Content-Type: multipart/alternative; boundary="Apple-Mail=_75FFF7B4-DA2C-4BC6-9F90-526B459A5B4B" Mime-Version: 1.0 (Mac OS X Mail 13.0 \(3608.40.2.2.4\)) Subject: Re: Indexing on JSONB field not working Date: Sat, 28 Dec 2019 14:59:07 -0500 In-Reply-To: Cc: Tom Lane , Tomas Vondra , pgsql-bugs To: Jeff Janes 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> 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=_75FFF7B4-DA2C-4BC6-9F90-526B459A5B4B Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=us-ascii >=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 Oh. That explains it. I tweaked a few server parameters, wonder if that = made the difference. I changed, pg_stat_statements.track: ALL=09 track_activity_query_size: 2048 Now we have stats, can anyone see why the index is not used? Thanks! Zhihong --Apple-Mail=_75FFF7B4-DA2C-4BC6-9F90-526B459A5B4B Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=us-ascii

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.


Oh. That explains it. I tweaked a few server = parameters, wonder if that made the difference. I = changed,

pg_stat_statements.track: ALL

track_activity_query_size: 2048

Now we have stats, can anyone see why = the index is not used?

Thanks!

Zhihong

= --Apple-Mail=_75FFF7B4-DA2C-4BC6-9F90-526B459A5B4B--