In testing my database, I've encountered what appears to be a
concerning performance problem using a rule (ON INSERT). Here's
the situation:
Every time a row is inserted into a table (called 'messages'), I want
to increment a counter in a different table (called 'users'). The
best way I could think up to implement this was to use a Postgres
rule which incremented the appropriate data when an INSERT is
performed.
The database schema (lots of extraneous stuff removed):
CREATE TABLE users (
id serial PRIMARY KEY,
num_posts int4 DEFAULT 0,
);
CREATE TABLE messages (
id serial,
poster int4 NOT NULL REFERENCES users MATCH FULL,
);
CREATE RULE update_posts_total
AS ON insert TO messages DO
UPDATE users SET num_posts = num_posts + 1 WHERE users.id = new.poster;
To test performance, I wrote a simple Perl script which inserts
3000 rows into the 'messages' table (inside a single transaction).
With the rule:
Adding 3000 messages.
364 wallclock secs ( 1.04 usr + 0.22 sys = 1.26 CPU)
Without the rule:
Adding 3000 messages.
7 wallclock secs ( 0.83 usr + 0.19 sys = 1.02 CPU)
While the test is running, postgres was using about 98% of the
available CPU time (perl is using the rest).
I would expect the rule it cause a bit of overhead (maybe
taking twice or three times as long as w/o the rule), but
it's taking ~52x longer.
I've tried creating an index on messages.poster, but it has
no effect (performance is the same). I guesses that Postgres
was ignoring the index so I disabled seqscan, but that had
no effect.
A couple questions:
1) Are rules really this slow?
2) Have I done something wrong? Is there a more efficient way to
implement this?
3) Will this translate into an equivelant real-world performance hit?
I wrote the Perl script in a few minutes so it's not particularly
accurate. In 'production', my application will only insert 1 row
per transaction, with perhaps 1 or 2 inserts per second (the
majority of queries will probably be SELECTs). What exactly is
causing the benchmark to be *so* slow, and will it effect my
application to the same degree?
4) The current rule only performs 1 action, but when this app
is finished this rule will probably be performing 3 or more
UPDATEs for every INSERT. Will this bring correspondingly poor
performance (i.e. 364x3 seconds per 3000 inserts), or does
the performance problem lie somewhere else?
I'm running Postgres 7.1-devel on FreeBSD 4.1-STABLE. The tests
were run on my development box - a P2 350 with 128 MB of RAM.
Feel free to ask me for more info.
Thanks in advance,
Neil
--
Neil Conway <neilconway@home.com>
Get my GnuPG key from: http://klamath.dyndns.org/mykey.asc
Encrypted mail welcomed
Don't worry about people stealing your ideas. If your ideas are any
good, you'll have to ram them down people's throats.
-- Howard Aiken