Date: Tue, 22 Apr 2003 03:23:39 -0500
From: "Jim C. Nasby" <jim@nasby.net>
To: Josh Berkus <josh@agliodbs.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, Kevin Brown <kevin@sysexperts.com>,
	pgsql-performance@postgresql.org
Subject: Re: [SQL] Yet Another (Simple) Case of Index not used
Message-ID: <20030422032339.A37876@flake.decibel.org>
Reply-To: jim@nasby.net
References: <EKEBJNAJDPKJBDFGJNIJAENMDBAA.denis@next2me.com>
	<20030419130146.GI1847@filer> <21955.1050767928@sss.pgh.pa.us>
	<200304191203.18634.josh@agliodbs.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
User-Agent: Mutt/1.2.5.1i
In-Reply-To: <200304191203.18634.josh@agliodbs.com>;
	from josh@agliodbs.com on Sat, Apr 19, 2003 at 12:03:18PM -0700

On Sat, Apr 19, 2003 at 12:03:18PM -0700, Josh Berkus wrote:
> Kevin, Tom:
> 
> > (The cheapness can be disputed as well, since it creates a single point
> > of contention for all inserts and deletes on the table.  But that's a
> > different topic.)
> 
> Actually, this was the problem with the trigger method of maintaining COUNT 
> information in PostgreSQL.   The statistics table itself becomes a 
> significant souce of delay, since if a table_A gets 10,000 rows updated than 
> table_count_A must necessarily be updated 10,000 times ... creating a lot of 
> dead tuples and severely attenuating the table on disk until the next vacuum 
> ... resulting in Update #10,000 to table_count_A taking 100+ times as long as 
> Update #1 does, due to the required random seek time on disk.
 
Once statement level triggers are implimented, the performance would
probably be fine, assuming your update was a single statement.
-- 
Jim C. Nasby (aka Decibel!)                    jim@nasby.net
Member: Triangle Fraternity, Sports Car Club of America
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"