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

On 09/24/2013 08:01 AM, jesper@krogh.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/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.