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@xxxxxxxxxxxxxxx> 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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
