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.
|
| Список | 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 по дате отправления: