Why is RELEASE SAVEPOINT sometimes slow?

From: Evan Martin <postgresql(at)realityexists(dot)net>
To: pgsql-general(at)postgresql(dot)org
Subject: Why is RELEASE SAVEPOINT sometimes slow?
Date: 2012-05-11 10:16:59
Message-ID: 4FACE71B.1090109@realityexists.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I'm running a bulk import application against PostgreSQL 9.1.3, which
has several stages and each stage follows the same general pattern:

BEGIN TRANSACTION
DELETE (many rows)

CREATE SAVEPOINT
INSERT
...
RELEASE SAVEPOINT

CREATE SAVEPOINT
INSERT
INSERT
... half an hour of inserts later ...
RELEASE SAVEPOINT

COMMIT TRANSACTION

I find that for one particular stage of the import the RELEASE SAVEPOINT
command consistently takes about 6 minutes, while for the rest of them
it's very quick. COMMIT TRANSACTION is always very quick.

At first I thought the discrepancy may be because that particular stage
has many INSERT statements inside one savepoint, while other stages
create many savepoints with a small amount of work in each. However, if
I take out the savepoints entirely then the COMMIT TRANSACTION statement
for that stage takes 6 minutes, while for the rest of them it's still
very quick.

Could anyone explain what may be happening here? What is PostgreSQL
doing when I call RELEASE SAVEPOINT that it seems to otherwise do in
COMMIT TRANSACTION?

Thanks,

Evan

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Simon Riggs 2012-05-11 10:26:14 Re: Why is RELEASE SAVEPOINT sometimes slow?
Previous Message Albe Laurenz 2012-05-11 08:53:25 Re: Sequence scan if "OR Condition" in where statement