Re: is it possible to make this faster?

[Bruno Wolff III <bruno(at)wolff(dot)to>]

On Thu, May 25, 2006 at 16:07:19 -0400,
  Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
> 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.
> 
> in my sample case with cardinality of 1000 for a, 2000 for b, and
> 300000 records in t, pgsql does a seq. scan on dev box in about a
> second (returning 2000 records).
> 
> recent versions of mysql do much better, returning same set in < 20ms.
> mysql explain says it uses an index to optimize the group by somehow.
> is there a faster way to write this query?

SELECT DISTINCT ON (a, b) a, b, c FROM t ORDER BY a DESC, b DESC, c DESC;