Hello Matthew, hello Tom,
thanks for your reply.
...and yes, the hint with the newly created index solved the problem.
kind regards...GERD...
Tom Lane wrote:
> =?ISO-8859-2?Q?Gerd_K=F6nig?= <koenig@transporeon.com> writes:
>> I'm quite sure that the difference from 94ms (explain of the delete statement)
>> to 24s (duration in the trigger) is not only due to some overhead in trigger
>> handling...but I've no idea what else we can check..?!?
>
> There are two possible explanations for the time difference:
>
> 1. The second time around, the relevant rows were already in cache.
>
> 2. You might not actually be testing the same plan. The query that's
> being executed by the trigger function is parameterized. The manual
> equivalent would look about like this:
>
> prepare foo(int,int,text) as
> DELETE FROM "NotReceivedTransport" WHERE "SId" =
> $1 AND "CId" = $2 AND "ShipperTransportNumber" = $3;
>
> explain analyze execute foo(11479,11479,'100432');
>
> (Note that I'm guessing as to the parameter data types.)
>
> It seems possible that without knowledge of the exact Cid value being
> searched for, the planner would choose not to use the index on that
> column. As Matthew already noted, this index is pretty marginal for
> this query anyway, and the planner might well only want to use it for
> less-common Cid values.
>
> I agree with Matthew's solution --- an index better adapted to this
> query will probably be worth its maintenance overhead. But if you
> want to understand the behavior you were seeing in trying to
> investigate, I think it's one of the two issues above.
>
> regards, tom lane
>
--
/===============================\
| Gerd König
| - Infrastruktur -
|
| TRANSPOREON GmbH
| Pfarrer-Weiss-Weg 12
| DE - 89077 Ulm
|
| Tel: +49 [0]731 16906 16
| Fax: +49 [0]731 16906 99
| Web: www.transporeon.com
|
\===============================/
Bleiben Sie auf dem Laufenden.
Jetzt den Transporeon Newsletter abonnieren!
http://www.transporeon.com/unternehmen_newsletter.shtml
TRANSPOREON GmbH, Amtsgericht Ulm, HRB 722056
Geschäftsf.: Peter Förster, Roland Hötzl, Marc-Oliver Simon