Re: slow plan for min/max

Поиск
Список
Период
Сортировка
От Matt Clark
Тема Re: slow plan for min/max
Дата
Msg-id LFEIJBEOKGPDHCEMDGNFOEHNCAAA.matt@ymogen.net
обсуждение исходный текст
Ответ на Re: slow plan for min/max  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-performance
> > Know what we (OK, I) need?  An explicitly non-aggregate max() and min(),
> > implemented differently, so they can be optimised.
>
> Not per se.  The way I've been visualizing this is that we add to
> pg_aggregate a column named, say, aggsortop, with the definition:
...snip of cunning potentially geralisable plan...
> How do you structure the resulting query plan, if it's at all complex
> (think multiple aggregate calls...)?  I'm not clear on the answers to
> any of those questions, so I'm not volunteering to try to code it up ...

So, you're not going to code it, I'm not going to code it, I doubt anyone
else is soon.

The issue is going to remain then, that max() and min() are implemented in a
way that is grossly counterintuitively slow for 99% of uses.  It's not bad,
or wrong, just a consequence of many higher level factors.  This should
therefore be very prominently flagged in the docs until there is either a
general or specific solution.

FYI I have rewritten 4 queries today to work around this (with nice
performance benefits) as a result of this thread.  Yeah, I should have
spotted the _silly_ seq scans beforehand, but if you're not looking, you
don't tend to see.  Best improvement is 325msec to 0.60msec!

I'm happy to do the doc work.

M


В списке pgsql-performance по дате отправления:

Предыдущее
От: Josh Berkus
Дата:
Сообщение: Re: Reading data in bulk - help?
Следующее
От: Bruce Momjian
Дата:
Сообщение: Re: Hardware recommendations to scale to silly load