Content-Type: multipart/alternative;
 boundary="------------NI6XawikQRdcLILEurEe7w91"
Message-ID: <e0d87b50-8a38-4e25-bc88-86aa83029afb@gmail.com>
Date: Wed, 18 Mar 2026 04:16:30 +0600
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: postgres chooses objectively wrong index
To: Merlin Moncure <mmoncure@gmail.com>,
 pgsql-performance@lists.postgresql.org
References: 
 <CAHyXU0xkTBW4p2N9QNLwye3O5SgzSGSMiPjFb7P8MyXCrEhhwg@mail.gmail.com>
Content-Language: en-US
From: Alexey Ermakov <alexius.work@gmail.com>
In-Reply-To: 
 <CAHyXU0xkTBW4p2N9QNLwye3O5SgzSGSMiPjFb7P8MyXCrEhhwg@mail.gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/e0d87b50-8a38-4e25-bc88-86aa83029afb%40gmail.com>
Precedence: bulk

This is a multi-part message in MIME format.
--------------NI6XawikQRdcLILEurEe7w91
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit

On 2026-03-18 03:01, Merlin Moncure wrote:
> 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.
>
> ...
>
> I'm wondering if there are other tricks that might apply here, for 
> example, multi column index statistics...curious if anyone has 
> thoughts on that.
>
> Any suggestions?
>
> merlin
>
Hello. I think planner doesn't have information about distribution of 
*async.task_execution_state(task)* unless it's part of any full index. I 
would try to give that with extended statistics (postgresql 14+):

create statistics (mcv) task_task_execution_state_stat on ((async.task_execution_state(task))) from async.task;
analyze async.task;

If that won't help - please show distribution from pg_stats_ext view for 
extended statistic above.


--
Alexey Ermakov

--------------NI6XawikQRdcLILEurEe7w91
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit

<!DOCTYPE html>
<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body>
    <div class="moz-cite-prefix">On 2026-03-18 03:01, Merlin Moncure
      wrote:<br>
    </div>
    <blockquote type="cite"
cite="mid:CAHyXU0xkTBW4p2N9QNLwye3O5SgzSGSMiPjFb7P8MyXCrEhhwg@mail.gmail.com">
      <meta http-equiv="content-type" content="text/html; charset=UTF-8">
      <div dir="ltr">
        <div dir="ltr">
          <div>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.</div>
          <div><br>
          </div>
          ...<br>
          <div><br>
          </div>
          <div>I'm wondering if there are other tricks that might apply
            here, for example, multi column index statistics...curious
            if anyone has thoughts on that.</div>
          <div><br>
          </div>
          <div>Any suggestions?</div>
          <div><br>
          </div>
          <div>merlin</div>
          <div><br>
          </div>
        </div>
      </div>
    </blockquote>
    <p>Hello. I think planner doesn't have information about
      distribution of <b>async.task_execution_state(task)</b> unless
      it's part of any full index. I would try to give that with
      extended statistics (postgresql 14+):<br>
    </p>
    <pre>create statistics (mcv) task_task_execution_state_stat on ((async.task_execution_state(task))) from async.task;
analyze async.task;</pre>
    <p>If that won't help - please show distribution from pg_stats_ext
      view for extended statistic above.<br>
    </p>
    <p><br>
      --<br>
      Alexey Ermakov</p>
    <p></p>
  </body>
</html>

--------------NI6XawikQRdcLILEurEe7w91--