> The attached script shows a simplicistic version of this
> behaviour. The first SP is the "ideal" one wrt the execution time, as
> it always uses the best indexes. The other two try to accomplish the
> same job, using some trick to avoid combinatorial replication of the
> same code: clearly the "ideal" way of doing quickly become impractical
> as the number of arguments grows.


> CREATE OR ALTER PROCEDURE proc$testbes1 (p_dat DATE, p_id INTEGER)
> RETURNS (what INTEGER) AS
> BEGIN
> FOR SELECT what
> FROM testbes tb
> WHERE ((:p_id IS NULL) OR tb.id = :p_id)
> AND ((:p_dat IS NULL) OR tb.dat = :p_dat)
> INTO what DO
> SUSPEND;
> END

This one can't use index because it contains (:p_id IS NULL)
which can't use index (since there is no table field),
and can't be optimized at prepare time.

> CREATE OR ALTER PROCEDURE proc$testbes2 (p_dat DATE, p_id INTEGER)
> RETURNS (what INTEGER) AS
> BEGIN
> FOR SELECT what
> FROM testbes tb
> WHERE tb.id = coalesce(:p_id, tb.id)
> AND tb.dat = coalesce(:p_dat, tb.dat)
> INTO what DO
> SUSPEND;
> END

This one can't use index because the fields are parts of expressions.
(and depend on parameter, so even expressions indexes would not help)

Ivan