From: Tom Lane <tgl@sss.pgh.pa.us>
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 16:52:27 -0400
Message-ID: <15919.1148590347@sss.pgh.pa.us> (raw)
In-Reply-To: <b42b73150605251307id0e7998td4a3693f0d456f88@mail.gmail.com>
References: <b42b73150605251307id0e7998td4a3693f0d456f88@mail.gmail.com>
"Merlin Moncure" <mmoncure@gmail.com> writes:
> 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.
The index won't help, as per this comment from planagg.c:
* We don't handle GROUP BY, because our current implementations of
* grouping require looking at all the rows anyway, and so there's not
* much point in optimizing MIN/MAX.
Given the numbers you mention (300k rows in 2000 groups) I'm not
convinced that an index-based implementation would help much; we'd
still need to fetch at least one record out of every 150, which is
going to cost near as much as seqscanning all of them.
> recent versions of mysql do much better, returning same set in < 20ms.
Well, since they don't do MVCC they can answer this query from the
index without going to the heap at all. But that still seems remarkably
fast for something that has to grovel through 300k index entries.
regards, tom lane
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: tgl@sss.pgh.pa.us, mmoncure@gmail.com
Subject: Re: is it possible to make this faster?
In-Reply-To: <15919.1148590347@sss.pgh.pa.us>
* 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