From: Josh Berkus <josh@agliodbs.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
To: Kevin Brown <kevin@sysexperts.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: [SQL] Yet Another (Simple) Case of Index not used
Date: Sat, 19 Apr 2003 12:03:18 -0700
Message-ID: <200304191203.18634.josh@agliodbs.com> (raw)
In-Reply-To: <21955.1050767928@sss.pgh.pa.us>
References: <EKEBJNAJDPKJBDFGJNIJAENMDBAA.denis@next2me.com>
<20030419130146.GI1847@filer>
<21955.1050767928@sss.pgh.pa.us>
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.
I can personally think of two ways around this:
In MySQL: store table_count_A as a non-MVCC table or global variable.
Drawback: the count would not be accurate, as you would see changes due to
incomplete transactions and eventually the count would be knocked off
completely by an overload of multi-user activity. However, this does fit
with MySQL's design philosophy of "Speed over accuracy", so I suspect that
that's what they're doing.
In PostgreSQL:
a) Put table_count_A on superfast media like a RAM card so that random seeks
after 10,000 updates do not become a significant delay;
b) create an asynchronious table aggregates collector which would collect
programmed statistics (like count(*) from table A) much in the same way that
the planner statistics collector does. This would have the disadvantage of
on being up to date when the database is idle, but the advantage of not
imposing any significant overhead on Updates.
(Incidentally, I proposed this to one of my clients who complained about
Postgres' slow aggregate performance, but they declined to fund the effort)
--
Josh Berkus
Aglio Database Solutions
San Francisco
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: 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: <200304191203.18634.josh@agliodbs.com>
* 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