agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedperformance drop when function argument is evaluated in WHERE clause
4+ messages / 2 participants
[nested] [flat]
* performance drop when function argument is evaluated in WHERE clause
@ 2014-04-08 11:53 Gerardo Herzig <gherzig@fmed.uba.ar>
0 siblings, 1 reply; 4+ messages in thread
From: Gerardo Herzig @ 2014-04-08 11:53 UTC (permalink / raw)
To: pgsql-sql
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?
Thanks!
Gerardo
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [PERFORM] performance drop when function argument is evaluated in WHERE clause
@ 2014-04-08 13:50 Tom Lane <tgl@sss.pgh.pa.us>
parent: Gerardo Herzig <gherzig@fmed.uba.ar>
0 siblings, 1 reply; 4+ messages in thread
From: Tom Lane @ 2014-04-08 13:50 UTC (permalink / raw)
To: Gerardo Herzig <gherzig@fmed.uba.ar>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [PERFORM] performance drop when function argument is evaluated in WHERE clause
@ 2014-04-08 17:33 Gerardo Herzig <gherzig@fmed.uba.ar>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: Gerardo Herzig @ 2014-04-08 17:33 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: 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 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Re: [PERFORM] performance drop when function argument is evaluated in WHERE clause
@ 2014-04-08 17:41 Tom Lane <tgl@sss.pgh.pa.us>
parent: Gerardo Herzig <gherzig@fmed.uba.ar>
0 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2014-04-08 17:41 UTC (permalink / raw)
To: Gerardo Herzig <gherzig@fmed.uba.ar>; +Cc: pgsql-sql
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 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.
In that case consider moving to 9.2 or later. I believe it'd handle
this scenario better.
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2014-04-08 17:41 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2014-04-08 11:53 performance drop when function argument is evaluated in WHERE clause Gerardo Herzig <gherzig@fmed.uba.ar>
2014-04-08 13:50 ` Tom Lane <tgl@sss.pgh.pa.us>
2014-04-08 17:33 ` Gerardo Herzig <gherzig@fmed.uba.ar>
2014-04-08 17:41 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox