Re: Disabling options lowers the estimated cost of a query

Tomas Vondra <tomas(dot)vondra(at)enterprisedb(dot)com> writes:
> On 2/26/21 4:00 AM, Tom Lane wrote:
>> Hmm. While the search should be exhaustive, there are pretty
>> aggressive pruning heuristics (mostly in and around add_path()) that
>> can cause us to drop paths that don't seem to be enough better than
>> other alternatives. I suspect that the seqscan plan may have beaten
>> out the other one at some earlier stage that didn't think that the
>> startup-cost advantage was sufficient reason to keep it.

> It seems the whole problem is in generate_orderedappend_paths(), which
> simply considers two cases - paths with minimal startup cost and paths
> with minimal total costs. But with LIMIT that does not work, of course.

Ah, I see.

> Not sure how to fix this without making generate_orderedappend_paths way
> more complicated ...

You could, if root->tuple_fraction is > 0, also make a set of paths that
are optimized for that fetch fraction. This is cheating to some extent,
because it's only entirely accurate when your rel is the only one, but it
seems better than ignoring the issue altogether. The code to select the
right child path would be approximately like get_cheapest_fractional_path,
except that you need to restrict it to paths with the right sort order.

regards, tom lane