Re: Planner performance extremely affected by an hanging transaction (20-30 times)?

On 09/24/2013 08:01 AM, jesper(at)krogh(dot)cc wrote:
> This stuff is a 9.2 feature right? What was the original problem to be
> adressed?

Earlier, actually. 9.1? 9.0?

The problem addressed was that, for tables with a "progressive" value
like a sequence or a timestamp, the planner tended to estimate 1 row any
time the user queried the 10,000 most recent rows due to the stats being
out-of-date. This resulted in some colossally bad query plans for a
very common situation.

So there's no question that the current behavior is an improvement,
since it affects *only* users who have left an idle transaction open for
long periods of time, something you're not supposed to do anyway. Not
that we shouldn't fix it (and backport the fix), but we don't want to
regress to the prior planner behavior.

However, a solution is not readily obvious:

On 09/24/2013 03:35 AM, Tom Lane wrote:
> Kevin Grittner <kgrittn(at)ymail(dot)com> writes:
>> > Are we talking about the probe for the end (or beginning) of an
>> > index? If so, should we even care about visibility of the row
>> > related to the most extreme index entry? Should we even go to the
>> > heap during the plan phase?
> Consider the case where some transaction inserted a wildly out-of-range
> value, then rolled back. If we don't check validity of the heap row,
> we'd be using that silly endpoint value for planning purposes ---
> indefinitely. That's not an improvement over the situation that the
> probe is meant to fix.

Agreed. And I'll also attest that the patch did fix a chronic bad
planner issue.

On 09/20/2013 03:01 PM, Jeff Janes wrote:> 3) Even worse, asking if a
given transaction has finished yet can be a
> serious point of system-wide contention, because it takes the
> ProcArrayLock, once per row which needs to be checked. So you have 20
> processes all fighting over the ProcArrayLock, each doing so 1000
times per
> query.

Why do we need a procarraylock for this? Seems like the solution would
be not to take a lock at all; the information on transaction commit is
in the clog, after all.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com