From: 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: Wed, 11 Mar 2015 12:15:33 -0400
Message-ID: <17467.1426090533@sss.pgh.pa.us> (raw)
In-Reply-To: <CAPs+M8JaFmns1me5PB5TE9jsTscM0yW9ONL4JwzFHmcbQ-9p-g@mail.gmail.com>
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>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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.
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
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: 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: <17467.1426090533@sss.pgh.pa.us>
* 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