Re: unexpected stable function behavior

On Mon, Mar 14, 2011 at 3:46 AM, Julius Tuskenis <julius(at)nsoft(dot)lt> wrote:
> Hello, Merlin
>
> Thank you for your quick response.
>
> 2011.03.10 23:14, Merlin Moncure rašė:
>
> This is a huge problem with non trivial functions in the select list.
> Pushing the result into and a subquery does NOT guarantee that the
> inner result is materialized first.
>
> From the postgresql documentation about STABLE functions: "This category
> allows the optimizer to optimize multiple calls of the function to a single
> call." I thought that this means that optimizer executes the function only
> for now parameter sets and stores results in some "cache" and use it if the
> parameters are already known. I realize this is very naive approach and most
> probably everything is much more complicated. I would appreciate if someone
> would explain the mechanism (or provide with some useful link).

Just because some optimizations can happen doesn't mean they will
happen or there is even capability to make them happen. There was
some recent discussion about this very topic here:
http://postgresql.1045698.n5.nabble.com/function-contants-evaluated-for-every-row-td3278945.html.

> Try a CTE.
>
> with foo as
> (
> select yadda;
> )
> select func(foo.a), foo.* from foo;
>
> I'm sorry, but I'm totally new to CTE. Would you please show me how should I
> use the stable function and where the parameters should be put to improve
> the behavior of the optimizer for my problem?

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;

Another way to fight this is to play with the cost planner hint
parameter in 'create function', but I prefer the CTE -- it gives
strong guarantees about order of execution which is what you really
want. CTEs are great btw, I'd start learning them immediately.

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...do you agree CTE is
the right way to advise handling this problem...is it worth further
notation?

merlin