| From: | Gerardo Herzig <gherzig(at)fmed(dot)uba(dot)ar> |
|---|---|
| To: | pgsql-performance(at)postgresql(dot)org, "pgsql-sql " <pgsql-sql(at)postgresql(dot)org> |
| Subject: | performance drop when function argument is evaluated in WHERE clause |
| Date: | 2014-04-08 11:53:41 |
| Message-ID: | 731037499.293695.1396958021532.JavaMail.root@fmed.uba.ar |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-performance 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
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Andrew W. Gibbs | 2014-04-08 12:48:35 | query against large table not using sensible index to find very small amount of data |
| Previous Message | Manoj Gadi | 2014-04-08 09:35:39 | Nested loop issue |
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Tom Lane | 2014-04-08 13:50:01 | Re: [PERFORM] performance drop when function argument is evaluated in WHERE clause |
| Previous Message | Jan Ostrochovsky | 2014-04-04 14:43:52 | Re: Formatting query result like a calendar |