Re: Slow COUNT

On 12/2/05, Poul Møller Hansen <freebsd(at)pbnet(dot)dk> wrote:
> Rodrigo Gonzalez wrote:
> > I answer here so each one can help you.
> >
> > Cannot understand what you mean....index is numbered? Are you talking
> > about "autoincrement" as called in mysql? use sequences please
> >
>
> Sorry, forgot that your email address was in the reply-to field.
>
> I was seeking a solution on how to make a faster count on a lot of rows,
> and I was wondering on the difference between PostgreSQL's and MySQL's
> (MyISAM) of handling counts.
>
> I understand the advantages of MVCC compared to row/table locking.
> And as far as I have understood PostgreSQL count the rows looping
> through all rows, and that's why it takes that long when there are many
> rows.
>
> But how is MySQL (MyISAM) doing it, and why doesn't that way work in
> the MVCC model.
>
>
> Thanks,
> Poul
>

That's because MyISAM isn't concerned about transactions and
visibility stuff... it simply stores and increments...

in postgres you have to now if the row is visible to the transaction
that is counting, if the row was deleted by a concurrent
transaction... etc, etc... it's not as easy as insert, increment...

so the way to do it is create a trigger that record in a table the
number of rows...
in postgres there isn't such mechanism implicit for all tables because
it will be a penalty for both: insert and deletes in all tables and
the case is that there few tables were you want know exact counts, if
any

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