Re: Why count(*) doest use index?

On Sun, Mar 6, 2011 at 1:46 AM, John R Pierce <pierce(at)hogranch(dot)com> wrote:
> On 03/05/11 2:05 PM, Allan Kamau wrote:
>>
>> Is it possible in theory to efficiently perform count the primary or
>> unique indices underlying data structures, regardless whether there is
>> a WHERE clause detailing filtration base on values from such index or
>> not?
>
> indexes are not exact, due to possibly constant changes in the current
> number of visible elements in the relation.
>
>
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

I would assume the primary key or unique indexes are the cornerstone
of each insertion and deletion.
During the start of an insert into a tuple the primary and unique (not
null) indexes are first consulted. Same as the case of a delete as the
relation should still allow for insertion of a tuple having a value in
the primary index matching the value of a just deleted tuple.
If this is true it seems that the primary key and perhaps other unique
indexes do indeed contain exact details of the uniqueness of the
persisted tuples of a given relation at any given time. Even though
some other field values of the relation are being updated the number
of tuples may not change without the involvement of the primary or
unique indices.
Or am I missing a crucial point.

Allan.