Re: is it possible to make this faster?

On 5/25/06, Steinar H. Gunderson <sgunderson@bigfoot.com> wrote:
> On Thu, May 25, 2006 at 04:07:19PM -0400, Merlin Moncure 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;

>   select a,b,(select c from t t2 order by c desc where t1.a=t2.a and t1.b=t2.b)
>   from t t1 group by a,b;

this came out to a tie with the group by approach, although it
produced a different (but similar) plan.  we are still orders of
magnitude behind mysql here.

Interestingly, if I extract out the distinct values of a,b to a temp
table and rejoin to t using your approach, I get competitive times
with mysql.  this means the essential problem is:

select a,b from t group by a,b

is slow.  This feels like the same penalty for mvcc we pay with count(*)...hm.

merlin