On Mon, Mar 14, 2011 at 3:46 AM, Julius Tuskenis <julius@xxxxxxxx> 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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
