Home Explore Blog CI



postgresql

32th chunk of `doc/src/sgml/rules.sgml`
00aeeae36ac82fd5473d11bafcca3e77ee758e3409a8c3580000000100000d58
 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 &gt;= 'old'
                       AND hostname &lt;  'ole'
</programlisting>

    The command added by the rule will be:

<programlisting>
DELETE FROM software WHERE computer.hostname &gt;= 'old' AND computer.hostname &lt; 'ole'
                       AND software.hostname = computer.hostname;
</programlisting>

    with the plan

<literallayout class="monospaced">
Hash Join
  -&gt;  Seq Scan on software
  -&gt;  Hash
    -&gt;  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
  -&gt;  Index Scan using comp_hostidx on computer
  -&gt;  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
  -&gt;  Index Scan using comp_manufidx on computer
  -&gt;  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>

Title: Performance Comparison of Triggers and Rules
Summary
This section compares the performance of triggers and rules in PostgreSQL, analyzing various delete operations and query plans, and concludes that rules are generally faster than triggers unless they result in large and poorly qualified joins, where the planner fails to optimize effectively.