much difference in speed between
the trigger and the rule implementation.
</para>
<para>
With the next delete we want to get rid of all the 2000 computers
where the <structfield>hostname</structfield> starts with
<literal>old</literal>. There are two possible commands to do that. One
is:
<programlisting>
DELETE FROM computer WHERE hostname >= 'old'
AND hostname < 'ole'
</programlisting>
The command added by the rule will be:
<programlisting>
DELETE FROM software WHERE computer.hostname >= 'old' AND computer.hostname < 'ole'
AND software.hostname = computer.hostname;
</programlisting>
with the plan
<literallayout class="monospaced">
Hash Join
-> Seq Scan on software
-> Hash
-> Index Scan using comp_hostidx on computer
</literallayout>
The other possible command is:
<programlisting>
DELETE FROM computer WHERE hostname ~ '^old';
</programlisting>
which results in the following executing plan for the command
added by the rule:
<literallayout class="monospaced">
Nestloop
-> Index Scan using comp_hostidx on computer
-> Index Scan using soft_hostidx on software
</literallayout>
This shows, that the planner does not realize that the
qualification for <structfield>hostname</structfield> in
<literal>computer</literal> could also be used for an index scan on
<literal>software</literal> when there are multiple qualification
expressions combined with <literal>AND</literal>, which is what it does
in the regular-expression version of the command. The trigger will
get invoked once for each of the 2000 old computers that have to be
deleted, and that will result in one index scan over
<literal>computer</literal> and 2000 index scans over
<literal>software</literal>. The rule implementation will do it with two
commands that use indexes. And it depends on the overall size of
the table <literal>software</literal> whether the rule will still be faster in the
sequential scan situation. 2000 command executions from the trigger over the SPI
manager take some time, even if all the index blocks will soon be in the cache.
</para>
<para>
The last command we look at is:
<programlisting>
DELETE FROM computer WHERE manufacturer = 'bim';
</programlisting>
Again this could result in many rows to be deleted from
<literal>computer</literal>. So the trigger will again run many commands
through the executor. The command generated by the rule will be:
<programlisting>
DELETE FROM software WHERE computer.manufacturer = 'bim'
AND software.hostname = computer.hostname;
</programlisting>
The plan for that command will again be the nested loop over two
index scans, only using a different index on <literal>computer</literal>:
<programlisting>
Nestloop
-> Index Scan using comp_manufidx on computer
-> Index Scan using soft_hostidx on software
</programlisting>
In any of these cases, the extra commands from the rule system
will be more or less independent from the number of affected rows
in a command.
</para>
<para>
The summary is, rules will only be significantly slower than
triggers if their actions result in large and badly qualified
joins, a situation where the planner fails.
</para>
</sect1>
</chapter>