Re: Query works when kludged, but would prefer "best practice" solution

"Carlo Stonebanks" <stonec(dot)register(at)sympatico(dot)ca> writes:
> Thanks for the suggestion - this concept is pretty new to me. Can you expand
> a bit on the idea of how to place such a "dummy" constraint on a function,
> and the conditions on which it affects the planner?

Let's say that you know that the function's result column "x" can only
range from 1 to 1000. The planner does not know that, and has no
statistics from which it could guess, so it's going to fall back on
default selectivity estimates for any WHERE clause involving x.
So for instance you could tack on something like

FROM ... (select * from myfunc() where x <= 1000) ...

which will change the actual query result not at all, but will cause the
planner to reduce its estimate of the number of rows out by whatever the
default selectivity estimate for an inequality is (from memory, 0.333,
but try it and see). If that's too much or not enough, you could try
some other clauses that will never really reject any rows, for instance

where x >= 1 and x <= 1000
where x <> -1
where x is not null

Of course this technique depends on knowing something that will always
be true about your data, but most people can think of something...

Now this is not going to affect the evaluation of the function itself at
all. What it will do is affect the shape of a join plan built atop that
function scan, since joins are pretty much all about minimizing the
number of intermediate rows.

> Would this require that
> constraint_exclusion be set on?

No.

regards, tom lane