Re: Bad plan for nested loop + limit

On Fri, Feb 27, 2009 at 11:54 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> The problem here is that the planner estimates the cost of a Limit
> plan node by adding up (1) the startup cost of the underlying plan
> node, in this case 0 for the nestjoin, and (2) a percentage of the run
> cost, based on the ratio of the number of rows expected to be returned
> to the total number of rows.  In this case, the nested loop is
> expected to return 6944 rows, so it figures it won't have to get very
> far to find the 4 you requested.
[...]
> I will think about this some more but nothing is occurring to me off
> the top of my head.

Thanks for explaining. Is there any way to rewrite the query in a way
that will avoid the nested loop join -- other than actually disabling
nested loop joins? If I do the latter, the resulting query uses a hash
join and completes in 80-100 ms, which is still pretty horrible,
especially for a query that returns nothing, but extremely auspicious
compared to the unthinkable 4-5 seconds for the current query.

Alexander.