On 2006-04-25, Tony Caduto <[EMAIL PROTECTED]> wrote: > Hi, > I have a client who has a 25 million row table that is used to keep > track of financial security info. > So far it has worked great, but today someone wanted to get all the tax > codes(there are lot's of dupes) from the table. > So we tried this: > > select DISTINCT tax_code from warehouse.sec_trans > We let this run for 1/2 hour or so and canceled it. > > Then I tried select DISTINCT ON (tax_code) tax_code from warehouse.sec_trans > > same deal, had to cancel it.
select tax_code from warehouse.sec_trans group by tax_code; Both of the DISTINCT variants rely on sorting. GROUP BY, on the other hand, can use a hash aggregate, which will be much more efficient where the number of distinct values is low. Of course it will still need to scan the whole table... -- Andrew, Supernews http://www.supernews.com - individual and corporate NNTP services ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
