Re: [PATCH] Add a guc parameter to control limit clause adjust path cost.

Bryan Green <dbryan(dot)green(at)gmail(dot)com> writes:
> I am new to this area, but isn't the problem the filter selectivity
> estimation? The filter Filter: ((voucher_file_name IS NULL) AND
> (payment_type =
>> ANY ('{7,8}'::numeric[])) AND (status = '3'::numeric))
>> Rows Removed by Filter: 586208
>> Buffers: shared hit=95516 (main=95516 vm=0 fsm=0)
> removes the most rows. The optimizer shouldn't have selected the index
> scan path even with LIMIT existing.

I hadn't looked closely at the two plans being compared, but yeah,
they *both* suck. Why is it choosing a nestloop join when it knows
the outside will yield many many rows? The "good" plan is better
only because it decided to parallelize the outer table scan, which
moves the goalposts not very far and frankly seems like a chance
result anyway. I wonder if we are looking at the behavior of a
planner that's been locally modified in other ways, or is using
strange cost settings.

> Maybe the benefit of the LIMIT
> should be adjusted downward based on the filter selectivity?

Yeah, ideas similar to that are what I was alluding to in my
other response. The typical issue is that the filter clause's
selectivity is underestimated (so that there are fewer matching
rows than we thought) and/or the matching rows preferentially
appear closer to the end of the index scan than the beginning.
I'd be happier with a proposal that would inflate the LIMIT's
minimum cost by some factor based on the likelihood of that
sort of error, rather than just kneecapping the calculation
altogether.

regards, tom lane