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 1ilLwJ-0002SO-KK for pgsql-bugs@arkaria.postgresql.org; Sat, 28 Dec 2019 23:56: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 1ilLwI-0002iX-4D for pgsql-bugs@arkaria.postgresql.org; Sat, 28 Dec 2019 23:56: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 1ilLwH-0002iQ-S9 for pgsql-bugs@lists.postgresql.org; Sat, 28 Dec 2019 23:56:09 +0000 Received: from mail-wm1-x342.google.com ([2a00:1450:4864:20::342]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1ilLwE-000736-UB for pgsql-bugs@lists.postgresql.org; Sat, 28 Dec 2019 23:56:09 +0000 Received: by mail-wm1-x342.google.com with SMTP id a5so11252221wmb.0 for ; Sat, 28 Dec 2019 15:56:06 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=2ndquadrant-com.20150623.gappssmtp.com; s=20150623; h=date:from:to:cc:subject:message-id:references:mime-version :content-disposition:in-reply-to; bh=Q26N56Y8DcJL3Ib/DVsRD4UQGHMbzspCfM+TKIDdakM=; b=iJtpWmtS6PkYfNawoX2dbVv2E7PA2ubdNRtND0HWLZRqoAvZqMh491PWwfG7RSngds o3UidcXzhg6Ta4X6KVSnoWZtE+VnV/SlQWKz+SmB/3XCCIobvvWLiUqT35AXz9OQZ8wn aDdnzDsaiQu90G6WaWrmklcdvJQu2RsMs2baJOamF2xiykzeYuVi1A6YSAoCSPRGEjYl 6gwsHKuOfZdtVayKrbABQnN1LLEpHNDxzmO7bNkC09yPC9+sutiqgtjk9o24xTQXV2ld mgPVl4CTV7c53wuR/nsAcydLnGOHzor+V4pQf8WQu8rxmNbz/kr9byBzMxc5DYzHxZsJ 9XoQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:date:from:to:cc:subject:message-id:references :mime-version:content-disposition:in-reply-to; bh=Q26N56Y8DcJL3Ib/DVsRD4UQGHMbzspCfM+TKIDdakM=; b=FEwucj7fRE3Io9qj+aROKe9lyW0dwj6V8Z9h8iVstjwRiru/xam7arp8ntoJSMn+kU S/7BH/n9TUagEOqV39MKE46GdZPeLTxyDK4Ssgp73ewhr5gt+++qmREAHCqPZAW9mPhe csAdS+Zl+F+phXxwApl9Xg8lrBf+Mlz7+ddB87NreWhvV9xw/6CIxwYcuyrgGOjzc7i1 u5b3knAK1f1041tgbyjZ7fPPl3R8Cevw/pYxwQzxGLfhbGWw52rZyxXOWXBPLfvetg0V Tcw+VTMIlb49M6lJsxWxn1608SRi8lgTKKc3GzHYYHjBhRSHfeFAdSVetx5navJK/Tn8 59Pg== X-Gm-Message-State: APjAAAWA9avzJXQAi9UeR9l9cydDsHB7x7csAfGxR+TjDh1Cg9ADIY+p Z6vXeBx7f/heFQsBsPqLHRrneA== X-Google-Smtp-Source: APXvYqzEds/9QC0UBMZCr48PXn6a8FSDazTMqhZst1GLg8sZp05xfvnlJ0Ln3uesD6Nz+HVjc72xxQ== X-Received: by 2002:a1c:3803:: with SMTP id f3mr26836402wma.134.1577577365782; Sat, 28 Dec 2019 15:56:05 -0800 (PST) Received: from localhost (ip-86-49-253-92.net.upcbroadband.cz. [86.49.253.92]) by smtp.gmail.com with ESMTPSA id s10sm40037242wrw.12.2019.12.28.15.56.04 (version=TLS1_3 cipher=TLS_AES_256_GCM_SHA384 bits=256/256); Sat, 28 Dec 2019 15:56:05 -0800 (PST) Date: Sun, 29 Dec 2019 00:56:04 +0100 From: Tomas Vondra To: Zhihong Zhang Cc: Jeff Janes , Tom Lane , pgsql-bugs Subject: Re: Indexing on JSONB field not working Message-ID: <20191228235604.g75ymd7dvc5nwxgt@development> References: <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> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii; format=flowed Content-Disposition: inline In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk On Sat, Dec 28, 2019 at 02:59:07PM -0500, Zhihong Zhang wrote: >> >> 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? > 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. 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. regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services