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
