On Jan 15, 2008 7:58 PM, Tom Lane <tgl@xxxxxxxxxxxxx> wrote:
> If that really is the behavior you want, and not a typo
It is, most of parameters passed to a plpgsql function are in fact parts of the filter and if certain filter item is null it is considered unknown and we don't want it to affect the result in that case. The query pattern itself is a shortcut to cover both cases in one expressions which works fine while applied to secondary fields and delivers troubles when used on key indexed fields that might influence planning decisions. Thanks for the "union" idea, other ways around I could think of are dynamic queries or a bunch of plpgsql's 'if-elseif's.
> It can see that it's got a range constraint on 'val', but not exactly
> how wide the range is, so the selectivity estimate is DEFAULT_RANGE_INEQ_SEL
> which is hardwired at 0.005. 0.005 * 1000000 = 5000.
Probably this (parametrized query's planning) is something worth mentioning in the docs one day.
> As for the rowcount estimate, I think it's using DEFAULT_UNK_SEL (which
> also happens to be 0.005) as the selectivity of an IS NULL test with a
> non-Var target. That's a bit useless in this case, since for any one
> call of the query it's either going to be constant true or constant
> false, but there's not any obvious better way to do it.
Hm.. It could build union'ed plans for every possible value of unknown boolean _expression_ similar to that of your example's, though this could make plans grow huge.
> If that really is the behavior you want, and not a typo
It is, most of parameters passed to a plpgsql function are in fact parts of the filter and if certain filter item is null it is considered unknown and we don't want it to affect the result in that case. The query pattern itself is a shortcut to cover both cases in one expressions which works fine while applied to secondary fields and delivers troubles when used on key indexed fields that might influence planning decisions. Thanks for the "union" idea, other ways around I could think of are dynamic queries or a bunch of plpgsql's 'if-elseif's.
> It can see that it's got a range constraint on 'val', but not exactly
> how wide the range is, so the selectivity estimate is DEFAULT_RANGE_INEQ_SEL
> which is hardwired at 0.005. 0.005 * 1000000 = 5000.
Probably this (parametrized query's planning) is something worth mentioning in the docs one day.
> As for the rowcount estimate, I think it's using DEFAULT_UNK_SEL (which
> also happens to be 0.005) as the selectivity of an IS NULL test with a
> non-Var target. That's a bit useless in this case, since for any one
> call of the query it's either going to be constant true or constant
> false, but there's not any obvious better way to do it.
Hm.. It could build union'ed plans for every possible value of unknown boolean _expression_ similar to that of your example's, though this could make plans grow huge.
