Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pHQxe-00043f-LH for pgsql-general@arkaria.postgresql.org; Mon, 16 Jan 2023 14:59:46 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pHQxd-0000Ay-Cw for pgsql-general@arkaria.postgresql.org; Mon, 16 Jan 2023 14:59:45 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pHQxd-0000Aj-37 for pgsql-general@lists.postgresql.org; Mon, 16 Jan 2023 14:59:45 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pHQxa-0000I1-RE for pgsql-general@lists.postgresql.org; Mon, 16 Jan 2023 14:59:44 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.15.2/8.15.2) with ESMTP id 30GExcai1860908; Mon, 16 Jan 2023 09:59:39 -0500 From: Tom Lane To: Harmen cc: pgsql-general@lists.postgresql.org Subject: Re: row estimate for partial index In-reply-to: <20230116085523.GK91568@arp.lijzij.de> References: <20230113150159.GJ91568@arp.lijzij.de> <1008139.1673713387@sss.pgh.pa.us> <20230116085523.GK91568@arp.lijzij.de> Comments: In-reply-to Harmen message dated "Mon, 16 Jan 2023 09:55:23 +0100" MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-ID: <1860906.1673881178.1@sss.pgh.pa.us> Date: Mon, 16 Jan 2023 09:59:38 -0500 Message-ID: <1860907.1673881178@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk Harmen writes: > On Sat, Jan 14, 2023 at 11:23:07AM -0500, Tom Lane wrote: >> If you are running a reasonably recent PG version you should be able to >> fix that by setting up "extended statistics" on that pair of columns: > CREATE STATISTICS dist4 (ndistinct) ON deleted, org_id FROM contactsbool; > CREATE STATISTICS dist4b (ndistinct) ON org_id, deleted FROM contactsbool; 1. ndistinct is not the correct stats type for this problem. (I think dependencies is, but generally speaking, it's not worth trying to be smarter than the system about which ones you need. Just create 'em all.) 2. Per the CREATE STATISTICS man page, the order of the columns is not significant, so you're just doubling the amount of work for ANALYZE without gaining anything. I think you will find that CREATE STATISTICS stats1 ON deleted, org_id FROM contactsbool; is enough to fix this. It improved the estimate for me in v14 and HEAD, anyway. regards, tom lane