MIME-Version: 1.0
References: 
 <CAD2_BUOH-Y4QkJiibNjD8pSin_+qCWn+vJOYfdnQdP40jzNzVw@mail.gmail.com>
 <a48fe1da-f78b-4aac-abed-c957cb568fac@postgrespro.ru>
In-Reply-To: <a48fe1da-f78b-4aac-abed-c957cb568fac@postgrespro.ru>
From: David Rowley <dgrowleyml@gmail.com>
Date: Tue, 2 Sep 2025 16:41:06 +1200
Message-ID: 
 <CAApHDvr78vT9=v=0EK0aAvhTqwjWYwwG_KouEzaJSYpim9MtZg@mail.gmail.com>
Subject: Re: Planner makes sub-optimal execution plan
To: Alena Rybakina <a.rybakina@postgrespro.ru>
Cc: =?UTF-8?B?0JDQu9C10LrRgdC10Lkg0JHQvtGA0YnRkdCy?= <aborschev@gmail.com>,
	pgsql-performance@lists.postgresql.org
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/CAApHDvr78vT9%3Dv%3D0EK0aAvhTqwjWYwwG_KouEzaJSYpim9MtZg%40mail.gmail.com>
Precedence: bulk

On Tue, 2 Sept 2025 at 00:41, Alena Rybakina <a.rybakina@postgrespro.ru> wr=
ote:
> After disabling MIN/MAX optimization in the grouping_planner function:
> /*
>           * Preprocess MIN/MAX aggregates, if any.  Note: be careful abou=
t
>           * adding logic between here and the query_planner() call.
> Anything
>           * that is needed in MIN/MAX-optimizable cases will have to be
>           * duplicated in planagg.c.
>           */
>          //if (parse->hasAggs)
>          //    preprocess_minmax_aggregates(root);
>
> I got a better query plan, but I=E2=80=99m still investigating what went =
wrong.

This is basically just the standard issue people have with how we cost LIMI=
T.

If you look at this part:

->  Limit  (cost=3D0.43..7.91 rows=3D1 width=3D8)
          Output: docum.dt
          ->  Index Scan using docum_dt_7ee1d676 on public.docum
(cost=3D0.43..420487.43 rows=3D56222 width=3D8)

you can see that the Limit total cost is startup (0.43) plus 420487.43
/ 56222 * 1.

The problem is that the planner assumes there's no correlation between
dt and dt_real. It thinks because a decent number of rows have dt_real
>=3D '2025-08-14 09:44:09.033592' that it'll find the LIMIT 1 row fairly
quickly by scanning the docum_dt_7ee1d676 index. Unfortunately, it's
not quick because the rows matching  dt_real >=3D '2025-08-14
09:44:09.033592' are nearly at the end of the index.

In v16+, using something like MIN(docum.dt ORDER BY dt) AS "dt__min"
should force the planner into giving a better plan. ORDER BY /
DISTINCT aggregates are a bit less efficient in versions earlier than
that, so doing that would require a bit of additional work if using
v14. It might not be too bad, though.

David