Re: Speedier count(*)

Поиск
Список
Период
Сортировка
От Tino Wildenhain
Тема Re: Speedier count(*)
Дата
Msg-id 1123757536.27613.10.camel@Andrea.peacock.de
обсуждение исходный текст
Ответ на Re: Speedier count(*)  (Gavin Sherry <swm@alcove.com.au>)
Ответы Re: Speedier count(*)  (PFC <lists@boutiquenumerique.com>)
Список pgsql-performance
Am Donnerstag, den 11.08.2005, 20:36 +1000 schrieb Gavin Sherry:
> On Thu, 11 Aug 2005, Tino Wildenhain wrote:
>
> > Am Donnerstag, den 11.08.2005, 00:40 -0400 schrieb Mark Cotner:
> > > Here's a trigger I wrote to perform essentially the same purpose.  The nice
> > > thing about this is it keeps the number up to date for you, but you do incur
> > > slight overhead.
> > ...
> > >
> > > CREATE TRIGGER del_rowcount_tr BEFORE DELETE ON test FOR EACH ROW EXECUTE
> > >    PROCEDURE del_rowcount();
> > > CREATE TRIGGER add_rowcount_tr BEFORE INSERT ON test FOR EACH ROW EXECUTE
> > >    PROCEDURE add_rowcount();
> > >
> > > INSERT INTO rowcount (tablename) VALUES ('test');
> > ...
> >
> > beware of problems with concurrency and even what happens
> > if transactions roll back. Maybe you can "fix" it a bit
> > by regulary correcting the count via cronjob or so.
>
> What problems? MVCC takes care of this.

Actually in this case MVCC works against you.
Just imagine some competing transactions to insert
end delete at will.

You could lock the count table to prevent the problem
where 2 competing transactions do an insert, read the
start value and add 1 to it and then write the result
- which is n+1 rather then n+2 - so you are off by one.
Think of the same when one transaction inserts 100
and the other 120. Then you could even be off by 100.

But locking probably gets your worser performance then
simply count(*) all the time if you insert a lot. Also
prepare for the likeness of deadlocks.


В списке pgsql-performance по дате отправления:

Предыдущее
От: Gavin Sherry
Дата:
Сообщение: Re: Speedier count(*)
Следующее
От: Luis Cornide Arce
Дата:
Сообщение: Why is not using the index