Date: Thu, 5 Jun 2008 12:16:39 -0500
From: Kenneth Marshall <ktm@rice.edu>
To: Dan Harris <fbsd@drivefaster.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: query performance question
Message-ID: <20080605171639.GE5624@it.is.rice.edu>
References: <4844F95B.4040009@m4n.nl>
	<31028.217.77.161.17.1212483874.squirrel@sq.gransy.com>
	<4848098A.5070500@drivefaster.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <4848098A.5070500@drivefaster.net>
User-Agent: Mutt/1.5.16 (2007-06-09)

Dan,

Did you try this with 8.3 and its new HOT functionality? 

Ken

On Thu, Jun 05, 2008 at 09:43:06AM -0600, Dan Harris wrote:
> tv@fuzzy.cz wrote:
>>
>> 3) Build a table with totals or maybe subtotals, updated by triggers. This
>> requires serious changes in application as well as in database, but solves
>> issues of 1) and may give you even better results.
>>
>> Tomas
>>
>>   
> I have tried this.  It's not a magic bullet.  We do our billing based on 
> counts from huge tables, so accuracy is important to us.  I tried 
> implementing such a scheme and ended up abandoning it because the summary 
> table became so full of dead tuples during and after large bulk inserts 
> that it slowed down selects on that table to an unacceptable speed.  Even 
> with a VACUUM issued every few hundred inserts, it still bogged down due to 
> the constant churn of the inserts. 
> I ended up moving this count tracking into the application level.  It's 
> messy and only allows a single instance of an insert program due to the 
> localization of the counts in program memory, but it was the only way I 
> found to avoid the penalty of constant table churn on the triggered 
> inserts.
>
> -Dan
>
> -- 
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>