From: Tom Lane <tgl@sss.pgh.pa.us>
To: Gerardo Herzig <gherzig@fmed.uba.ar>
cc: pgsql-performance@postgresql.org, "pgsql-sql " <pgsql-sql@postgresql.org>
Subject: Re: [PERFORM] performance drop when function argument is evaluated in
 WHERE clause
In-reply-to: <731037499.293695.1396958021532.JavaMail.root@fmed.uba.ar>
References: <731037499.293695.1396958021532.JavaMail.root@fmed.uba.ar>
Comments: In-reply-to Gerardo Herzig <gherzig@fmed.uba.ar>
 message dated "Tue, 08 Apr 2014 08:53:41 -0300"
Date: Tue, 08 Apr 2014 09:50:01 -0400
Message-ID: <23396.1396965001@sss.pgh.pa.us>
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

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