To: Alvaro Herrera <alvherre@commandprompt.com>
Cc: Miroslav ?ulc <miroslav.sulc@startnet.cz>,  pgsql-sql@postgresql.org
Subject: Re: SELECT DISTINCT too slow
References: <447EEAF1.7040305@startnet.cz> <20060601135310.GB18513@surnet.cl>
	<447EFE92.2070509@startnet.cz> <20060601145327.GA19388@surnet.cl>
In-Reply-To: <20060601145327.GA19388@surnet.cl>
From: Greg Stark <gsstark@mit.edu>
Organization: The Emacs Conspiracy; member since 1992
Date: 01 Jun 2006 11:46:28 -0400
Message-ID: <87k680hoa3.fsf@stark.xeocode.com>
Lines: 35
User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.4
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

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