On 29 March 2016 at 20:01, Achilleas Mantzios <achill@xxxxxxxxxxxxxxxxxxxxx> wrote: > We are using PostgreSQL 9.3. Shouldn't the optimizer use a single call to > get_machdef_sister_defids in get_machdef_sister_defids_maxrh ?? It shouldn't be up to the optimizer to evaluate a STABLE function. Only IMMUTABLE functions will be evaluated during planning. What's not that clear to me is if the planner might be able to work a bit harder to create an "Initplan" for stable functions with Const arguments. Right now I can't quite see a reason why that couldn't be improved upon, after all, the documentation does claim that a STABLE function during a "single table scan it will consistently return the same result for the same argument values". However it would be quite simple just for you to force the STABLE function to be evaluated once, instead of once per row, just by modifying your query to become: select max(rh) into tmp from items where vslwhid=vvslid and itoar(defid) ~ (select get_machdef_sister_defids(vdefid)); Viewing the EXPLAIN of this, you'll notice the InitPlan, which will evaluate the function and allow the use the output value as a parameter in the main query. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
