=?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