8.4b1 regression?
I loaded a copy of a production database into PG 8.4b1 and immediately
saw that all of our queries were significantly slower compared to v8.1.
Some investigation showed that the use of non-IMMUTABLE PL/PGSQL
functions as view columns, when these views are joined with other
views, cause the query to be planned poorly.
Attached are the two different plans. Literally, the only difference
is changing the definition of the custom PL/PGSQL to be IMMUTABLE.
I spent some time coming up with a reproduce-able schema, but it's
almost 500k gzipped. Is that too big to attach to -hackers? The
function in the test schema is simply:
CREATE FUNCTION make_it_slow(id bigint) RETURNS text
LANGUAGE plpgsql AS $$begin return 'non-immutable functions make
it slow'; end;$$;
In our case, the suspect functions *can* be declared IMMUTABLE, and we
should have done that in the first place, but I thought it was worth
mentioning that v8.1 did a much better job planning in this particular
case.
If my test schema will be beneficial, please let me know.
Thanks!
eric
On Apr 20, 2009, at 2:27 PM, Eric B. Ridge wrote:
Some investigation showed that the use of non-IMMUTABLE PL/PGSQL
functions as view columns, when these views are joined with other
views, cause the query to be planned poorly.
I'm sorry. I should have said VOLATILE functions. Which is the
default if nothing is specified (and that's true for 8.1 and 8.4)
eric
"Eric B. Ridge" <ebr@tcdi.com> writes:
I loaded a copy of a production database into PG 8.4b1 and immediately
saw that all of our queries were significantly slower compared to v8.1.
Some investigation showed that the use of non-IMMUTABLE PL/PGSQL
functions as view columns, when these views are joined with other
views, cause the query to be planned poorly.
I think this is due to a change that was made in 8.2:
* Do not flatten subqueries that contain volatile functions in their
target lists (Jaime Casanova)
This prevents surprising behavior due to multiple evaluation of a
volatile function (such as random() or nextval()). It might cause
performance degradation in the presence of functions that are
unnecessarily marked as volatile.
The pre-8.2 behavior was unduly optimistic about the safety of
rearranging calls to volatile functions.
regards, tom lane