Re: Indices for select count(*)?

On 12/21/05, Marcus Engene <mengpg(at)engene(dot)se> wrote:
> 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.
>

because in the MVCC model an index contains tuples (records) that are
dead to you (doesn't exist, becuase were deleted, updated) but that
are live to other transactions... so you still have to visit the table
to see if that tuple is live to to you and have to count it or not...

> I'm sorry if this has been discussed recently.
>
> Best regards,
> Marcus
>

--
Atentamente,
Jaime Casanova
(DBA: DataBase Aniquilator ;)