Message-ID: <4848098A.5070500@drivefaster.net>
Date: Thu, 05 Jun 2008 09:43:06 -0600
From: Dan Harris <fbsd@drivefaster.net>
User-Agent: Thunderbird 2.0.0.14 (Macintosh/20080421)
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Re: query performance question
References: <4844F95B.4040009@m4n.nl>
	<31028.217.77.161.17.1212483874.squirrel@sq.gransy.com>
In-Reply-To: <31028.217.77.161.17.1212483874.squirrel@sq.gransy.com>
Content-Type: text/plain; charset=ISO-8859-2; format=flowed
Content-Transfer-Encoding: 7bit

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