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

On Mon, Jan 10, 2005 at 11:51:51AM -0500, Alex Turner wrote:
> 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?

No, it's more complex than that. Index entries would have to be labeled
with both a creation transaction Id and a destruction transaction Id
(xmin and xmax. Probably it'd also need Cmin and Cmax to be completely
consistent.) Keeping them in sync would be prone to deadlock because
it'd have to simultaneously update the table proper and the possibly
multiple indexes. Plus, having all those identifiers in the index file
would imply more I/O costs.

> 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?

The choice is yours (or whoever's): if you absolutely need exact
numbers, you pay the cost of having a trigger. OTOH if you can do with
estimates, you can use the reltuples column from pg_class.

--
Alvaro Herrera (<alvherre[(at)]dcc(dot)uchile(dot)cl>)
"There is evil in the world. There are dark, awful things. Occasionally, we get
a glimpse of them. But there are dark corners; horrors almost impossible to
imagine... even in our worst nightmares." (Van Helsing, Dracula A.D. 1972)