From: Tom Lane <tgl@sss.pgh.pa.us>
To: Tomas Vondra <tomas.vondra@2ndquadrant.com>
cc: pgsql-performance@postgresql.org,
 Gunnlaugur Thor Briem <gunnlaugur@gmail.com>
Subject: Re: EXPLAIN (no ANALYZE) taking an hour for INSERT FROM SELECT
In-reply-to: <54F8F2BB.2010402@2ndquadrant.com>
References: 
 <CAPs+M8Kk6sGMpXN4KaANmDWXJ1F-FKwD+6szh0QmLv0HZfhY4g@mail.gmail.com>
 <B6F6FD62F2624C4C9916AC0175D56D8828B82149@jenmbs01.ad.intershop.net>
 <CAPs+M8JtK3EqjPp=o=CLpOCr4L_0zwmKnbr6ib2T=r+-Z4xP7Q@mail.gmail.com>
 <54F8F2BB.2010402@2ndquadrant.com>
Comments: In-reply-to Tomas Vondra <tomas.vondra@2ndquadrant.com>
 message dated "Fri, 06 Mar 2015 01:20:11 +0100"
Date: Thu, 05 Mar 2015 19:44:54 -0500
Message-ID: <6068.1425602694@sss.pgh.pa.us>
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

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

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


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance