From: | Jeff Janes <jeff(dot)janes(at)gmail(dot)com> |
---|---|
To: | Pawel Veselov <pawel(dot)veselov(at)gmail(dot)com> |
Cc: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: min/max performance inequality. |
Date: | 2015-01-07 23:34:49 |
Message-ID: | CAMkU=1zg7ar+k1QxCUsQvCL-+uXXTanQ18TnxtS7zAwP8tLkPg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Wed, Jan 7, 2015 at 3:00 PM, Pawel Veselov <pawel(dot)veselov(at)gmail(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2015-01-07 23:35:51 | Re: min/max performance inequality. |
Previous Message | Craig Ringer | 2015-01-07 23:26:20 | Re: ALTER TABLE to ADD BDR global sequence |