Re: rules *very* slow?

After my last email, I added the rest of the rule actions. So the
relevant part of the schema now looks like this:

CREATE RULE update_msg_stats
    AS ON INSERT TO messages DO (
        UPDATE users SET num_posts = num_posts + 1
            WHERE users.id = new.poster;
        UPDATE threads SET num_posts = num_posts + 1
            WHERE threads.id = new.thread;
        UPDATE forums SET num_posts = forums.num_posts + 1 FROM threads t
            WHERE t.id = new.thread AND forums.id = t.forum;

        UPDATE threads SET last_post = timestamp('now')
            WHERE threads.id = new.thread;
        UPDATE forums SET last_post = timestamp('now') FROM threads t
            WHERE t.id = new.thread AND forums.id = t.forum;
    );

(I'll just provide the definition of the rule -- if you need all the
tables it effects, just tell me.)

I reran the 3000-inserts test case with the more complex rule in place
- after 1 hour, and canceled it. It had inserted about 2000 of the
3000 rows (judging by the value of messages_id_seq). As before,
inserting these rows took < 10 seconds without the rules.

On Wed, Oct 18, 2000 at 10:50:43PM -0400, Tom Lane wrote:
> > 1) Are rules really this slow?
>
> Seems like they shouldn't be.  Could you recompile with PROFILE=-pg
> and get a gprof profile on the 3000-inserts test case?

I tried rebuilding Postgres, but 'gmake' fails with:

gmake[3]: Leaving directory `/usr/home/nconway/pgsql-20000924/src/backend/utils'
gcc -I../../src/include  -O2 -m486 -pipe -Wall -Wmissing-prototypes -Wmissing-declarations -pg -o postgres
access/SUBSYS.obootstrap/SUBSYS.o catalog/SUBSYS.o parser/SUBSYS.o commands/SUBSYS.o executor/SUBSYS.o lib/SUBSYS.o
libpq/SUBSYS.omain/SUBSYS.o nodes/SUBSYS.o optimizer/SUBSYS.o port/SUBSYS.o postmaster/SUBSYS.o regex/SUBSYS.o
rewrite/SUBSYS.ostorage/SUBSYS.o tcop/SUBSYS.o utils/SUBSYS.o -lz -lcrypt -lcompat -lln -lm -lutil -lreadline -ltermcap
-lncurses -export-dynamic -pg 
/usr/libexec/elf/ld: cannot find -lc_p
gmake[2]: *** [postgres] Error 1
gmake[2]: Leaving directory `/usr/home/nconway/pgsql-20000924/src/backend'
gmake[1]: *** [all] Error 2
gmake[1]: Leaving directory `/usr/home/nconway/pgsql-20000924/src'
gmake: *** [all] Error 2

After it has been compiling for a while. I'm running FreeBSD 4.1-STABLE on
x86 with gcc 2.95.2 . I tried compiling 3 nightly snapshots (Oct 18, Oct 19,
and Sept. 24), and none of them worked. They all compile properly without
profiling.

--
Neil Conway <neilconway@home.com>
Get my GnuPG key from: http://klamath.dyndns.org/mykey.asc
Encrypted mail welcomed

Four stages of acceptance:
    i) this is worthless nonsense;
    ii) this is an interesting, but perverse, point of view;
    iii) this is true, but quite unimportant;
    iv) I always said so.
        -- J. B. S. Haldane in Journal of Genetics 58:464 (1963).