Subject: Re: is it possible to make this faster?
From: Scott Marlowe <smarlowe@g2switchworks.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Merlin Moncure <mmoncure@gmail.com>,
	Postgresql Performance <pgsql-performance@postgresql.org>
In-Reply-To: <15919.1148590347@sss.pgh.pa.us>
References: <b42b73150605251307id0e7998td4a3693f0d456f88@mail.gmail.com>
	<15919.1148590347@sss.pgh.pa.us>
Content-Type: text/plain
Content-Transfer-Encoding: 7bit
Message-Id: <1148591729.25526.57.camel@state.g2switchworks.com>
Mime-Version: 1.0
Date: Thu, 25 May 2006 16:15:29 -0500

On Thu, 2006-05-25 at 15:52, Tom Lane wrote:
> "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.

Well, they do, just with innodb tables.

Merlin, have you tried this against innodb tables to see what you get?