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

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org, Gunnlaugur Thor Briem <gunnlaugur(at)gmail(dot)com>
Subject: Re: EXPLAIN (no ANALYZE) taking an hour for INSERT FROM SELECT
Date: 2015-03-06 00:44:54
Message-ID: 6068.1425602694@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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 :-(.

In any case, I concur with your advice:

> Even if you could reproduce the problem on another machine (because of
> keeping the data internal) on a server with debug symbols and see where
> most of the time is spent (e.g. using 'perf top'), that'd be useful.

Without some more-precise idea of where the time is going, we're really
just guessing as to the cause.

regards, tom lane

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Jim Nasby 2015-03-06 01:29:26 Re: slow server : s_lock and _bt_checkkeys on perf top
Previous Message Tomas Vondra 2015-03-06 00:20:11 Re: EXPLAIN (no ANALYZE) taking an hour for INSERT FROM SELECT