Re: long running insert statement

On Thu, 1 Oct 2009, Gerd König wrote:
> Trigger NotReceivedTransport_Delete: time=24658.394 calls=1

Yeah, it's pretty obvious this is the problem.

> explain analyze DELETE FROM "NotReceivedTransport" WHERE
> "SId" = 11479 AND "CId" = 11479 AND
> "ShipperTransportNumber" = '100432';
> QUERY PLAN
> ----------------------------------------------------------------------------------
> Bitmap Heap Scan on "NotReceivedTransport" (cost=20.35..3939.16 rows=1
> width=6) (actual time=94.625..94.625 rows=0 loops=1)
> Recheck Cond: ("CId" = 11479)
> Filter: (("SId" = 11479) AND (("ShipperTransportNumber")::text
> = '100432'::text))
> -> Bitmap Index Scan on notreceivedtransport_index_cid
> (cost=0.00..20.35 rows=1060 width=0) (actual time=2.144..2.144 rows=6347 loops=1)
> Index Cond: ("CarrierCustomerId" = 11479)
> Total runtime: 94.874 ms
> (6 rows)

Maybe it's cached this time.

In any case, you have a bitmap index scan which is fetching 6347 rows and
then filtering that down to zero. Assuming one seek per row, that means
6347 disc seeks, which is about 3.8 ms per seek - better than you would
expect from a disc. This means that the time taken is quite reasonable for
what you are asking it to do.

To fix this, I suggest creating an index on NotReceivedTransport(SId, CId,
ShipperTransportNumber). Then, the index will be able to immediately see
that there are no rows to delete.

Matthew

--
"We have always been quite clear that Win95 and Win98 are not the systems to
use if you are in a hostile security environment." "We absolutely do recognize
that the Internet is a hostile environment." Paul Leach <paulle(at)microsoft(dot)com>