Re: Why count(*) doest use index?

On Sat, Mar 5, 2011 at 8:02 PM, Raymond O'Donnell <rod(at)iol(dot)ie> wrote:
> On 03/03/2011 13:29, obamabarak(at)e1(dot)ru wrote:
>>
>> I use pgsql 9.0.3 and I know that postgresql tries to use the fields in
>> indexes instead of the original table if it possible
>>
>> But when I run
>>
>> SELECT COUNT(id) FROM tab
>>
>> or
>>
>> SELECT COUNT(*) FROM tab
>>
>> where there "id" is PRIMARY KEY and there are other indexes there I get
>> execution plan that doesnt use any indexes, but sequentab scanning the
>> original table.
>
> Because when you do SELECT COUNT(*) without any WHERE.... clause, then
> PostgreSQL has to scan through *all* the rows in the table in order to count
> them.
>
> Ray.
>
> --
> Raymond O'Donnell :: Galway :: Ireland
> rod(at)iol(dot)ie
>
> --
> 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
>

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?

Allan.