Re: rules *very* slow?

Tom Lane wrote:
> 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.

Cannot recreate such a big runtime difference here. With the
given example, the test with the rule runs ~4 times compared
to without the rule (26 secs vs. 8 secs using a Tcl script as
driver).

And that is IMHO not too bad. Having the rule in place means
that the rewriter has to create one UPDATE per INSERT, which
must be executed. This UPDATE then invokes a referential
integrity trigger to check whether the KEY of the users row
has changed (in which case it'd need to check if there are
references). So there is more overhead than just one more
UPDATE.

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

It shouldn't help here. But it will help in the case of
deleting users to speedup the referential action lookup for
existing messages.

>
> > 1) Are rules really this slow?

Not AFAICS. But to ensure could you please give me more
information? What is the number and average size of rows in
the users table? Are the 3000 messages distributed over all
users or just a few?

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck(at)Yahoo(dot)com #