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

From: Josh Berkus <josh(at)agliodbs(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Kevin Brown <kevin(at)sysexperts(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: [SQL] Yet Another (Simple) Case of Index not used
Date: 2003-04-19 19:03:18
Message-ID: 200304191203.18634.josh@agliodbs.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-performance pgsql-sql

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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Joe Conway 2003-04-19 19:50:01 Re: stddev returns 0 when there is one row
Previous Message Oliver Elphick 2003-04-19 18:59:51 Re: Please some help on a join question with sum

Browse pgsql-performance by date

  From Date Subject
Next Message Tom Lane 2003-04-19 20:26:49 Re: [SQL] Yet Another (Simple) Case of Index not used
Previous Message Stephan Szabo 2003-04-19 19:03:02 Re: [PERFORM] Foreign key performance

Browse pgsql-sql by date

  From Date Subject
Next Message Tom Lane 2003-04-19 20:26:49 Re: [SQL] Yet Another (Simple) Case of Index not used
Previous Message Rajesh Kumar Mallah 2003-04-19 16:42:56 Re: replicable problem with PL/Perl