X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id C33C39FA315 for ; Thu, 25 May 2006 17:21:15 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 28130-06 for ; Thu, 25 May 2006 17:21:09 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from wolff.to (wolff.to [66.93.197.194]) by postgresql.org (Postfix) with SMTP id 9A54A9F94EA for ; Thu, 25 May 2006 17:21:09 -0300 (ADT) Received: (qmail 22909 invoked by uid 500); 25 May 2006 20:23:14 -0000 Date: Thu, 25 May 2006 15:23:14 -0500 From: Bruno Wolff III To: Merlin Moncure Cc: Postgresql Performance Subject: Re: is it possible to make this faster? Message-ID: <20060525202314.GA20747@wolff.to> Mail-Followup-To: Bruno Wolff III , Merlin Moncure , Postgresql Performance References: Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: User-Agent: Mutt/1.4.2.1i X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200605/471 X-Sequence-Number: 19258 On Thu, May 25, 2006 at 16:07:19 -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; > > 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;