Re: Postgresql simple query performance question

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.

--
ACV