Re: unexpected stable function behavior

Hello,

2011.03.14 15:41, Merlin Moncure rašė:
> WITH results as
> (
>   SELECT distinct price_id as price_id
>   FROM ticket_price
>     JOIN ticket_has_ticket_price ON (price_id = thtp_price_id)
>   WHERE price_event_id = 7820 and (current_timestamp>= price_date AND
> current_timestamp<= price_date_till)
>   ) as qq
> )
>   SELECT web_select_extra_price(price_id, 7820, 1) from results;
>
Thank you Merlin for your help. I have updated my function to use CTE. 
Although there was no performance improvement (I had the select with 
function using distinct values joined earlyer) it's good to know the 
optimizer will not change the way I want the query to be executed. Thank 
you once again.

> CTEs are great btw, I'd start learning them immediately.
I am going to do that.
> IMNSHO, this (uncontrolled number of function executions when run via
> field select list) is a common gotcha w/postgres and a FAQ.  Also the
> documentation is not very helpful on this point...
Yes, I totally agree with you. I think sentence like "Although function 
is marked as STABLE or IMMUTABLE the optimizer is not obliged to take 
advantage of these properties." (sorry for my English).
> do you agree CTE is the right way to advise handling this problem...is it worth further
> notation?
Yes, the CTE worked fine for me. Reading some more on this topic I found 
some comments that the optimizer has no possibility to know how many 
times the function is to be called in such queries (without actually 
executing the query), so there is no way to determine the cost. That 
explains why not the optimal plan was chosen to my query.

-- 
Julius Tuskenis
Programavimo skyriaus vadovas
UAB nSoft
mob. +37068233050