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

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

On 2026-03-19 00:38, Merlin Moncure wrote:
> On Tue, Mar 17, 2026 at 11:27 PM Alexey Ermakov 
> <alexius.work@gmail.com> wrote:
>
>     On 2026-03-18 04:52, Merlin Moncure wrote:
>>
>>         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;
>
>
> This can't help performance, as the index...
> CREATE INDEX ON async.task(concurrency_pool, priority, entered)
> WHERE async.task_execution_state(task) = 'READY';
>
> ...is very precisely configured to provide exactly what's needed; I 
> need tasks for that exact pool in that exact order if and only if 
> ready.  The partial predicate is designed to keep the index nice and 
> small since only a small portion of tasks would be eligible at any 
> specific time.

The index I suggested was not intended to be used by such queries, it's 
only a way to provide statistics for the planner as `create statistics` 
on expression is not working here.

It might not be enough and require additional columns (in that case it 
will be replacement for your index) or perhaps elevated statistics 
target. It could even make things worse but I'm sure you have ways to 
test that before putting it on an important database.

It should help if planner underestimate number of rows but even then 
total estimation won't be perfect when we have 2 conditions on state 
that obviously statistically dependent. Combining both conditions on 
application side would work much better if that is possible...

What would also might help there - output of `explain (analyze, 
buffers)` of a query that really had a bad plan and executes in seconds 
with sizes of indexes. And same output but with `set enable_sort = off` 
to see plan that supposed to be better. And just in case number of 
live/dead tuples in that table from pg_stat_user_tables.

--

Alexey Ermakov


--------------d1P55AfiRpc8Xj38FPurpBdN
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-19 00:38, Merlin Moncure
      wrote:<br>
    </div>
    <blockquote type="cite"
cite="mid:CAHyXU0zhdKYDvt_iON5kVSa4AXa=p_vLP1JMs3u0FFA29rP-aQ@mail.gmail.com">
      <meta http-equiv="content-type" content="text/html; charset=UTF-8">
      <div dir="ltr">
        <div dir="ltr">
          <div dir="ltr">On Tue, Mar 17, 2026 at 11:27 PM Alexey Ermakov
            &lt;<a href="mailto:alexius.work@gmail.com" target="_blank"
              moz-do-not-send="true" class="moz-txt-link-freetext">alexius.work@gmail.com</a>&gt;
            wrote:</div>
          <div dir="ltr">
            <div class="gmail_quote">
              <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 04:52, Merlin Moncure wrote:<br>
                  </div>
                  <blockquote type="cite">
                    <div dir="ltr">
                      <div dir="ltr">
                        <blockquote class="gmail_quote"
style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">
                          <div>
                            <div dir="ltr">
                              <div dir="ltr">
                                <div>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+):</div>
                              </div>
                            </div>
                            <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>
                </div>
              </blockquote>
              <div><br>
              </div>
              <div>This can't help performance, as the index...</div>
              <div>CREATE INDEX ON async.task(concurrency_pool,
                priority, entered)<br>
                WHERE async.task_execution_state(task) = 'READY';</div>
              <div><br>
              </div>
              <div>...is very precisely configured to provide exactly
                what's needed; I need tasks for that exact pool in that
                exact order if and only if ready.  The partial predicate
                is designed to keep the index nice and small since only
                a small portion of tasks would be eligible at any
                specific time.</div>
            </div>
          </div>
        </div>
      </div>
    </blockquote>
    <p>The index I suggested was not intended to be used by such
      queries, it's only a way to provide statistics for the planner as
      `create statistics` on expression is not working here.</p>
    <p>It might not be enough and require additional columns (in that
      case it will be replacement for your index) or perhaps elevated
      statistics target. It could even make things worse but I'm sure
      you have ways to test that before putting it on an important
      database.</p>
    <p>It should help if planner underestimate number of rows but even
      then total estimation won't be perfect when we have 2 conditions
      on state that obviously statistically dependent. Combining both
      conditions on application side would work much better if that is
      possible...<br>
    </p>
    <p>What would also might help there - output of `explain (analyze,
      buffers)` of a query that really had a bad plan and executes in
      seconds with sizes of indexes. And same output but with `set
      enable_sort = off` to see plan that supposed to be better. And
      just in case number of live/dead tuples in that table from
      pg_stat_user_tables.</p>
    <p>--</p>
    <p>Alexey Ermakov</p>
    <p><br>
    </p>
  </body>
</html>

--------------d1P55AfiRpc8Xj38FPurpBdN--