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
Re: [PERFORM] performance drop when function argument is evaluated in WHERE clause
From
Tom Lane
Date:
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
Re: [PERFORM] performance drop when function argument is evaluated in WHERE clause
From
Gerardo Herzig
Date:
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 >
Re: Re: [PERFORM] performance drop when function argument is evaluated in WHERE clause
From
Tom Lane
Date:
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