Date: Tue, 8 Apr 2014 14:33:27 -0300 (ART)
From: Gerardo Herzig <gherzig@fmed.uba.ar>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org, pgsql-sql <pgsql-sql@postgresql.org>
Message-ID: <34678100.310095.1396978407846.JavaMail.root@fmed.uba.ar>
In-Reply-To: <23396.1396965001@sss.pgh.pa.us>
Subject: Re: [PERFORM] performance drop when function argument is evaluated
 in WHERE clause
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

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@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
> 


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql