Message-ID: <55072D9F.40808@BlueTreble.com>
Date: Mon, 16 Mar 2015 14:23:11 -0500
From: Jim Nasby <Jim.Nasby@BlueTreble.com>
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.9;
 rv:31.0) Gecko/20100101 Thunderbird/31.4.0
MIME-Version: 1.0
To: Tom Lane <tgl@sss.pgh.pa.us>, Gunnlaugur Thor Briem <gunnlaugur@gmail.com>
CC: Tomas Vondra <tomas.vondra@2ndquadrant.com>, Jeff Janes
 <jeff.janes@gmail.com>, Kevin Grittner <kgrittn@ymail.com>,
 "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Re: EXPLAIN (no ANALYZE) taking an hour for INSERT FROM
 SELECT
References: <54F9E179.4000804@2ndquadrant.com>
 <1407502480.21180.1425691482543.JavaMail.yahoo@mail.yahoo.com>
 <27245.1425692294@sss.pgh.pa.us>
 <CAMkU=1zxAB4AQySxex_DXP2mt+Ab=S+nQw+2M1rY7CFJzgYfTQ@mail.gmail.com>
 <54FB1CDA.1080606@2ndquadrant.com>
 <CAPs+M8JaFmns1me5PB5TE9jsTscM0yW9ONL4JwzFHmcbQ-9p-g@mail.gmail.com>
 <17467.1426090533@sss.pgh.pa.us>
In-Reply-To: <17467.1426090533@sss.pgh.pa.us>
Content-Type: text/plain; charset="windows-1252"; format=flowed
Content-Transfer-Encoding: 7bit
X-MS-Exchange-CrossTenant-OriginalArrivalTime: 16 Mar 2015 19:22:49.4932 (UTC)
X-MS-Exchange-CrossTenant-FromEntityHeader: Hosted
X-MS-Exchange-Transport-CrossTenantHeadersStamped: DM2PR11MB0350
X-Pg-Spam-Score: -1.9 (-)
List-Archive: <http://archives.postgresql.org/pgsql-performance>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-ID: <pgsql-performance.postgresql.org>
List-Owner: <mailto:pgsql-performance-owner@postgresql.org>
List-Post: <mailto:pgsql-performance@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-performance>
List-Unsubscribe: 
 <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
X-Mailing-List: pgsql-performance
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

On 3/11/15 11:15 AM, Tom Lane wrote:
> Gunnlaugur Thor Briem <gunnlaugur@gmail.com> writes:
>> Yes, I think that's it: I've just realized that immediately prior to the
>> INSERT, in the same transaction, an unfiltered DELETE has been issued; i.e.
>> the whole table is being rewritten. Then the INSERT is issued ... with a
>> WHERE clause on non-existence in the (now empty) table.
>
>> In that case of course the WHERE clause is unnecessary, as it will always
>> evaluate as true (and we've locked the whole table for writes). Looks like
>> it is a lot worse than unnecessary, though, if it triggers this performance
>> snafu in EXPLAIN INSERT.
>
> Ah-hah.  So what's happening is that the planner is doing an indexscan
> over the entire table of now-dead rows, looking vainly for an undeleted
> maximal row.  Ouch.
>
> I wonder how hard it would be to make the indexscan give up after hitting
> N consecutive dead rows, for some suitable N, maybe ~1000.  From the
> planner's viewpoint it'd be easy enough to fall back to using whatever
> it had in the histogram after all.  But that's all happening down inside
> index_getnext, and I'm hesitant to stick some kind of wart into that
> machinery for this purpose.

ISTM what we really want here is a time-based behavior, not number of 
rows. Given that, could we do the index probe in a subtransaction, set 
an alarm for X ms, and simply abort the subtransaction if the alarm fires?
-- 
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com


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