Content-Type: text/plain;
  charset="iso-8859-1"
From: Josh Berkus <josh@agliodbs.com>
Organization: Aglio Database Solutions
To: Tom Lane <tgl@sss.pgh.pa.us>, Kevin Brown <kevin@sysexperts.com>
Subject: Re: [SQL] Yet Another (Simple) Case of Index not used
Date: Sat, 19 Apr 2003 12:03:18 -0700
User-Agent: KMail/1.4.3
Cc: pgsql-performance@postgresql.org
References: <EKEBJNAJDPKJBDFGJNIJAENMDBAA.denis@next2me.com>
	<20030419130146.GI1847@filer> <21955.1050767928@sss.pgh.pa.us>
In-Reply-To: <21955.1050767928@sss.pgh.pa.us>
MIME-Version: 1.0
Message-Id: <200304191203.18634.josh@agliodbs.com>
Content-Transfer-Encoding: quoted-printable

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=
=20
information in PostgreSQL.   The statistics table itself becomes a=20
significant souce of delay, since if a table_A gets 10,000 rows updated tha=
n=20
table_count_A must necessarily be updated 10,000 times ... creating a lot o=
f=20
dead tuples and severely attenuating the table on disk until the next vacuu=
m=20
... resulting in Update #10,000 to table_count_A taking 100+ times as long =
as=20
Update #1 does, due to the required random seek time on disk.

I can personally think of two ways around this:

In MySQL: store table_count_A as a non-MVCC table or global variable.=20=20
Drawback: the count would not be accurate, as you would see changes due to=
=20
incomplete transactions and eventually the count would be knocked off=20
completely by an overload of multi-user activity.  However, this does fit=
=20
with MySQL's design philosophy of "Speed over accuracy", so I suspect that=
=20
that's what they're doing.

In PostgreSQL:
a) Put table_count_A on superfast media like a RAM card so that random seek=
s=20
after 10,000 updates do not become a significant delay;
b) create an asynchronious table aggregates collector which would collect=
=20
programmed statistics (like count(*) from table A) much in the same way tha=
t=20
the planner statistics collector does.  This would have the disadvantage of=
=20
on being up to date when the database is idle, but the advantage of not=20
imposing any significant overhead on Updates.
	(Incidentally, I proposed this to one of my clients who complained about=
=20
Postgres' slow aggregate performance, but they declined to fund the effort)

--=20
Josh Berkus
Aglio Database Solutions
San Francisco