Re: Indices for select count(*)?

mengpg@engene.se (Marcus Engene) writes:
> Greg Stark wrote:
>> Alexander Scholz <alexander.scholz1@freenet.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.

The index does not contain tuple visibility information, and so is
*useless* for the purpose.  It does not contain the useful information
you evidently imagine it does.

This question is asked steadily, frequently.
--
output = ("cbbrowne" "@" "ntlug.org")
http://www3.sympatico.ca/cbbrowne/
Rules of the Evil Overlord #32. "I will not fly into a rage and kill a
messenger who brings me bad news  just to illustrate how evil I really
am. Good messengers are hard to come by."
<http://www.eviloverlord.com/>