Re: In progress INSERT wrecks plans on table

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?

Did you have some simple performance test script for this?

- Heikki