Re: count(*) optimization

On Tue, Sep 06, 2005 at 15:21:16 -0400,
huaxin zhang <uwcssa(at)gmail(dot)com> wrote:
> not sure where to put this.
>
> I run two queries:
>
> 1. select count(*) from table where indexed_column<10;
> 2. select * from table where indexed_column<10;
>
> the indexed column is not clustered at all. I saw from the trace that
> both query runs
> through index scans on that index and takes the same amount of buffer
> hits and disk read. However, shouldn't the optimizer notice that the
> first query only needs to look at the indexes
> and possibly reduce the amount of buffer/disk visits?

No, because that isn't true. Whether or not a tuple is visible to the current
transaction isn't stored in indexes. If you have more questions on this, you
should look through the archives before asking them, as this topic has been
discussed numerous times.