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 1pGj6D-0000zK-VD for pgsql-general@arkaria.postgresql.org; Sat, 14 Jan 2023 16:09:41 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pGj6C-0006Lv-MP for pgsql-general@arkaria.postgresql.org; Sat, 14 Jan 2023 16:09:40 +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 1pGLZF-0000cv-1B for pgsql-general@lists.postgresql.org; Fri, 13 Jan 2023 15:02:05 +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 1pGLZC-0001UN-6z for pgsql-general@lists.postgresql.org; Fri, 13 Jan 2023 15:02:04 +0000 Received: from localhost (arp.lijzij.de [local]) by arp.lijzij.de (OpenSMTPD) with ESMTPA id 27c8a412 for ; Fri, 13 Jan 2023 16:01:59 +0100 (CET) Date: Fri, 13 Jan 2023 16:01:59 +0100 From: Harmen To: pgsql-general@lists.postgresql.org Subject: row estimate for partial index Message-ID: <20230113150159.GJ91568@arp.lijzij.de> MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Disposition: inline Content-Transfer-Encoding: 8bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk Hi, I'm trying to figure out why a certain query doesn't use a partial index. The context: The table has about 100M rows with "contacts". Every contact belongs to an "org", and can be marked "deleted". Since we're generally only interested in non-deleted contacts we have a (partial) index on the table: "contact_organization_id" btree (org_id, id) WHERE deleted IS NULL for queries such as: "select * from contacts where org_id=123 and deleted is null order by id" Works well enough. However, we now have an org_id which has > 10% of the rows, but only a handful rows where "deleted is null" matches (so the org has a lot of "deleted" contacts). The planner doesn't like this and it falls back to a full table scan for the above query. I've added a dedicated index just for that org_id, to see if that helps: "org123" btree (id) WHERE deleted IS NULL AND org_id = 123 The planner seems to use it now, however the row estimate is way off: my_db=> explain SELECT c.id, COALESCE(c.first_name, '') FROM contacts AS c WHERE c.organization_id = 123 AND c.deleted IS NULL ORDER BY id ASC; QUERY PLAN ──────────────────────────────────────────────────────────────────────────────────────────────────── Gather Merge (cost=1761204.72..1883324.98 rows=1046672 width=36) Workers Planned: 2 -> Sort (cost=1760204.70..1761513.04 rows=523336 width=36) Sort Key: id -> Parallel Bitmap Heap Scan on contact c (cost=318.13..1696183.14 rows=523336 width=36) Recheck Cond: ((deleted IS NULL) AND (organization_id = 8448)) -> Bitmap Index Scan on org123 (cost=0.00..4.13 rows=1256006 width=0) (7 rows) Time: 3.337 ms That "1256006" estimate from the org123 index is wrong. There are 7 contacts in that org: -> Bitmap Index Scan on org8448 (cost=0.00..4.13 rows=1256006 width=0) (actual time=0.841..0.841 rows=7 loops=1) Is this expected? Is there a way to improve this? Thanks! Harmen