On Wed, Aug 11, 2010 at 11:50 AM, Brian Ceccarelli <bceccare...@net32.com> wrote: > OK. The documentation says "allows the optimizer to optimize . . . ." > But then the example guarantees the one-time-only for a index scan condition. > > From the documentation: 8.4.4 Chapter 32 and 8.2.17 Chapter 33. > > .A STABLE function cannot modify the database and is guaranteed to return > the same results given the same arguments for all rows within a single > statement. This category allows the optimizer to optimize multiple calls of > the function to a single call. In particular, it is safe to use an expression > containing such a function in an index scan condition. (Since an index scan > will evaluate the comparison value only once, not once at each row, it is not > valid to use a VOLATILE function in an index scan condition.) > > The behavior of the optimizers <= 8.2 certainly fit the description. The > 8.4 behavior is vastly different.
Reading between the lines, I think I sense that this has got you pretty frustrated, so in defense of the new behavior, let me just mention that, in general, inlining SQL queries results in a HUGE performance benefit. It's sort of unfortunate that it doesn't work out that way for you in this case, but I don't think it's a bad idea in general. *thinks* In theory, the optimization Brian wants is possible here, right? I mean, you could replace the functional call with a Param, and pull the Param out and make it an InitPlan. Seems like that would generally be a win, if you figure to loop more than once and the execution cost of the function is not too tiny. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
