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

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