public inbox for [email protected]  

help / color / mirror / Atom feed

From: Tom Lane <[email protected]>
To: Merlin Moncure <[email protected]>
Cc: [email protected]
Subject: Re: postgres chooses objectively wrong index
Date: Tue, 17 Mar 2026 18:24:28 -0400
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAHyXU0xkTBW4p2N9QNLwye3O5SgzSGSMiPjFb7P8MyXCrEhhwg@mail.gmail.com>
References: <CAHyXU0xkTBW4p2N9QNLwye3O5SgzSGSMiPjFb7P8MyXCrEhhwg@mail.gmail.com>

Merlin Moncure <[email protected]> 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—a common pattern in task-type systems.
> ...

> Usually, we get a plan that looks like this:

>  Limit  (cost=0.38..39.74 rows=10 width=563) (actual time=0.054..0.054 rows=0 loops=1)
>    ->  Index Scan using task_concurrency_pool_priority_entered_idx on task  (cost=0.38..705.08 rows=179 width=563) (actual time=0.053..0.053 rows=0 loops=1)

> Sometimes, based on a certain data distribution, we get results like this:

> Limit  (cost=25.75..25.78 rows=10 width=563) (actual time=8.909..8.911 rows=0 loops=1)
>   ->  Sort  (cost=25.75..26.20 rows=179 width=563) (actual time=8.908..8.909 rows=0 loops=1)

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

view thread (12+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: postgres chooses objectively wrong index
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox