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 1in8cw-0006MJ-Fr for pgsql-bugs@arkaria.postgresql.org; Thu, 02 Jan 2020 22:07:34 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1in8cu-0006gB-RT for pgsql-bugs@arkaria.postgresql.org; Thu, 02 Jan 2020 22:07:32 +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 1in8cu-0006g4-Dr for pgsql-bugs@lists.postgresql.org; Thu, 02 Jan 2020 22:07:32 +0000 Received: from mail-qk1-x744.google.com ([2607:f8b0:4864:20::744]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1in8cs-0006Ti-7G for pgsql-bugs@lists.postgresql.org; Thu, 02 Jan 2020 22:07:31 +0000 Received: by mail-qk1-x744.google.com with SMTP id j9so32849959qkk.1 for ; Thu, 02 Jan 2020 14:07:30 -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=YKlZzPXVnFuHGH/9M0R1eCnyB/KkzQyEVyzc0+njfJw=; b=ePW7AbkKEMakKCTBBwDPOOd6lmmkR3V676G9KRmrn0Jdf6CF7q4T9ikNc7C02Z4TDi x9d/AUUWbVlGnnvfquisrCfe3ySUORdclw9jbyzl4AdSFolaxBnv4NknQQcxIMtApXHt Aj5cS1bLTW7UWrZUyQksnzZAEe8zYLIwwML1kFrsq8lZuikngRhdD6JcNYKYYi2l2BA8 vjT0g2O0QUavBZSylL0VnkW3Qox248iJSk7/sMy0eEdvWD7al8X+Fz2I+tC2APso2aid AJkVNIDPTxqU4bLPJ9T8X7INzTEc8l/PRsDSIRzFZAtT7FQ3gZ6yV1dE57/eLmRteA5I yKdQ== 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=YKlZzPXVnFuHGH/9M0R1eCnyB/KkzQyEVyzc0+njfJw=; b=EDxGe5s79lyagn2ovhvawPhnQOS7GaOIwUv6i1/hCzgNr+bYTWpjEL9kfsd6Xvcxi/ qCYn2h9+Vcp28IAJNzsu2fxvTGp6/Y2YAePXkaIvFj/Ixv5r+t3wqgM6/ha85/DuXITj Wq+2cQpE8r+Sp6T1ISVCQVmOLL9J1tY6mbmPuJM4mrpBgbo+8uMay18RYHtEGA+9JLAY oMJnwbrmrDo9DXUBQASrc57xXhkmYZNdGIwh72kOJw/8r5hXa51ky3WxMKo27LyKwH8W R8eXI3sWsud3Mk3oCwv2pVx3bsMARZsZ4gugPrkb3dmV9QzwOmP4CawsHTEimAKx9dWp eN+A== X-Gm-Message-State: APjAAAVmjyV6DUzNLxbyptkTn5qUP9pA8YWkHL919Izdw3+9Fu9XQB04 OCGt357a/bRjvdsA7N0BVWQ= X-Google-Smtp-Source: APXvYqzDjmRjlTc2QrXGXi5ys5YzrVRtaQ4ihjjA5TrMLjIn7p5rUiYrWGKCCGQBgsxueHm400EZEw== X-Received: by 2002:a37:ae07:: with SMTP id x7mr67946811qke.28.1578002849340; Thu, 02 Jan 2020 14:07:29 -0800 (PST) Received: from [10.8.0.6] (pool-173-79-105-206.washdc.fios.verizon.net. [173.79.105.206]) by smtp.gmail.com with ESMTPSA id l19sm17272862qtq.48.2020.01.02.14.07.28 (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Thu, 02 Jan 2020 14:07:28 -0800 (PST) Content-Type: text/plain; charset=utf-8 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: <18649.1578002294@sss.pgh.pa.us> Date: Thu, 2 Jan 2020 17:07:28 -0500 Cc: Jeff Janes , Tomas Vondra , pgsql-bugs Content-Transfer-Encoding: quoted-printable Message-Id: 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> <18649.1578002294@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 > On Jan 2, 2020, at 4:58 PM, Tom Lane wrote: >=20 > Zhihong Zhang writes: >> So I like to report 2 bugs, >=20 >> 1. =E2=80=9CCREATE INDEX=E2=80=9D with expression should generate = stats automatically.=20 >=20 > It does. I created an index with JSONB field yesterday. It still didnn=E2=80=99t = have stats today till I ran =E2=80=98analyze=E2=80=99.=20 >=20 >> 2. Auto-analyze should generate stats if it is not available, = regardless of the number of records changed. >=20 > This doesn't square with anyone else's reality either. We have auto-analyze running every night and it didn=E2=80=99t create = the stats either. I think it=E2=80=99s probably related to the logic = that it skips analyzing if not enough records changed. There was no = database changes at all in our case. I can reproduce those problems easily. Just tell me what information you = need to help debugging and I will get it for you. Thanks! Zhihong