From: Jim C. Nasby <jim@nasby.net>
To: Josh Berkus <josh@agliodbs.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Kevin Brown <kevin@sysexperts.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: [SQL] Yet Another (Simple) Case of Index not used
Date: Tue, 22 Apr 2003 03:23:39 -0500
Message-ID: <20030422032339.A37876@flake.decibel.org> (raw)
In-Reply-To: <200304191203.18634.josh@agliodbs.com>
References: <EKEBJNAJDPKJBDFGJNIJAENMDBAA.denis@next2me.com>
<20030419130146.GI1847@filer>
<21955.1050767928@sss.pgh.pa.us>
<200304191203.18634.josh@agliodbs.com>
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?"
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: jim@nasby.net, josh@agliodbs.com, tgl@sss.pgh.pa.us, kevin@sysexperts.com, pgsql-performance@postgresql.org
Subject: Re: [SQL] Yet Another (Simple) Case of Index not used
In-Reply-To: <20030422032339.A37876@flake.decibel.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox