Re: rules *very* slow?

Tom Lane wrote:
> Neil Conway <nconway@klamath.dyndns.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@Yahoo.com #