On Tue, Sep 24, 2013 at 10:43 AM, Josh Berkus <josh@xxxxxxxxxxxx> wrote:
On 09/24/2013 08:01 AM, jesper@xxxxxxxx wrote:Earlier, actually. 9.1? 9.0?
> This stuff is a 9.2 feature right? What was the original problem to be
> adressed?
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.
Some transaction just take a long time to complete their work. If the first thing it does is insert these poisoned values, then go on to do other intensive work on other tables, it can do some serious damage without being idle.
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:
The mergejoinscansel code is almost pathologically designed to exercise this case (which seems to be what is doing in the original poster) because it systematically probes the highest and lowest values from one table against the other. If they have the same range, that means it will always be testing the upper limit. Perhaps mergejoinscansel could pass a flag to prevent the look-up from happening. My gut feeling is that mergejoin it would not be very sensitive to the progressive value issue, but I can't really back that up. On the other hand, if we could just make getting the actual value faster then everyone would be better off.
On 09/20/2013 03:01 PM, Jeff Janes wrote:> 3) Even worse, asking if a
given transaction has finished yet can be aWhy do we need a procarraylock for this? Seems like the solution would
> 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.
be not to take a lock at all; the information on transaction commit is
in the clog, after all.
My understanding is that you are not allowed to check the clog until after you verify the transaction is no longer in progress, otherwise you open up race conditions.
Cheers,
Jeff
