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@xxxxxxxxxxxxx> > Para: "Gerardo Herzig" <gherzig@xxxxxxxxxxx> > CC: pgsql-performance@xxxxxxxxxxxxxx, "pgsql-sql" <pgsql-sql@xxxxxxxxxxxxxx> > Enviados: Martes, 8 de Abril 2014 10:50:01 > Asunto: Re: performance drop when function argument is evaluated in WHERE clause > > Gerardo Herzig <gherzig@xxxxxxxxxxx> 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-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
