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 1inOsx-0005hG-Hf for pgsql-bugs@arkaria.postgresql.org; Fri, 03 Jan 2020 15:29:11 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1inOsw-0002aU-9Q for pgsql-bugs@arkaria.postgresql.org; Fri, 03 Jan 2020 15:29:10 +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 1inOsv-0002aM-UW for pgsql-bugs@lists.postgresql.org; Fri, 03 Jan 2020 15:29:10 +0000 Received: from mail-qt1-x82f.google.com ([2607:f8b0:4864:20::82f]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1inOst-0002z9-KX for pgsql-bugs@lists.postgresql.org; Fri, 03 Jan 2020 15:29:09 +0000 Received: by mail-qt1-x82f.google.com with SMTP id j5so37067851qtq.9 for ; Fri, 03 Jan 2020 07:29:07 -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=6mfZSvgaoV4+8wGYKIeP9+UuJExIrwLpSnQvEPXrQQ8=; b=bBfYTqKGMHYs/uRnIsdboHP+Sjmytq+u7MNLnjEgDWBwOMs1/kTfV5oQL2KqUS/ZEG ERQOLg+qp9lfrgrHbsIIFq8W7LtmQiy/McVFj8dOaxDQJU63iJOO/f1LcDnNeQWNeHmU Op1HEuHq3ThKSJmjRVRpB43nEAHSpxx4ivZl4Po+v+F2JBsLRDCg/KOWbhM56E3BuwcD KXmvvtgIhbjqMV4YzETq/97NlWD47bLPMpUCrzCky+xw62GeIsdxmIHSz1dHADvy5+Zy /U4BS4KtwsYx3ifzvZAhofEbQeyXYtZ1Ft7vJtba54u5TGmjKtETbGkZYkYTSiCgG2wM wySQ== 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=6mfZSvgaoV4+8wGYKIeP9+UuJExIrwLpSnQvEPXrQQ8=; b=Hdv0bPJ/c4jdkwaf4g/q4fZ8JtiHOM4ghgEFSTNKvITnbVhDjq6wHCQqQwW1cMUP3k 3P0KKhqhpuPVSUrgAjgfW40oyEKcSJ6EYSO4gWb4xDgvhFbuM6zJR6IsCupq92aa68qo zHkUacT36kZrjxQrycvzfMoQwonowIPcq8ehe28H59jXErxIg3dz1VBuU/WwLFXwNjPk HOlp7m8rLsMozsZnZc0+67GHM/5pe5X41557bVu38GL9kUSYzqcHh7L9iXgsHIzGuQ3U F3rO5NJAzmlKu8dPvEfpic0l6FVGJ5PNJLORsmCM7uI16/QNxBfVQS4ytopOCjdpGOFy 4uXA== X-Gm-Message-State: APjAAAXdnk8MeEMoloqIgnAWpbg0BwFSg6BD0TkLMo66+FFrzLBZYwS3 byDVwfjvNMJYCG9lHSSHQoI= X-Google-Smtp-Source: APXvYqzpTIRlfduq1f6hYnnsHZ2RK4Dmuf+FzQ2vJYi+xBR1SS6Ygg/9+1QdVDs5ab922S6oB0Pv8w== X-Received: by 2002:aed:33c5:: with SMTP id v63mr59671897qtd.298.1578065346170; Fri, 03 Jan 2020 07:29:06 -0800 (PST) Received: from [192.168.2.2] (pool-173-79-105-206.washdc.fios.verizon.net. [173.79.105.206]) by smtp.gmail.com with ESMTPSA id o81sm16553345qke.33.2020.01.03.07.29.05 (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Fri, 03 Jan 2020 07:29:05 -0800 (PST) From: Zhihong Zhang Message-Id: Content-Type: multipart/alternative; boundary="Apple-Mail=_CA15E356-887F-4200-BE84-15FE0B51EC84" 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, 3 Jan 2020 10:29:04 -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> <39B1D245-31C9-407E-8136-514890B92CBA@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=_CA15E356-887F-4200-BE84-15FE0B51EC84 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 > On Jan 3, 2020, at 8:54 AM, Jeff Janes wrote: >=20 > You told us vociferously that ANALYZE was run after the index was = created. >=20 I did have that problem but can=E2=80=99t reproduce it right now. I am = just trying to concentrate on the issues I can reproduce easily. We have been dealing with the slow query issue for months. The first = advice is always to run vacuum/analyze. That didn=E2=80=99t help. I = think things changed when I accidentally restarted the server by = applying some configuration changes. Now, bouncing the server always = brings the stats back, without running =E2=80=98analyze=E2=80=99. We had the same issue on another server. I made a snapshot of the = database before I restarted the server. Anything I can capture to help = nail the issue? Thanks! Zhihong --Apple-Mail=_CA15E356-887F-4200-BE84-15FE0B51EC84 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8

On Jan 3, 2020, at 8:54 AM, Jeff Janes <jeff.janes@gmail.com> wrote:

You = told us vociferously that ANALYZE was run after the index was = created.


I did have that problem but can=E2=80=99t = reproduce it right now.  I am just trying to concentrate on the = issues I can reproduce easily.

We have been dealing with the slow = query issue for months. The first advice is always to run = vacuum/analyze. That didn=E2=80=99t help. I think things changed when I = accidentally restarted the server by applying some configuration = changes. Now, bouncing the server always brings the stats back, without = running =E2=80=98analyze=E2=80=99.

We had the same issue on another = server. I made a snapshot of the database before I restarted the server. = Anything I can capture to help nail the issue?

Thanks!

Zhihong



= --Apple-Mail=_CA15E356-887F-4200-BE84-15FE0B51EC84--