On 5/25/06, Steinar H. Gunderson <sgunderson@xxxxxxxxxxx> 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
