Re: rules *very* slow?

Neil Conway <nconway(at)klamath(dot)dyndns(dot)org> writes:
> 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.

Ouch.

> 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.

An index on messages.poster wouldn't help here, AFAICS. The update
generated by the rule should be using an indexscan on the users.id
index (check this by doing an EXPLAIN on one of your insert commands).

> 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?

> 2) Have I done something wrong? Is there a more efficient way to
> implement this?

Actually, I'd have gone for a trigger implemented by a plpgsql function,
so that you only pay the overhead of planning the UPDATE query once not
every time. But I don't think that should explain 52x, either.
As long as you've uncovered a problem, let's see if we can fix it before
you go off in a different direction.

regards, tom lane