Thread: performance drop when function argument is evaluated in WHERE clause

Hi all. I have a function that uses a "simple" select between 3 tables. There is a function argument to help choose how
aWHERE 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?

Thanks!
Gerardo

Gerardo Herzig <gherzig@fmed.uba.ar> writes:
> Hi all. I have a function that uses a "simple" select between 3 tables. There is a function argument to help choose
howa 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

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
amountof extra code. 

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

Thanks again for your time!

Gerardo



----- Mensaje original -----
> De: "Tom Lane" <tgl@sss.pgh.pa.us>
> Para: "Gerardo Herzig" <gherzig@fmed.uba.ar>
> CC: pgsql-performance@postgresql.org, "pgsql-sql" <pgsql-sql@postgresql.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@fmed.uba.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
>

Gerardo Herzig <gherzig@fmed.uba.ar> writes:
> 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
atall.  
> The "conditional filter" es a pretty common pattern in our functions, i would have to add (and maintain) a
substantialamount of extra code. 

In that case consider moving to 9.2 or later.  I believe it'd handle
this scenario better.

            regards, tom lane