Re: does "select count(*) from mytable" always do a seq

> No offense or anything, but that doesn't make any sense. If you are
> running count(*) against a table, it still has to worry about MVCC,
> and which rows are visible to your transaction. What difference does
> it make, table or index, the system still has to figure out which rows
> are visible in the current transaction, so why not use the index?

Your mistake seems to be assuming that row visibility is tracked in the
index. As was stated earlier in the thread, row visibility information is
not available in the index, therefore rows have to be looked at to determine
whether they're visible. What this means is that using the index would only
add an additional unnecessary step.

> (The example is really count(pkey) because count(*) is always going to
> do a seq scan I reckon - and could probably never use an index).

No, if there is an index on a column that is required, such as a primary
key, then count(pkey) is equal to count(*). Many databases make use of this
fact to optimize performance of count(*) by using an index scan.

--
Scott Ribe
scott_ribe(at)killerbytes(dot)com
http://www.killerbytes.com/
(303) 665-7007 voice