From: | Steve Tucknott <steve(at)retsol(dot)co(dot)uk> |
---|---|
To: | PostGreSQL <pgsql-novice(at)postgresql(dot)org> |
Subject: | Re: Triggers and speed - 7.4.5 / 8.0.3 |
Date: | 2006-01-09 16:38:37 |
Message-ID: | 1136824717.2797.122.camel@retsol1 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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(at)retsol(dot)co(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Joao Miguel Ferreira | 2006-01-09 17:37:19 | how to list all tables in a database |
Previous Message | Frank Bax | 2006-01-09 16:26:50 | Re: What does 'schema' mean ? |