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

Forgive my ignorance, but I'm still learning about much of this stuff.
If you perform:

select an_id, int_value from my_table where int_value>400;

The table has an index on int_value and there are enough rows to
warrant using it. Doesn't the database perform in index scan on
int_value followed by a retrieve for the datablocks with relavent oids
to get the an_id field?

If another transaction has inserted rows into this table, won't the
index have been updated, and contain new row references? Does this
imply that the database must retrieve the row information to determine
if the row is a row from a different transaction?

thanks,

Alex Turner

On Sat, 08 Jan 2005 12:39:41 -0700, Scott Ribe
<scott_ribe(at)killerbytes(dot)com> wrote:
> > 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
>
>