Message-ID: <54F9E179.4000804@2ndquadrant.com>
Date: Fri, 06 Mar 2015 18:18:49 +0100
From: Tomas Vondra <tomas.vondra@2ndquadrant.com>
Organization: 2ndQuadrant
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
 rv:31.0) Gecko/20100101 Thunderbird/31.5.0
MIME-Version: 1.0
To: Tom Lane <tgl@sss.pgh.pa.us>
CC: pgsql-performance@postgresql.org,
 Gunnlaugur Thor Briem <gunnlaugur@gmail.com>
Subject: Re: EXPLAIN (no ANALYZE) taking an hour for INSERT FROM
 SELECT
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> <6068.1425602694@sss.pgh.pa.us>
In-Reply-To: <6068.1425602694@sss.pgh.pa.us>
Content-Type: text/plain; charset=iso-8859-2
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

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


regards

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


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