performance drop when function argument is evaluated in WHERE clause

Поиск
Список
Период
Сортировка
От Gerardo Herzig
Тема performance drop when function argument is evaluated in WHERE clause
Дата
Msg-id 731037499.293695.1396958021532.JavaMail.root@fmed.uba.ar
обсуждение исходный текст
Ответы Re: performance drop when function argument is evaluated in WHERE clause
Список pgsql-performance
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



В списке pgsql-performance по дате отправления:

Предыдущее
От: "Manoj Gadi"
Дата:
Сообщение: Nested loop issue
Следующее
От: "Andrew W. Gibbs"
Дата:
Сообщение: query against large table not using sensible index to find very small amount of data