1. Basically, I was assuming that "STABLE" was more than just a optimizer "hint".
2. Is it not better to evaluate a STABLE function once, and pass the returned constant to a function, than pass the function itself and inline it? Is not passing a constant more efficient than even an inline query? Please show me an example where an inline query gets a performance boost. Thanks! -----Original Message----- From: Tom Lane [mailto:t...@sss.pgh.pa.us] Sent: Wednesday, August 11, 2010 4:34 PM To: Brian Ceccarelli Cc: Robert Haas; pgsql-bugs@postgresql.org Subject: Re: [BUGS] BUG #5611: SQL Function STABLE promoting to VOLATILE Brian Ceccarelli <bceccare...@net32.com> writes: > OK. The documentation says "allows the optimizer to optimize . . . ." > But then the example guarantees the one-time-only for a index scan condition. > No, the documentation states that *if* an index scan is used, functions involved in the indexscan's qual condition will be evaluated just once, rather than once per row. There is no "guarantee" of any sort that such a plan will be chosen. The point of the STABLE marking is to inform the optimizer that it is safe to choose an index scan because the function's results will not change compared to the naive SQL semantics wherein the WHERE condition is evaluated for each row. Thus, the guarantee actually runs the other way: you are promising the optimizer that your function doesn't have side effects or change its results intra-query. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
