From: | Evan Martin <postgresql(at)realityexists(dot)net> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Why is RELEASE SAVEPOINT sometimes slow? |
Date: | 2012-05-13 13:59:04 |
Message-ID: | 4FAFBE28.3090203@realityexists.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
I dug a bit further into this and found that the code was also creating
and releasing a savepoint for each insert (and there were over 10,000 of
them). When I removed that the delay at the end disappeared.
Regards,
Evan
On 11/05/2012 8:26 PM, Simon Riggs wrote:
> On 11 May 2012 11:16, Evan Martin<postgresql(at)realityexists(dot)net> wrote:
>> 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?
> Sounds interesting.
>
> Please can you produce a test case that demonstrates this, then post
> the SQL file and an output of a run that shows the negative timing?
>
> Thanks
>
From | Date | Subject | |
---|---|---|---|
Next Message | Pham Ngoc Hai | 2012-05-14 04:12:48 | Postgresql segmentation fault at slot_deform_tuple |
Previous Message | Jasen Betts | 2012-05-13 10:39:36 | Re: Uppercase to lowercase trigger? |