From: Jim Nasby <Jim.Nasby@BlueTreble.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
To: Gunnlaugur Thor Briem <gunnlaugur@gmail.com>
Cc: Tomas Vondra <tomas.vondra@2ndquadrant.com>
Cc: Jeff Janes <jeff.janes@gmail.com>
Cc: Kevin Grittner <kgrittn@ymail.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: EXPLAIN (no ANALYZE) taking an hour for INSERT FROM SELECT
Date: Mon, 16 Mar 2015 14:23:11 -0500
Message-ID: <55072D9F.40808@BlueTreble.com> (raw)
In-Reply-To: <17467.1426090533@sss.pgh.pa.us>
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>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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
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: Jim.Nasby@BlueTreble.com, tgl@sss.pgh.pa.us, gunnlaugur@gmail.com, tomas.vondra@2ndquadrant.com, jeff.janes@gmail.com, kgrittn@ymail.com
Subject: Re: EXPLAIN (no ANALYZE) taking an hour for INSERT FROM SELECT
In-Reply-To: <55072D9F.40808@BlueTreble.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