Re: Planner hints in Postgresql

On Mon, Mar 17, 2014 at 7:12 PM, Jim Nasby <jim(at)nasby(dot)net> wrote:

> On 3/17/14, 5:07 PM, Claudio Freire wrote:
>
>
>> On Mon, Mar 17, 2014 at 7:01 PM, Jim Nasby <jim(at)nasby(dot)net <mailto:
>> jim(at)nasby(dot)net>> wrote:
>>
>> Even better would be if the planner could estimate how bad a plan
>> will become if we made assumptions that turn out to be wrong.
>>
>>
>> That's precisely what risk estimation was about.
>>
>> Something like
>>
>> SELECT * FROM wherever WHEN id > something LIMIT COST 10000;
>>
>> Would forbid a sequential scan *if* the table is big enough to suspect
>> the plan might take that much, or a nested loop *if* the planner cannot
>> *prove* it will be faster than that.
>>
>> I don't believe the limit unit is obscure at all (page fetches being a
>> nice measuring stick), but what is, is what do you do when no plan fits the
>> limits.
>>
>
> I don't think that's the same thing... what you're describing is a way to
> not begin a query if a low-enough cost plan can't be found.
>
> What I'm talking about is when the planner picks one low-cost plan over
> another and it turns out the estimate of the one that was picked was WAY
> off. I've actually seen cases where plan estimates that were off by just
> 100 units produce wildly different results.
>
> In that scenario, LIMIT COST won't help at all.
>

The case you describe is different. It's when a plan *effectively* is more
expensive than estimated, but the planner could not estimate it.

That's what was mentioned about switching plans mid-way through them, which
is IMO quite ill-defined as such (it needs a lot of love in order to get a
workable spec out of that).