Skip site navigation (1) Skip section navigation (2)

Re: [SQL] Yet Another (Simple) Case of Index not used

From: "Jim C(dot) Nasby" <jim(at)nasby(dot)net>
To: Josh Berkus <josh(at)agliodbs(dot)com>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Kevin Brown <kevin(at)sysexperts(dot)com>,pgsql-performance(at)postgresql(dot)org
Subject: Re: [SQL] Yet Another (Simple) Case of Index not used
Date: 2003-04-22 08:23:39
Message-ID: 20030422032339.A37876@flake.decibel.org (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-performancepgsql-sql
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(at)nasby(dot)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?"


In response to

Responses

pgsql-performance by date

Next:From: Jim C. NasbyDate: 2003-04-22 08:30:58
Subject: Re: choosing the right platform
Previous:From: Josh BerkusDate: 2003-04-21 16:14:43
Subject: Re: [SQL] Yet Another (Simple) Case of Index not used

pgsql-sql by date

Next:From: Christopher Kings-LynneDate: 2003-04-22 08:33:27
Subject: Re: [SQL] Yet Another (Simple) Case of Index not used
Previous:From: Stephan SzaboDate: 2003-04-22 03:58:57
Subject: Re: trouble creating trigger

pgsql-general by date

Next:From: Christopher Kings-LynneDate: 2003-04-22 08:33:27
Subject: Re: [SQL] Yet Another (Simple) Case of Index not used
Previous:From: sector119Date: 2003-04-22 07:42:15
Subject: contrib/tablefunc/connectby question

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group