Re: In progress INSERT wrecks plans on table

On 16 June 2013 16:04, Heikki Linnakangas <hlinnakangas(at)vmware(dot)com> wrote:
> On 03.05.2013 15:41, Simon Riggs wrote:
>>
>> What appears to be happening is we're spending a lot of time in
>> TransactionIdIsInProgress() so we can set hints and then when we find
>> it is still in progress we then spend more time in XidIsInSnapshot()
>> while we check that it is still invisible to us. Even if the
>> transaction we see repeatedly ends, we will still pay the cost in
>> XidIsInSnapshot repeatedly as we execute.
>>
>> Given that code path, I would expect it to suck worse on a live system
>> with many sessions, and even worse with many subtransactions.
>>
>> (1) A proposed fix is attached, but its only a partial one and barely
>> tested.
>>
>> Deeper fixes might be
>>
>> (2) to sort the xid array if we call XidIsInSnapshot too many times
>> in a transaction. I don't think that is worth it, because a long
>> running snapshot may be examined many times, but is unlikely to see
>> multiple in-progress xids repeatedly. Whereas your case seems
>> reasonably common.
>
>
> Yeah, sorting would be a waste of time most of the time.
>
> Instead of adding a new cache field, how about just swapping the matched XID
> to the beginning of the array?

Do you think that is significantly different from what I've done?

> Did you have some simple performance test script for this?

Files attached to set up and tear down the test. Needs
max_prepared_transactions = 100

--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services