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

po 18. 7. 2022 v 16:29 odesílatel Tom Lane <tgl@sss.pgh.pa.us> napsal:

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.)

Thank you for reply

Regards

Pavel