From: Bruno Wolff III <bruno@wolff.to>
To: Merlin Moncure <mmoncure@gmail.com>
Cc: Postgresql Performance <pgsql-performance@postgresql.org>
Subject: Re: is it possible to make this faster?
Date: Thu, 25 May 2006 15:23:14 -0500
Message-ID: <20060525202314.GA20747@wolff.to> (raw)
In-Reply-To: <b42b73150605251307id0e7998td4a3693f0d456f88@mail.gmail.com>
References: <b42b73150605251307id0e7998td4a3693f0d456f88@mail.gmail.com>
On Thu, May 25, 2006 at 16:07:19 -0400,
Merlin Moncure <mmoncure@gmail.com> 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;
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: bruno@wolff.to, mmoncure@gmail.com
Subject: Re: is it possible to make this faster?
In-Reply-To: <20060525202314.GA20747@wolff.to>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox