8.4b1 regression?

From: "Eric B(dot) Ridge" <ebr(at)tcdi(dot)com>
To: hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: 8.4b1 regression?
Date: 2009-04-20 18:27:44
Message-ID: 548D756F-91C3-4F91-9F8C-5BC3114BD9C4@tcdi.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

Attachment Content-Type Size
slow.txt text/plain 2.5 KB
fast.txt text/plain 1.9 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Eric B. Ridge 2009-04-20 18:32:49 Re: 8.4b1 regression?
Previous Message Pavel Stehule 2009-04-20 16:56:47 Re: Patch for 8.5, transformationHook