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
