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

I'm no database writing guru, but wouldn't it just be a matter of
adding a transaction number to an index entry so as to determine it's
newness and only retrieve entries with an older transaction number?

I'm guessing that the theory is that most insert transactions will be
committed, or only contain a small number of rows relative to the
overall size of the table, and therefore the extra overhead of
checking newer tuples won't impact the overall performance that much?

I know I'm asking kind of deep questions that really don't affect much
of anything, but I'm a devilishly curious individual, and I like
understanding things that I use well. Feel free to tell me that it's
irrelavant, or that I'm full of hot air and I don't have a good
question ;)

Alex Turner
NetEconomist

On Mon, 10 Jan 2005 10:34:46 -0600, Bruno Wolff III <bruno(at)wolff(dot)to> wrote:
> On Mon, Jan 10, 2005 at 10:26:46 -0500,
> Alex Turner <armtuk(at)gmail(dot)com> wrote:
> > 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?
>
> I don't think that oids are used in the process, but if the planner thinks
> an index scan would be better it will use one.
>
> > 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?
>
> When doing an index scan, the heap tuples still need to be checked for
> visibility to the current transaction.
>
>