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 1pIQqV-0007Aw-GW for pgsql-general@arkaria.postgresql.org; Thu, 19 Jan 2023 09:04:31 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pIQqU-0006BI-6t for pgsql-general@arkaria.postgresql.org; Thu, 19 Jan 2023 09:04:30 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pIQqT-0006B9-SK for pgsql-general@lists.postgresql.org; Thu, 19 Jan 2023 09:04:29 +0000 Received: from arp.lijzij.de ([185.139.33.98]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pIQqR-0006sK-MJ for pgsql-general@lists.postgresql.org; Thu, 19 Jan 2023 09:04:29 +0000 Received: from localhost (arp.lijzij.de [local]) by arp.lijzij.de (OpenSMTPD) with ESMTPA id 8a2b6ef9; Thu, 19 Jan 2023 10:04:25 +0100 (CET) Date: Thu, 19 Jan 2023 10:04:25 +0100 From: Harmen To: Tom Lane Cc: pgsql-general@lists.postgresql.org Subject: Re: row estimate for partial index Message-ID: <20230119090425.GA8608@arp.lijzij.de> References: <20230113150159.GJ91568@arp.lijzij.de> <1008139.1673713387@sss.pgh.pa.us> <20230116085523.GK91568@arp.lijzij.de> <1860907.1673881178@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <1860907.1673881178@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On Mon, Jan 16, 2023 at 09:59:38AM -0500, Tom Lane wrote: > 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; Hello Tom, thanks again for your help, it's much appreciated. > 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.) I don't understand the "dependencies" stat here. The documentation has as examples zip codes with city names, and month/day columns. Those I follow. But in this case I generally select a single "org_id", which can have an arbitrary number of "deleted" rows. I don't understand how having the org_id available gives a better prediction than taking the plain ratio of all "deleted" rows in the whole table. > 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. Noted. Are these statistics expensive? Or do you expect them to be basically noise? > 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. It does indeed for me as well! I'm not 100% sure it's because of the test data being too simple, but I'll test. Unfortunately, as you already remarked might be the case in your original reply, I don't get the same good results if I change the "deleted" column from a "boolean not null" to a "timestamptz null", though. Then it's back to (pg15.1): -> Parallel Seq Scan on contacts (cost=0.00..126284.02 rows=513913 width=16) (actual time=254.677..393.448 rows=5 loops=3) Filter: ((deleted IS NULL) AND (org_id = 5)) Rows Removed by Filter: 3333329 Test table in case that's convenient to have: DROP table if exists contacts; CREATE table contacts (id int not null, org_id int not null, deleted timestamptz null, firstname text); CREATE index contacts_orgs on contacts (org_id, id) where deleted is null; WITH ids as (select * from generate_series(0, 10000000)) insert into contacts select ids.generate_series, mod(ids.generate_series, 7), null, 'hello world' from ids; UPDATE contacts set deleted = now() where id > 100 and org_id = 5; CREATE STATISTICS dist4 (ndistinct) ON deleted, org_id FROM contacts; CREATE STATISTICS dist5 (dependencies) ON deleted, org_id FROM contacts; ANALYZE contacts; explain (analyze) select id, firstname from contacts where org_id = 5 and deleted is null order by id; Again, this is only a problem when a single org_is has such a large percentage of the table that the planner switches to table scans, which can only ever be a few org_ids. On production I've added an index with the problematic org_id in the "where" part, and that avoids the problem for now (milliseconds vs minutes), but it's not a great fix. Thanks! Harmen