Postgres Pro
Downloads
Home   >   mailing lists

Thread: Slow trigger on identical DB but different machine

Slow trigger on identical DB but different machine

From
Etienne Labuschagne
Date:
Hi all,

I have the exact same DB on two machines.  One is a RedHat box with a
SAN for storage.  The other is a Windows XP laptop.

The RedHat box outperforms the laptop with everything (as expected)
except with a DELETE FROM query (quite unexpected).

After doing a EXPLAIN ANALYZE, it seems that a trigger (on delete)
function is causing the delay (the trigger is implemented in pgSQL).
I'm not sure how to analyze the parts "inside" the trigger to see which
operation is causing the problem.

The strange thing is that both databases are EXACTLY the same with
EXACTLY the same SQL query executed (I restore both DBs from the same
backup file to ensure that everything is the same - data and objects).
Why wouldn't the trigger cause the same problem on the Windows XP
laptop?  The RedHat machine takes about 100x longer to do the operation
than the laptop!

How can I log/analyze the operations inside a trigger and are there any
suggestions as to why the trigger would be so slow?

Any help would be greatly appreciated.

Etienne

Re: Slow trigger on identical DB but different machine

From
Tom Lane
Date:
Etienne Labuschagne <elabuschagne@agileworks.net> writes:
> The strange thing is that both databases are EXACTLY the same with
> EXACTLY the same SQL query executed (I restore both DBs from the same
> backup file to ensure that everything is the same - data and objects).

Have you vacuumed and analyzed in both DBs?  This sounds like either out-
of-date stats or different configuration settings in the two.

            regards, tom lane

Re: Slow trigger on identical DB but different machine

From
Etienne Labuschagne
Date:
Hi Tom,

That was one of the first things I tried, to no avail . . .

Regards
Etienne

Tom Lane wrote:
> Etienne Labuschagne <elabuschagne@agileworks.net> writes:
>
>> The strange thing is that both databases are EXACTLY the same with
>> EXACTLY the same SQL query executed (I restore both DBs from the same
>> backup file to ensure that everything is the same - data and objects).
>>
>
> Have you vacuumed and analyzed in both DBs?  This sounds like either out-
> of-date stats or different configuration settings in the two.
>
>             regards, tom lane
>
>

Re: Slow trigger on identical DB but different machine

From
Etienne Labuschagne
Date:
Tom Lane wrote:
> Etienne Labuschagne <elabuschagne@agileworks.net> writes:
>
>> The strange thing is that both databases are EXACTLY the same with
>> EXACTLY the same SQL query executed (I restore both DBs from the same
>> backup file to ensure that everything is the same - data and objects).
>>
>
> Have you vacuumed and analyzed in both DBs?  This sounds like either out-
> of-date stats or different configuration settings in the two.
>
>             regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faq
>

Yes, I have vacuum-analyzed the DB to no avail . . .