Re: In progress INSERT wrecks plans on table

On 2 May 2013 23:19,  <mark.kirkwood@catalyst.net.nz> wrote:
>> On 2 May 2013 01:49, Mark Kirkwood <mark.kirkwood@catalyst.net.nz> wrote:
>>
>> I think we need a problem statement before we attempt a solution,
>> which is what Tom is alluding to.
>>
>
> Actually no - I think Tom (quite correctly) was saying that the patch was
> not a viable solution. With which I agree.
>
> I believe the title of this thread is the problem statement.
>
>> ISTM that you've got a case where the plan is very sensitive to a
>> table load. Which is a pretty common situation and one that can be
>> solved in various ways. I don't see much that Postgres can do because
>> it can't know ahead of time you're about to load rows. We could
>> imagine an optimizer that set thresholds on plans that caused the
>> whole plan to be recalculated half way thru a run, but that would be a
>> lot of work to design and implement and even harder to test. Having
>> static plans at least allows us to discuss what it does after the fact
>> with some ease.
>>
>> The plan is set using stats that are set when there are very few
>> non-NULL rows, and those increase massively on load. The way to cope
>> is to run the ANALYZE immediately after the load and then don't allow
>> auto-ANALYZE to reset them later.
>
> No. We do run analyze immediately after the load. The surprise was that
> this was not sufficient - the (small) amount of time where non optimal
> plans were being used due to the in progress row activity was enough to
> cripple the system - that is the problem. The analysis of why not led to
> the test case included in the original email. And sure it is deliberately
> crafted to display the issue, and is therefore open to criticism for being
> artificial. However it was purely meant to make it easy to see what I was
> talking about.

I had another look at this and see I that I read the second explain incorrectly.

The amount of data examined and returned is identical in both plans.
The only difference is the number of in-progress rows seen by the
second query. Looking at the numbers some more, it looks like 6000
in-progress rows are examined in addition to the data. It might be
worth an EXPLAIN patch to put instrumentation in to show that, but its
not that interesting.

It would be useful to force the indexscan into a bitmapscan to check
that the cost isn't attributable to the plan but to other overheads.

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.

(3) to make the check on TransactionIdIsInProgress() into a heuristic,
since we don't *need* to check that, so if we keep checking the same
xid repeatedly we can reduce the number of checks or avoid xids that
seem to be long running. That's slightly more coding than my quick
hack here but seems worth it.

I think we need both (1) and (3) but the attached patch does just (1).

This is a similar optimisation to the one I introduced for
TransactionIdIsKnownCompleted(), except this applies to repeated
checking of as yet-incomplete xids, and to bulk concurrent
transactions.

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