On 5/25/06, Steinar H. Gunderson <[EMAIL PROTECTED]> 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
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
