BUG #7598: Loss of view performance after dump/restore of the view definition

The following bug has been logged on the website:

Bug reference: 7598
Logged by: Vaclav Juza
Email address: vaclav(dot)juza(at)xitee(dot)com
PostgreSQL version: 9.2.1
Operating system: Linux 2.6.18-128.el5 x86_64 (RHEL 5.3)
Description:

Hi,
when a view (with the below properites) is dump and restored (no matter if
using pg_dump, copied from pg_admin or using pg_views) it's performance is
worse than before. The view was using tables with columns of type "character
varying(xx)" and the dump inserts "::text" casts into the join conditions on
these columns.
In the real case we faced this problem, the performance loss was much higher
on PostgreSQL 9.2.1 (3 seconds vs. 3 minutes) than on 9.1.4 (1.3 seconds vs.
7 seconds) and both variants were slower on 9.2.1 than on 9.1.4. In the test
case below the behaviour is similar on both Postgres version.

The testcase was created in a way that it has similar constructs as the
real-word case.

The testcase is initialized with the following (on our hardware it runs cca
1 minute):
======== TEST SETUP =======
set search_path=public, pg_catalog;

create table testtable
(
ida character varying (10), idb character varying (10), idc character
varying (10),
lvl numeric, val numeric
);
alter table testtable add constraint pk_testtable primary key (ida, idb,
idc, lvl);

create table testtable2
(
ida character varying (10), idb character varying (10), idc character
varying (10),
idd character varying (10),
lvl numeric, val numeric
);
alter table testtable2 add constraint pk_testtable2 primary key (ida, idb,
idc, idd, lvl);

insert into testtable
select
'a' || a.a, 'bb' || b.b, 'ccc' || c.c,
(37*a.a + 53*b.b + 71*c.c + 101*lvl.lvl) % 512,
( 31*a.a + 17*b.b + 7*c.c + 11*lvl.lvl ) % 16
from
generate_series(1, 5) a, generate_series(1, 50) b, generate_series(1, 500)
c,
generate_series(1, 9) lvl;

insert into testtable2
select
'a' || a.a, 'bb' || b.b, 'ccc' || 5*c.c, 'dddd' || d.d,
(37*a.a + 53*b.b + 71*5*c.c + 101*3*lvl.lvl) % 512,
(31*a.a + 17*b.b + 7*5*c.c + 11*3*lvl.lvl) % 3
from generate_series(1, 5) a, generate_series(1, 50) b, generate_series(1,
100) c,
generate_series(1, 10) d,
generate_series(1, 3) lvl;

create or replace view testview as
select t1.ida, t1.idb, t1.idc, t1.lvl, t1.val
from testtable t1
join testtable2 t6
on t6.ida=t1.ida and t6.idb=t1.idb and t6.idc=t1.idc and
t6.idd='dddd1'
and t6.lvl=
(
SELECT max(t7.lvl)
from testtable2 t7
where t7.ida=t6.ida and t7.idb=t6.idb and t7.idc=t6.idc
and t7.idd=t6.idd and t7.lvl<300
)
where t1.lvl=
(
SELECT max(t2.lvl)
from testtable t2
where t2.ida=t1.ida and t2.idb=t1.idb and t2.idc=t1.idc and t2.lvl<300
)
and (t1.ida, t1.idb, t1.idc) in
( select t3.ida, t3.idb, t3.idc
from testtable2 t3
join testtable t5
on t5.ida=t3.ida and t5.idb=t3.idb and t5.idc=t3.idc
where t3.lvl=
(
SELECT min(t4.lvl)
from testtable2 t4
where t4.ida=t3.ida and t4.idb=t3.idb and t4.idc=t3.idc and
t4.idd=t3.idd
and t4.lvl<300
)
and t3.idd='dddd8' and t3.val=0
)
;

==== END TEST SETUP =======

The following query:
select * from testview where ida='a4';
has the following performance on our hardware:
-- pg 9.2.1: time~=1.2s, cost=119222.86..123174.62
-- pg 9.1.4: time~=1.1s, cost=105848.75..112083.82

After recreating the view from dump or simplier from pg_views:
DO language plpgsql $$
declare
begin
execute ''::text ||
(
select 'CREATE OR REPLACE VIEW ' || viewname || ' AS ' ||definition
from pg_views where schemaname='public' and viewname='testview'
);
end;
$$

the same query
select * from testview where ida='a4';
on the same hardware has the following performance:
-- pg 9.2.1: time~=2.5s, cost=578843.62..587364.78
-- pg 9.1.4: time~=2.5s, cost=513879.12..521655.37

Expected:
The performance and execution plan of the query should be the same when the
view is dumped and restored.

Regards,
Vaclav Juza