Re: EXPLAIN (no ANALYZE) taking an hour for INSERT FROM SELECT

On 11.3.2015 18:30, Jeff Janes wrote:
> On Sat, Mar 7, 2015 at 7:44 AM, Tomas Vondra
> <tomas(dot)vondra(at)2ndquadrant(dot)com <mailto:tomas(dot)vondra(at)2ndquadrant(dot)com>> wrote:
>
> On 7.3.2015 03:26, Jeff Janes wrote:
> > On Fri, Mar 6, 2015 at 5:38 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us <mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us>
> > <mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us <mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us>>> wrote:
> >
> > But the actual query is using a seq scan, and so it would hint the
> > table in efficient sequential order, rather than hinting the table
> > haphazardly in index order like probing the endpoint does.
>
> I think this has nothing to do with the plan itself, but with the
> estimation in optimizer - that looks up the range from the index in some
> cases, and that may generate random I/O to the table.
>
>
> Right. Tom was saying that the work needs to be done anyway, but it is
> just that some ways of doing the work are far more efficient than
> others. It just happens that the executed plan in this case would do it
> more efficiently, (but in general you aren't going to get any less
> efficient than having the planner do it in index order).

Oh! Now I see what you meant. I parsed is as if you're suggesting that
the theory does not match the symptoms because the plan contains
sequential scan yet there's a lot of random I/O. But now I see that's
not what you claimed, so sorry for the noise.

--
Tomas Vondra http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services