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?
Merlin