From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Gerardo Herzig <gherzig(at)fmed(dot)uba(dot)ar> |
Cc: | pgsql-performance(at)postgresql(dot)org, "pgsql-sql " <pgsql-sql(at)postgresql(dot)org> |
Subject: | Re: [PERFORM] performance drop when function argument is evaluated in WHERE clause |
Date: | 2014-04-08 13:50:01 |
Message-ID: | 23396.1396965001@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance pgsql-sql |
Gerardo Herzig <gherzig(at)fmed(dot)uba(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2014-04-08 13:55:38 | Re: query against large table not using sensible index to find very small amount of data |
Previous Message | Shaun Thomas | 2014-04-08 13:39:41 | Re: query against large table not using sensible index to find very small amount of data |
From | Date | Subject | |
---|---|---|---|
Next Message | Gerardo Herzig | 2014-04-08 17:33:27 | Re: [PERFORM] performance drop when function argument is evaluated in WHERE clause |
Previous Message | Gerardo Herzig | 2014-04-08 11:53:41 | performance drop when function argument is evaluated in WHERE clause |