Обсуждение: Triggers and speed - 7.4.5 / 8.0.3

We have both 7.4.5 and 8.0.3 running and seem to have some speed problems with triggers/procedures in both.
We are using PL/pgSQL for the procedures. Is this a known issue that is cured in later releases?

The speed problem appears to be more related to the fact that an action has a trigger, rather than the action that the trigger spawns. IE the triggered procedure code seems to execute ok in stand-alone, but seems to take time load/execute. Is there a way of compiling the procedure(s) or something similar?

Regards, Steve Tucknott ReTSol Ltd DDI: 01903 828769 Mobile: 0773 671 5772

On 1/9/06 8:12 AM, "Steve Tucknott" <steve@retsol.co.uk> wrote:

> We have both 7.4.5 and 8.0.3 running and seem to have some speed
> problems with triggers/procedures in both.
> We are using PL/pgSQL for the procedures. Is this a known issue that is
> cured in later releases?
>
> The speed problem appears to be more related to the fact that an action
> has a trigger, rather than the action that the trigger spawns. IE the
> triggered procedure code seems to execute ok in stand-alone, but seems
> to take time load/execute. Is there a way of compiling the procedure(s)
> or something similar?

Steve,

I'm not an expert, but I think that posting your table structure and trigger
code will probably be useful for those who want to help.  Also, what makes
you say that trigger code speed is a problem?  Yes, triggers are going to
add overhead to table actions--a table without a trigger on it will behave
differently that one that does have a trigger, I think.

Sean

This was me 'mis analysing' and not reading the explains properly.
The triggered procedures were using the indexes correctly, BUT the indexes themselves were not 'quite right' - the index in error was defined with the most inclusive column first. Changing the index so that the first field was the most exclusive has increased the speed to what was expected ....
Sorry to have wasted anyone's time (thanks Sean)


On Mon, 2006-01-09 at 08:13 -0500, Sean Davis wrote:

On 1/9/06 8:12 AM, "Steve Tucknott" <steve@retsol.co.uk> wrote:

> We have both 7.4.5 and 8.0.3 running and seem to have some speed
> problems with triggers/procedures in both.
> We are using PL/pgSQL for the procedures. Is this a known issue that is
> cured in later releases?
> 
> The speed problem appears to be more related to the fact that an action
> has a trigger, rather than the action that the trigger spawns. IE the
> triggered procedure code seems to execute ok in stand-alone, but seems
> to take time load/execute. Is there a way of compiling the procedure(s)
> or something similar?

Steve, 

I'm not an expert, but I think that posting your table structure and trigger
code will probably be useful for those who want to help.  Also, what makes
you say that trigger code speed is a problem?  Yes, triggers are going to
add overhead to table actions--a table without a trigger on it will behave
differently that one that does have a trigger, I think.

Sean



---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not
       match

Regards, Steve Tucknott ReTSol Ltd DDI: 01903 828769 Mobile: 0773 671 5772