Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1w2cq1-000S9U-27 for pgsql-performance@arkaria.postgresql.org; Tue, 17 Mar 2026 22:24:33 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1w2cq0-005noJ-13 for pgsql-performance@arkaria.postgresql.org; Tue, 17 Mar 2026 22:24:32 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1w2cq0-005noA-05 for pgsql-performance@lists.postgresql.org; Tue, 17 Mar 2026 22:24:32 +0000 Received: from sss.pgh.pa.us ([68.162.161.243]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1w2cpx-00000000iE4-255Z for pgsql-performance@lists.postgresql.org; Tue, 17 Mar 2026 22:24:31 +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 62HMOSK4574350; Tue, 17 Mar 2026 18:24:28 -0400 From: Tom Lane To: Merlin Moncure cc: pgsql-performance@lists.postgresql.org Subject: Re: postgres chooses objectively wrong index In-reply-to: References: Comments: In-reply-to Merlin Moncure message dated "Tue, 17 Mar 2026 15:01:09 -0600" MIME-Version: 1.0 Content-Type: text/plain; charset="UTF-8" Content-ID: <574348.1773786268.1@sss.pgh.pa.us> Content-Transfer-Encoding: quoted-printable Date: Tue, 17 Mar 2026 18:24:28 -0400 Message-ID: <574349.1773786268@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk Merlin Moncure writes: > I've been maintaining an airflow style orchestrator in pl/pgsql, and it'= s > revealed a performance issue I just can't solve. There is a table, task= , > which may normally contain billions of rows, but only a tiny portion is > interesting for specific reasons=E2=80=94a common pattern in task-type s= ystems. > ... > Usually, we get a plan that looks like this: > Limit (cost=3D0.38..39.74 rows=3D10 width=3D563) (actual time=3D0.054.= .0.054 rows=3D0 loops=3D1) > -> Index Scan using task_concurrency_pool_priority_entered_idx on ta= sk (cost=3D0.38..705.08 rows=3D179 width=3D563) (actual time=3D0.053..0.0= 53 rows=3D0 loops=3D1) > Sometimes, based on a certain data distribution, we get results like thi= s: > Limit (cost=3D25.75..25.78 rows=3D10 width=3D563) (actual time=3D8.909.= .8.911 rows=3D0 loops=3D1) > -> Sort (cost=3D25.75..26.20 rows=3D179 width=3D563) (actual time=3D= 8.908..8.909 rows=3D0 loops=3D1) I think the fundamental problem here is that the planner is estimating 179 matching rows when the true count is 0. Getting that estimate down by, say, an order of magnitude would probably fix your issue. However, if the selectivity is already epsilon (are there really billions of rows?) it may be hard to get it down to a smaller epsilon. What statistics target are you using? How often do tasks change state? Could it be reasonable to partition the task table on state, rather than rely on an index? regards, tom lane