Re: [PERFORM] performance drop when function argument is evaluated in WHERE clause - Mailing list pgsql-sql

From Gerardo Herzig
Subject Re: [PERFORM] performance drop when function argument is evaluated in WHERE clause
Date
Msg-id 34678100.310095.1396978407846.JavaMail.root@fmed.uba.ar
Whole thread Raw
In response to Re: [PERFORM] performance drop when function argument is evaluated in WHERE clause  (Tom Lane <tgl@sss.pgh.pa.us>)
Responses Re: Re: [PERFORM] performance drop when function argument is evaluated in WHERE clause  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-sql
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
>


pgsql-sql by date:

Previous
From: Tom Lane
Date:
Subject: Re: [PERFORM] performance drop when function argument is evaluated in WHERE clause
Next
From: Tom Lane
Date:
Subject: Re: Re: [PERFORM] performance drop when function argument is evaluated in WHERE clause