Thread: 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

"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
markedas volatile.
 

The pre-8.2 behavior was unduly optimistic about the safety of
rearranging calls to volatile functions.
        regards, tom lane

On Apr 22, 2009, at 10:47 PM, Tom Lane wrote:

> I think this is due to a change that was made in 8.2:

Cool.  Thanks for the followup!

eric