From: Tomas Vondra <tomas.vondra@2ndquadrant.com>
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
Date: Fri, 06 Mar 2015 18:18:49 +0100
Message-ID: <54F9E179.4000804@2ndquadrant.com> (raw)
In-Reply-To: <6068.1425602694@sss.pgh.pa.us>
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>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tomas.vondra@2ndquadrant.com, tgl@sss.pgh.pa.us, gunnlaugur@gmail.com
Subject: Re: EXPLAIN (no ANALYZE) taking an hour for INSERT FROM SELECT
In-Reply-To: <54F9E179.4000804@2ndquadrant.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox