Re: Postgresql simple query performance question

In response to André Volpato <andre.volpato@ecomtecnologia.com.br>:

> Richard Huxton escreveu:
> > Reg Me Please wrote:
> >> While I would not spend resources in fine tuning the count(*), I would
> >> spend some to underastand why and how the other ones do it better.
> >>
> >> Just to be better.
> >
> > The problem is well understood, and there is extensive discussion in
> > the mailing lists archives. The basic problem is that with PG's
> > implementation of MVCC the indexes don't have row visibility
> > information. The simple solution of adding it to every index entry
> > would increase index size substantially imposing costs on every index
> > access and update.
> >
> > There's a thread in -hackers called "Visibility map thoughts" that is
> > looking at the situation again and if/how to implement visibility
> > information in a compact form.
>
>
> Remember that you can always use serial fields to count a table, like:
>
> alter table foo add id serial;
> select id from foo order by id desc limit 1;
>
> This should return the same value than count(*), in a few msecs.

I don't think so.  What kind of accuracy do you have when rows are
deleted?  Also, sequences are not transactional, so rolled-back
transactions will increment the sequence without actually adding
rows.

--
Bill Moran
http://www.potentialtech.com