On Sat, Mar 7, 2015 at 7:44 AM, Tomas Vondra <tomas.vondra@xxxxxxxxxxxxxxx> wrote:
On 7.3.2015 03:26, Jeff Janes wrote:
> On Fri, Mar 6, 2015 at 5:38 PM, Tom Lane <tgl@xxxxxxxxxxxxx
> <mailto:tgl@xxxxxxxxxxxxx>> 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
