Re: EXPLAIN (no ANALYZE) taking an hour for INSERT FROM SELECT

On 6.3.2015 01:44, Tom Lane wrote:
> Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)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 ...

regards

--
Tomas Vondra http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services