Re: EXPLAIN (no ANALYZE) taking an hour for INSERT FROM SELECT - Mailing list pgsql-performance

Tomas Vondra <tomas.vondra@2ndquadrant.com> wrote:
> On 6.3.2015 01:44, Tom Lane wrote:
>> Tomas Vondra <tomas.vondra@2ndquadrant.com> writes:
>>> On 5.3.2015 16:01, Gunnlaugur Thor Briem wrote:
>>>> - postgres version is 9.1.13
>>>
>>> The only thing I can think of is some sort of memory exhaustion,
>>> resulting in swapping out large amounts of memory.
>>
>> I'm wondering about the issue addressed by commit fccebe421 ("Use
>> SnapshotDirty rather than an active snapshot to probe index
>> endpoints"). Now, that was allegedly fixed in 9.1.13 ... but if the
>> OP were confused and this server were running, say, 9.1.12, that
>> could be a viable explanation. Another possibly viable explanation
>> for seeing the issue in 9.1.13 would be if I fat-fingered the
>> back-patch somehow :-(.
>
> How would fccebe421 explain the large amount of random writes (~4MB/s
> for more than an hour), reported in the initial post? And why would that
> only affect the EXPLAIN and not the bare query?
>
> I guess there might be two sessions, one keeping uncommitted changes
> (thus invisible tuples), and the other one doing the explain. And the
> actual query might be executed after the first session does a commit.
>
> But the random writes don't really match in this scenario ...

Sure they do -- both the index and heap pages may be rewritten with
hints that the rows are dead.  In this email:

http://www.postgresql.org/message-id/19685.1384307200@sss.pgh.pa.us

... Tom said:

| The other thing we might consider doing is using SnapshotAny, which
| would amount to just taking the extremal index entry at face value.
| This would be cheap all right (in fact, we might later be able to optimize
| it to not even visit the heap).  However, I don't like the implications
| for the accuracy of the approximation.  It seems quite likely that an
| erroneous transaction could insert a wildly out-of-range index entry
| and then roll back --- or even if it committed, somebody might soon come
| along and clean up the bogus entry in a separate transaction.  If we use
| SnapshotAny then we'd believe that bogus entry until it got vacuumed, not
| just till it got deleted.  This is a fairly scary proposition, because
| the wackier that extremal value is, the more impact it will have on the
| selectivity estimates.
|
| If it's demonstrated that SnapshotDirty doesn't reduce the estimation
| costs enough to solve the performance problem the complainants are facing,
| I'd be willing to consider using SnapshotAny here.  But I don't want to
| take that step until it's proven that the more conservative approach
| doesn't solve the performance problem.

I wonder whether this isn't an example of a case where SnapshotAny
would do better.  I have definitely seen cases where BIND time on a
bloated database has exceeded all other PostgreSQL time combined --
in this planner probe of the index.  I haven't seen two hours on a
single EXPLAIN, but I have seen it be several times the execution
time.

Regarding the concerns about the accuracy of the estimate and the
effects on the planner that an incorrect selectivity estimate could
have -- clearly scanning a large number of index tuples and chasing
them to the heap just to find that the tuples are not visible is
about as expensive as if they were visible *for the index scan step
itself*.  I wonder whether there would be any way to allow the
index scan cost to be based on the work it has to do while somehow
generating a reasonable number for the rows it will find to be
visible, so that estimation of other nodes can be based on that.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company