To: "Tino Wildenhain" <tino@wildenhain.de>, "Gavin Sherry" <swm@alcove.com.au>
Cc: "Mark Cotner" <mcotner@yahoo.com>,
	"Dan Harris" <fbsd@drivefaster.net>, pgsql-performance@postgresql.org
Subject: Re: Speedier count(*)
References: <BF204EF7.D9F6%mcotner@yahoo.com>
	<1123745049.27613.1.camel@Andrea.peacock.de>
	<Pine.LNX.4.58.0508112035580.1719@linuxworld.com.au>
	<1123757536.27613.10.camel@Andrea.peacock.de>
Message-ID: <op.svceokgbth1vuj@localhost>
Date: Thu, 11 Aug 2005 14:08:34 +0200
From: PFC <lists@boutiquenumerique.com>
Organization: =?iso-8859-15?Q?La_Boutique_Num=E9rique?=
Content-Type: text/plain; format=flowed; delsp=yes; charset=iso-8859-15
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
In-Reply-To: <1123757536.27613.10.camel@Andrea.peacock.de>
User-Agent: Opera M2/8.0 (Linux, build 1095)


> 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.

	Niet.

	If your trigger does UPDATE counts_cache SET cached_count =  
cached_count+N WHERE ...
	Then all locking is taken care of by Postgres.
	Of course if you use 2 queries then you have locking issues.

	However the UPDATE counts_cache has a problem, ie. it locks this row FOR  
UPDATE for the whole transaction, and all transactions which want to  
update the same row must wait to see if the update commits or rollbacks,  
so if you have one count cache row for the whole table you get MySQL style  
scalability...

	To preserve scalability you could, instead of UPDATE, INSERT the delta of  
rows inserted/deleted in a table (which has no concurrencies issues) and  
compute the current count with the sum() of the deltas, then with a cron,  
consolidate the deltas and update the count_cache table so that the deltas  
table stays very small.