Re: count(*) optimization

huaxin... I'll save you the time... see the topic "MUCH ADO ABOUT COUNT(*)"
and "ADVANCED INDEX USAGE".

On 9/6/05, 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?
>
> thanks
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>