Re: Indices for select count(*)?

From: Marcus Engene <mengpg(at)engene(dot)se>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: Indices for select count(*)?
Date: 2005-12-21 22:27:51
Message-ID: 43A9D6E7.2010501@engene.se
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Greg Stark wrote:
> Alexander Scholz <alexander(dot)scholz1(at)freenet(dot)de> writes:
>
>>Hi, thank you for your answer.
>>
>>Regarding the performance flow when trying to find out how many records are
>>currently being stored in the table, I don't see how an index should help...
>>Nevertheless we've created an unique index on "ID" but SELECT count("ID") from
>>"XYZ" still takes 35 seconds*. (ID is the primary key basing on a sequence,
>>select count(*) isn't faster.)
>>
>>So - what kind of indexing would speed this up then?
>
>
> No form of indexing can speed this up. To answer the server has to look at
> every record and count up how many of them should be included in your result.

Why couldn't it be possible to count # of items in an index?
The density of the information (items/inode|block|whatever it's called
in btrees) is likely to be much higher giving less disk i/o.

I'm sorry if this has been discussed recently.

Best regards,
Marcus

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Jaime Casanova 2005-12-21 22:49:39 Re: Indices for select count(*)?
Previous Message Qingqing Zhou 2005-12-21 22:19:45 Re: PostgreSQL crashing