To: "Merlin Moncure" <mmoncure@gmail.com>
cc: "Postgresql Performance" <pgsql-performance@postgresql.org>
Subject: Re: is it possible to make this faster? 
In-reply-to: <b42b73150605251307id0e7998td4a3693f0d456f88@mail.gmail.com> 
References: <b42b73150605251307id0e7998td4a3693f0d456f88@mail.gmail.com>
Comments: In-reply-to "Merlin Moncure" <mmoncure@gmail.com>
	message dated "Thu, 25 May 2006 16:07:19 -0400"
Date: Thu, 25 May 2006 16:52:27 -0400
Message-ID: <15919.1148590347@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

"Merlin Moncure" <mmoncure@gmail.com> writes:
> been doing a lot of pgsql/mysql performance testing lately, and there
> is one query that mysql does much better than pgsql...and I see it a
> lot in normal development:

> select a,b,max(c) from t group by a,b;

> t has an index on a,b,c.

The index won't help, as per this comment from planagg.c:

	 * We don't handle GROUP BY, because our current implementations of
	 * grouping require looking at all the rows anyway, and so there's not
	 * much point in optimizing MIN/MAX.

Given the numbers you mention (300k rows in 2000 groups) I'm not
convinced that an index-based implementation would help much; we'd
still need to fetch at least one record out of every 150, which is
going to cost near as much as seqscanning all of them.

> recent versions of mysql do much better, returning same set in < 20ms.

Well, since they don't do MVCC they can answer this query from the
index without going to the heap at all.  But that still seems remarkably
fast for something that has to grovel through 300k index entries.

			regards, tom lane