Re: [PERFORM] performance drop when function argument is evaluated in WHERE clause

Tom, thanks (as allways) for your answer. This is a 9.1.12. I have to say, im not very happy about if-elif-else'ing at all.
The "conditional filter" es a pretty common pattern in our functions, i would have to add (and maintain) a substantial amount of extra code.

And i dont really understand why the optimizer issues, since the arguments are immutable "strings", and should (or could at least) be evaluated only once.

Thanks again for your time!

Gerardo

----- Mensaje original -----
> De: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
> Para: "Gerardo Herzig" <gherzig(at)fmed(dot)uba(dot)ar>
> CC: pgsql-performance(at)postgresql(dot)org, "pgsql-sql" <pgsql-sql(at)postgresql(dot)org>
> Enviados: Martes, 8 de Abril 2014 10:50:01
> Asunto: Re: [PERFORM] performance drop when function argument is evaluated in WHERE clause
>
> Gerardo Herzig <gherzig(at)fmed(dot)uba(dot)ar> writes:
> > Hi all. I have a function that uses a "simple" select between 3
> > tables. There is a function argument to help choose how a WHERE
> > clause applies. This is the code section:
> > select * from....
> > [...]
> > where case $3
> > when 'I' then [filter 1]
> > when 'E' then [filter 2]
> > when 'P' then [filter 3]
> > else true end
>
> > When the function is called with, say, parameter $3 = 'I', the
> > funcion run in 250ms,
> > but when there is no case involved, and i call directly "with
> > [filter 1]" the function runs in 70ms.
>
> > Looks like the CASE is doing something nasty.
> > Any hints about this?
>
> Don't do it like that. You're preventing the optimizer from
> understanding
> which filter applies. Better to write three separate SQL commands
> surrounded by an if/then/else construct.
>
> (BTW, what PG version is that? I would think recent versions would
> realize that dynamically generating a plan each time would work
> around
> this. Of course, that approach isn't all that cheap either. You'd
> probably still be better off splitting it up manually.)
>
> regards, tom lane
>