Re: count(*) optimization

uwcssa(at)gmail(dot)com (huaxin zhang) writes:
> 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, it shouldn't, because that is NOT TRUE.

Indexes do not have MVCC visibility information stored in them, so
that a query cannot depend on the index to imply whether a particular
tuple is visible or not. It must read the tuple itself as well.
--
output = ("cbbrowne" "@" "acm.org")
http://www.ntlug.org/~cbbrowne/linuxdistributions.html
"I promise you a police car on every sidewalk."
-- M. Barry Mayor of Washington, DC