Skip site navigation (1) Skip section navigation (2)

Re: select ... distinct performance

From: Don Bowman <don(at)sandvine(dot)com>
To: 'Martijn van Oosterhout' <kleptog(at)svana(dot)org>,Don Bowman <don(at)sandvine(dot)com>
Cc: "'pgsql-general(at)postgresql(dot)org'" <pgsql-general(at)postgresql(dot)org>
Subject: Re: select ... distinct performance
Date: 2004-02-01 02:34:20
Message-ID: FE045D4D9F7AED4CBFF1B3B813C85337045D7DD6@mail.sandvine.com (view raw or flat)
Thread:
Lists: pgsql-general
From: Martijn van Oosterhout [mailto:kleptog(at)svana(dot)org]
>
> On Wed, Jan 28, 2004 at 11:20:30PM -0500, Don Bowman wrote:
> > I have a table with a large number of rows (10K in the 
> example below,
> > but >1M in some databases). I would like to find the distinct 
> > values for one of the columns. The column is indexed.
> > 
> > I would have expected that this would be a very fast operation,
> > simply walking down the index. In the example below, there is
> > only 1 unique value, but it takes 2 seconds. I would have
> > expected more like ~50ms.
> 
> The problem is that the index doesn't contain info about 
> which rows are
> visibile in your current transaction, so it has to load the 
> entire table to
> check. Looks like it used the index to avoid a sort step. I 
> don't think
> there is a way to write this that doesn't need the whole table.
> 
> Hope this helps,
> -- 

It would appear that postgresql does not support index-only fetches
(e.g. DB2).

or, perhaps a materialized view. I see there is some work on going
for this.

I haven't tried a stored procedure like this...

select first name into prev_name from Table 
while FETCH_OK: 
  return prev_name 
  fetch first name into prev_name from Table where name > prev_name 
end

to see if it can walk the index.

--don

pgsql-general by date

Next:From: Tom LaneDate: 2004-02-01 03:52:57
Subject: Re: Incorrect Results From Select When Using Non-English Characters
Previous:From: Martijn van OosterhoutDate: 2004-02-01 02:15:25
Subject: Re: Help: System requirements for postgresql 7.4.1

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group