Content-Type: text/plain;
  charset="iso-8859-1"
From: Josh Berkus <josh@agliodbs.com>
Organization: Aglio Database Solutions
To: Bret Hughes <bhughes@elevating.com>,
	postgresql sql list <pgsql-sql@postgresql.org>
Subject: Re: distinct values without seq scan
Date: Wed, 7 Apr 2004 08:43:06 -0700
User-Agent: KMail/1.4.3
References: <1081271194.18380.156.camel@bretsony>
In-Reply-To: <1081271194.18380.156.camel@bretsony>
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
Message-Id: <200404070843.06144.josh@agliodbs.com>

Bret,

> I have a table indexed on a char(35) field and want a query to return a
> distinct list of the values in this column.  Is there some syntactical
> magic I can do to get these values without a sequential scan?  I assume,
> ( here we go again ) that these values are in the index somewhere and I
> seem to recall a select DISTINCT using an index in DB2 way back when but
> I may be mistaken.
>
> Searching the archives I found an email that indicated this was possible
> in 7.4 and infact is the reason I upgraded from 7.2x.

Well, leaving aside that you have a data normalization issue (these values 
should really be in a reference list with an FK), to take advantage of the 
7.4 optimization, try:

SELECT advertiser FROM logrecords GROUP BY advertiser ORDER BY advertiser;

-- 
Josh Berkus
Aglio Database Solutions
San Francisco