BUG #7598: Loss of view performance after dump/restore of the view definition - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      7598
Logged by:          Vaclav Juza
Email address:      vaclav.juza@xitee.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):
=3D=3D=3D=3D=3D=3D=3D=3D TEST SETUP =3D=3D=3D=3D=3D=3D=3D
set search_path=3Dpublic, 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=3Dt1.ida and t6.idb=3Dt1.idb and t6.idc=3Dt1.idc and
t6.idd=3D'dddd1'
         and t6.lvl=3D
           (
              SELECT max(t7.lvl)
              from testtable2 t7
              where t7.ida=3Dt6.ida and t7.idb=3Dt6.idb and t7.idc=3Dt6.idc
                and t7.idd=3Dt6.idd and t7.lvl<300
           )
where t1.lvl=3D
  (
    SELECT max(t2.lvl)
    from testtable t2
    where t2.ida=3Dt1.ida and t2.idb=3Dt1.idb and t2.idc=3Dt1.idc and t2.lv=
l<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=3Dt3.ida and t5.idb=3Dt3.idb and t5.idc=3Dt3.idc
    where t3.lvl=3D
    (
      SELECT min(t4.lvl)
      from testtable2 t4
      where t4.ida=3Dt3.ida and t4.idb=3Dt3.idb and t4.idc=3Dt3.idc and
t4.idd=3Dt3.idd
       and t4.lvl<300
    )
    and t3.idd=3D'dddd8' and t3.val=3D0
  )
;

=3D=3D=3D=3D END TEST SETUP =3D=3D=3D=3D=3D=3D=3D

The following query:
  select * from testview where ida=3D'a4';
has the following performance on our hardware:
-- pg 9.2.1: time~=3D1.2s, cost=3D119222.86..123174.62
-- pg 9.1.4: time~=3D1.1s, cost=3D105848.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=3D'public' and viewname=3D'testview'
     );
  end;
  $$

the same query
  select * from testview where ida=3D'a4';
on the same hardware has the following performance:
-- pg 9.2.1: time~=3D2.5s, cost=3D578843.62..587364.78
-- pg 9.1.4: time~=3D2.5s, cost=3D513879.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