Content-Type: multipart/alternative;
 boundary="------------d7KfeKs6elDgvnOG12QYy4lP"
Message-ID: <381e7d47-8021-458d-9a64-0cdb80b004f0@gmail.com>
Date: Wed, 18 Mar 2026 11:27:26 +0600
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: postgres chooses objectively wrong index
To: Merlin Moncure <mmoncure@gmail.com>, Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@lists.postgresql.org
References: 
 <CAHyXU0xkTBW4p2N9QNLwye3O5SgzSGSMiPjFb7P8MyXCrEhhwg@mail.gmail.com>
 <574349.1773786268@sss.pgh.pa.us>
 <CAHyXU0yGKAvREkT47Oiw19xhicA2=peCSYc235s1hhna==BAVQ@mail.gmail.com>
Content-Language: en-US
From: Alexey Ermakov <alexius.work@gmail.com>
In-Reply-To: 
 <CAHyXU0yGKAvREkT47Oiw19xhicA2=peCSYc235s1hhna==BAVQ@mail.gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/381e7d47-8021-458d-9a64-0cdb80b004f0%40gmail.com>
Precedence: bulk

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

On 2026-03-18 04:52, Merlin Moncure wrote:
> On Tue, Mar 17, 2026 at 4:16 PM Alexey Ermakov 
> <alexius.work@gmail.com> wrote:
>
>     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.
>
>
> This unfortunately fails, probably because the table type includes 
> system columns (despite not using them).
> orchestrator_service_user@orchestrator=> create statistics  task_stats 
> (mcv) on (async.task_execution_state(task)) from async.task;
> ERROR:  statistics creation on system columns is not supported
>
> This would require some refactoring to fix.

Interesting... In that case functional index should help (as it also 
makes statistic for the planner):

create index concurrently on task_task_execution_state_idx async.task using btree ((async.task_execution_state(task)));

analyze async.task;

Perhaps multicolumn index will also help for queries but hard to say 
without knowing distributions. We could check state distribution info 
after index creation and analyze with query like this:

select * from pg_stats where tablename = 'task_task_execution_state_idx' \gx


--

Alexey Ermakov

--------------d7KfeKs6elDgvnOG12QYy4lP
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 04:52, Merlin Moncure
      wrote:<br>
    </div>
    <blockquote type="cite"
cite="mid:CAHyXU0yGKAvREkT47Oiw19xhicA2=peCSYc235s1hhna==BAVQ@mail.gmail.com">
      <meta http-equiv="content-type" content="text/html; charset=UTF-8">
      <div dir="ltr">
        <div dir="ltr">
          <div dir="ltr" class="gmail_attr">On Tue, Mar 17, 2026 at
            4:16 PM Alexey Ermakov &lt;<a
              href="mailto:alexius.work@gmail.com"
              moz-do-not-send="true" class="moz-txt-link-freetext">alexius.work@gmail.com</a>&gt;
            wrote:<br>
          </div>
          <blockquote class="gmail_quote"
style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">
            <div>
              <div>On 2026-03-18 03:01, Merlin Moncure wrote:<br>
              </div>
              <blockquote type="cite">
                <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>
            </div>
          </blockquote>
          <div><br>
          </div>
          <div>This unfortunately fails, probably because the table type
            includes system columns (despite not using them).</div>
          <div> </div>
          <div>orchestrator_service_user@orchestrator=&gt; create
            statistics  task_stats (mcv) on
            (async.task_execution_state(task)) from async.task;<br>
            ERROR:  statistics creation on system columns is not
            supported</div>
          <div><br>
          </div>
          <div>This would require some refactoring to fix. <br>
          </div>
        </div>
      </div>
    </blockquote>
    <p>Interesting... In that case functional index should help (as it
      also makes statistic for the planner):<br>
    </p>
    <pre>create index concurrently on task_task_execution_state_idx async.task using btree ((async.task_execution_state(task)));
</pre>
    <pre>analyze async.task;</pre>
    <p>Perhaps multicolumn index will also help for queries but hard to
      say without knowing distributions. We could check state
      distribution info after index creation and analyze with query like
      this:<br>
    </p>
    <pre>select * from pg_stats where tablename = 'task_task_execution_state_idx' \gx
</pre>
    <p><br>
    </p>
    <p>--</p>
    <p>Alexey Ermakov<br>
    </p>
  </body>
</html>

--------------d7KfeKs6elDgvnOG12QYy4lP--