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
or, perhaps a materialized view. I see there is some work on going
I haven't tried a stored procedure like this...
select first name into prev_name from Table
fetch first name into prev_name from Table where name > prev_name
to see if it can walk the index.
pgsql-general by date
|Next:||From: Tom Lane||Date: 2004-02-01 03:52:57|
|Subject: Re: Incorrect Results From Select When Using Non-English Characters |
|Previous:||From: Martijn van Oosterhout||Date: 2004-02-01 02:15:25|
|Subject: Re: Help: System requirements for postgresql 7.4.1|