rules *very* slow?

From: Neil Conway <nconway(at)klamath(dot)dyndns(dot)org>
To: PostgreSQL General <pgsql-general(at)postgresql(dot)org>
Subject: rules *very* slow?
Date: 2000-10-18 20:54:06
Message-ID: 20001018165406.A1032@klamath.dyndns.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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(at)home(dot)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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Philip Hallstrom 2000-10-18 21:46:36 Any risk in increasing BLCKSZ to get larger tuples?
Previous Message Tom Walta 2000-10-18 20:14:31 plpgsql - cache lookup error 18977