Re: min/max performance inequality.

Поиск
Список
Период
Сортировка
От Jeff Janes
Тема Re: min/max performance inequality.
Дата
Msg-id CAMkU=1zg7ar+k1QxCUsQvCL-+uXXTanQ18TnxtS7zAwP8tLkPg@mail.gmail.com
обсуждение исходный текст
Ответ на min/max performance inequality.  (Pawel Veselov <pawel.veselov@gmail.com>)
Ответы Re: min/max performance inequality.  (Pawel Veselov <pawel.veselov@gmail.com>)
Список pgsql-general
On Wed, Jan 7, 2015 at 3:00 PM, Pawel Veselov <pawel.veselov@gmail.com> wrote:
Hi.

I was wondering how come there is such a drastic difference between finding max and min. Seems like "index scan backwards" is really bad... The table is freshly re-indexed just in case. I added a count(*) in there, forcing the seq scan, and it's even better than the backwards index scan...

db=> EXPLAIN ANALYZE select min(rowdate) from r_agrio where blockid = 4814;

It crawls the data in rowdate order (either forward or reverse) until it finds the first 4814.  Crawling forward it finds 4814 very early. Crawling backwards it has to pass through a bunch of non-4814 before it finds the first 4814.

This fact doesn't show up in your EXPLAIN ANALYZE, but if you used a more modern version of postgresql (9.2 or above) there would be another line for "Rows Removed by Filter:" which would tell the story of what is going on.

If you have a composite index on (blockid, rowdate), it would help make this much faster, as it can go directly to the desired row.

Cheers,

Jeff

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

Предыдущее
От: Craig Ringer
Дата:
Сообщение: Re: ALTER TABLE to ADD BDR global sequence
Следующее
От: Tom Lane
Дата:
Сообщение: Re: min/max performance inequality.