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?
Merlin