From: | Jim Nasby <Jim(dot)Nasby(at)BlueTreble(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Gunnlaugur Thor Briem <gunnlaugur(at)gmail(dot)com> |
Cc: | Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com>, Jeff Janes <jeff(dot)janes(at)gmail(dot)com>, Kevin Grittner <kgrittn(at)ymail(dot)com>, "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: EXPLAIN (no ANALYZE) taking an hour for INSERT FROM SELECT |
Date: | 2015-03-16 19:23:11 |
Message-ID: | 55072D9F.40808@BlueTreble.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
On 3/11/15 11:15 AM, Tom Lane wrote:
> Gunnlaugur Thor Briem <gunnlaugur(at)gmail(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Jim Nasby | 2015-03-16 19:24:22 | Re: EXPLAIN (no ANALYZE) taking an hour for INSERT FROM SELECT |
Previous Message | Josh Berkus | 2015-03-16 19:12:15 | Re: Bad cost estimate with FALSE filter condition |