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 1ikbZX-0006du-Mx for pgsql-bugs@arkaria.postgresql.org; Thu, 26 Dec 2019 22:25: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 1ikbZV-00058B-E6 for pgsql-bugs@arkaria.postgresql.org; Thu, 26 Dec 2019 22:25:33 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1ikbZV-00054f-3m for pgsql-bugs@lists.postgresql.org; Thu, 26 Dec 2019 22:25:33 +0000 Received: from mail-qt1-x830.google.com ([2607:f8b0:4864:20::830]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1ikbZN-0001JL-RJ for pgsql-bugs@lists.postgresql.org; Thu, 26 Dec 2019 22:25:32 +0000 Received: by mail-qt1-x830.google.com with SMTP id e12so23298456qto.2 for ; Thu, 26 Dec 2019 14:25:25 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=mime-version:subject:from:in-reply-to:date:cc :content-transfer-encoding:message-id:references:to; bh=P1KvgGPiwaryl0iCDCyqhHope+zxuzTfYb8TOsyfOaI=; b=i7h/wdHYJ5186QDQ6DdJiTfIBFlZXNa2TDBVmK5hMy0AtPzvTkLsEX8UjwloveQlBF udq6Cix2AfDvOjL716D8kFoEw9C1xterpCsl0cNc9kYPqa5DiPB7GR2sr5SHYWAlJqXD wo/5OVDgPAT3HP8nCZT0W0DnVzgLqfdkoHFtDwnvFVqpv3ySnZxXyj62jBmeiL7tkK+E HrasyOn89QtQsZIK6Gcw91By5v7e62LlGamJ+Ixqu1meHXrVcXqJRYMLK+0cD9nxmGWP iWHMg09vgvtpnrZbOOzMWbRa5F0fmDES5ZZk0XbMgZU3VxeCLSfZqR87vDp7XccAx1fp El4Q== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:mime-version:subject:from:in-reply-to:date:cc :content-transfer-encoding:message-id:references:to; bh=P1KvgGPiwaryl0iCDCyqhHope+zxuzTfYb8TOsyfOaI=; b=blp3ABzRWlj/YeN+094nZiJKx6pTc5AVfg5BJYt7EFPvUJEO1eUaOSh9miWedlsa6C hZWZaaTVvBFPneAa1gJUSWItW2bAPIB/sqho9LvTLMdbpruL+6TkBbzpFql2k/hqeN55 kOIsvDHHmtX7H0TKWMVHutOZEJ9Grjid6WqHS4TXZpe8URNw3Z7xjMDVZLuY9+TmVrOc oSEqiKZaDD7wBsJEhiQ9WvjMVZta4FCiEn+9kAICO1mCwKSbe3ztlZnLSpRPK2UOVEI2 Sls41nA7pPt6py8M1WbNTrlDIXpl28gHkX9+pXDC2jb0kNtlMtzmLwEcqhPo1d0TWbrh 5bYg== X-Gm-Message-State: APjAAAUrioYUzvvXCGclX4CJ0pV+fWR0e5Ik/S5akvXrqZjtoTlSyExD mdYZE/F9Yf13GK8YYNriC0Q= X-Google-Smtp-Source: APXvYqxbhUJwIFFzL3JCRla5KN0exss7qhpf/Vi+Byoj2GJPBMZ2JEcYuaZVDO+OllDGYLsbgUNGrg== X-Received: by 2002:ac8:1349:: with SMTP id f9mr36351187qtj.179.1577399123878; Thu, 26 Dec 2019 14:25:23 -0800 (PST) Received: from [192.168.2.2] (pool-173-79-120-3.washdc.fios.verizon.net. [173.79.120.3]) by smtp.gmail.com with ESMTPSA id o33sm9973930qta.27.2019.12.26.14.25.23 (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Thu, 26 Dec 2019 14:25:23 -0800 (PST) Content-Type: text/plain; charset=us-ascii Mime-Version: 1.0 (Mac OS X Mail 13.0 \(3608.40.2.2.4\)) Subject: Re: Indexing on JSONB field not working From: Zhihong Zhang In-Reply-To: <12042.1577393358@sss.pgh.pa.us> Date: Thu, 26 Dec 2019 17:25:22 -0500 Cc: Jeff Janes , Tomas Vondra , pgsql-bugs Content-Transfer-Encoding: quoted-printable Message-Id: References: <0cf001d5b77f$bfe47e10$3fad7a30$@gmail.com> <20191220223005.f6w4ff4kdbgtanpz@development> <83E6A3F7-B1B4-4AD6-905A-BE11D244D35A@gmail.com> <4910.1577386715@sss.pgh.pa.us> <0A83C661-EDBB-43C8-9CBB-FB1E321A15CB@gmail.com> <12042.1577393358@sss.pgh.pa.us> To: Tom Lane X-Mailer: Apple Mail (2.3608.40.2.2.4) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk I am not superuser. Let me see how I can get access to the superuser on = RDS. I will get back to you. Thanks! Zhihong > On Dec 26, 2019, at 3:49 PM, Tom Lane wrote: >=20 > Zhihong Zhang writes: >> I looked at pg_stats for all our databases. None of them have stats = on indexes. Are there any settings disabling this? >=20 > No, I don't think so. We only collect stats on index expressions, = though, > not simple columns (since those would be duplicative of the underlying > column's stats). >=20 > Hmmm ... looking at the pg_stats view, it has a filter >=20 > WHERE NOT a.attisdropped AND > has_column_privilege(c.oid, a.attnum, 'select'::text) AND > (c.relrowsecurity =3D false OR NOT row_security_active(c.oid)) >=20 > The has_column_privilege test might be getting in the way if you're > not superuser; it will probably think you have no access privileges > for the index. I now recall somebody complaining about that before = [1], > but no fix has been accepted as yet. >=20 > Having said that, though, that only accounts for you not seeing the > entries in the pg_stats view; it doesn't explain why the optimizer > doesn't see them, assuming they're actually there in pg_statistic, > which they surely should be. >=20 > As I recall, RDS doesn't give out superuser access, so it may be > hard for you to learn more about what's happening :-( >=20 > regards, tom lane >=20 > [1] = https://www.postgresql.org/message-id/flat/6369212.CF36pTLAQO%40peanuts2