Обсуждение: limits of max, min optimization

On 2022-Jul-18, Pavel Stehule wrote:

> Hi
> 
> I am trying to fix one slow query, and found that optimization of min, max
> functions is possible only when there is no JOIN in the query.
> 
> Is it true?

See preprocess_minmax_aggregates() in
src/backend/optimizer/plan/planagg.c

> select max(insert_date) from foo join boo on foo.boo_id = boo.id
> where foo.item_id = 100 and boo.is_ok

Maybe it is possible to hack that code so that this case can be handled
better.


-- 
Álvaro Herrera        Breisgau, Deutschland  —  https://www.EnterpriseDB.com/

Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
> On 2022-Jul-18, Pavel Stehule wrote:
>> I am trying to fix one slow query, and found that optimization of min, max
>> functions is possible only when there is no JOIN in the query.

> See preprocess_minmax_aggregates() in
> src/backend/optimizer/plan/planagg.c
> Maybe it is possible to hack that code so that this case can be handled
> better.

The comments show this was already thought about:

     * We also restrict the query to reference exactly one table, since join
     * conditions can't be handled reasonably.  (We could perhaps handle a
     * query containing cartesian-product joins, but it hardly seems worth the
     * trouble.)

            regards, tom lane