Re: SRF called with optional NULL input runs 7x slower

On Mon, 2007-09-24 at 00:18 -0400, Alvaro Herrera wrote:
> Ow Mun Heng wrote:
>
> > how can I debug or diagnose where the issues lies? Explain analyse
> > doesn't do much since this is a Function Scan anyway.
>
> Take them out of the function and EXPLAIN ANALYZE them as plain SQL.
> Note that you should take the parameters out of the literal, so you need
> to do something like
>
> PREPARE foo AS SELECT ... replace code with $1, etc ...
> EXPLAIN ANALYZE EXECUTE foo(code, ...)
>
> Otherwise they would be treated as constants so the queries would be
> planned differently.
>
> (untested, so correct the syntax appropriately)
>
Okay.. I tried that, but it seems like there's an issue w/ the CASE
statements.

When I tried the prepare w/

AND (CASE WHEN $3 IS NULL THEN true else d.code = any ($3) END)
AND (CASE WHEN $4 IS NULL THEN TRUE else D.id = any($4) END)

it ERRORs w/ could not determine data type of parameter $3

If I were to just use

AND D.code = ANY($3) then it would work.

Any other clues?