Re: parameter hints to the optimizer

On Wed, 2004-07-21 at 19:12, Merlin Moncure wrote:
> There is one problem with the optimizer that is a constant source of
> frustration. When using prepared statements and functions (particularly
> where function parameters are passed to a query), the optimizer often
> fails to utilize an index inside a plan.
>
> This is a well known problem because the parameter values are not known
> at the time the plan is generated, making things difficult for the
> optimizer.
>
> It would be nice if 'hint' or sample parameters could be used when
> creating the statement so the optimizer could use those values when
> generating the plan. For example, the default parameter syntax of C++
> could be borrowed (assuming this doesn't break any syntax rules).
>
> example:
> prepare my_statement prepare (character varying='abc')
> as select * from t where t.k = $1;
>
> create function my_function(int4=1234) returns [...]
>
> Another way to deal with the problem is to defer plan generation until
> the first plan execution and use the parameters from that execution.
>
> Am I crazy? Comments?

Crazy enough to suggest what other RDBMS do.

It's a common problem, since it defeats the use of histogram statistics
to determine specific cardinality rather than generic cardinality.

The answer is to follow what those others do, since programs will be
written to take advantage of those optimization quirks.

DB2 supports various modes for BIND: REOPT(ALWAYS), REOPT(ONCE),
REOPT(VARS) and REOPT(NONE) - which are then manifested in their
precompiler.

..back to you,

Best Regards, Simon Riggs