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

On Sat, Mar 7, 2015 at 7:44 AM, Tomas Vondra <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>> 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).

In other similar cases I've looked at (for a different reason), the
executor wouldn't do that work at all because the plan it actually chooses
only touches a handful of rows. So it is planning a merge join, only to
realize how ridiculous one would be and so not use one. But it still pays
the price. In that, case the thing that would do the needful, in lieu of
the planner, would be a vacuum process. Which is optimal both because it
is in the background, and is optimized for efficient IO.

Cheers,

Jeff