From: Greg Stark <gsstark@mit.edu>
To: Alvaro Herrera <alvherre@commandprompt.com>
Cc: Miroslav ?ulc <miroslav.sulc@startnet.cz>
Cc: pgsql-sql@postgresql.org
Subject: Re: SELECT DISTINCT too slow
Date: 01 Jun 2006 11:46:28 -0400
Message-ID: <87k680hoa3.fsf@stark.xeocode.com> (raw)
In-Reply-To: <20060601145327.GA19388@surnet.cl>
References: <447EEAF1.7040305@startnet.cz>
<20060601135310.GB18513@surnet.cl>
<447EFE92.2070509@startnet.cz>
<20060601145327.GA19388@surnet.cl>
Alvaro Herrera <alvherre@commandprompt.com> writes:
> Miroslav ?ulc wrote:
> > Well, "key" is not primary key from another table. It is just a column
> > in pair "key" => "value".
> > The structure of the table is this:
> >
> > Id (primary key)
> > MRTPContactId (id of contact from table MRTPContact)
> > Key (key from pair key => value)
> > Value (value from pair key => value)
> >
> > So I want the get the list of keys used in the table.
>
> The plan you get is the most efficient possible for that query. If you
> had a table of possible keys (which should of course be FK of "Key"),
> you could get a much faster version :-)
Actually you could try the equivalent query:
SELECT Key FROM MRTPContactValue GROUP BY Key
This may or may not be faster because it can use a hash aggregate plan. I
would expect it to be faster here because there are few distinct keys and the
planner predicts that.
Eventually these two queries should be handled the same by Postgres but Hash
Aggregates are a new addition and DISTINCT/DISTINCT ON hasn't been adapted to
make use of them.
Also, incidentally, I don't see how a table of possible keys could help you
here. Nothing forces they table MRTPContactValue to use all possible keys...
--
greg
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-sql@postgresql.org
Cc: gsstark@mit.edu, alvherre@commandprompt.com, miroslav.sulc@startnet.cz
Subject: Re: SELECT DISTINCT too slow
In-Reply-To: <87k680hoa3.fsf@stark.xeocode.com>
* 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