rules *very* slow?
От | Neil Conway |
---|---|
Тема | rules *very* slow? |
Дата | |
Msg-id | 20001018165406.A1032@klamath.dyndns.org обсуждение исходный текст |
Ответы |
Re: rules *very* slow?
|
Список | 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@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
Вложения
В списке pgsql-general по дате отправления: