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

On 12/10/2012 23:24, Tom Lane wrote:
> vaclav.juza@xitee.com writes:
>
>> 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.
>
> It's difficult to tell whether there's any real issue here beyond that.
> I sometimes get a noticeably slower plan out of HEAD, but sometimes I
> don't, if I regenerate the random table contents.  It looks to me like
> the slower plans occur when it changes the join ordering, but that's
> contingent on rowcount estimates that are equally awful in both versions;
> the lvl = (SELECT max/min(lvl) ...) conditions are not estimated well by
> any existing Postgres release.  So I'm not inclined to ascribe a lot of
> significance to the planner's choices here.
>
>             regards, tom lane
>

Hi,

I have modified the test case (it is more similar to the real-word
query), so that now it is slower on 9.2.1 than on 9.1.4 (the version
with the explicit cast):
Setup took cca 230s on my hw:
====== BEGIN TEST SETUP =================
set search_path=public, pg_catalog;

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

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

create table testtable3
(
  ida character varying (10),
  idd character varying (10),
  status character(1)
);
alter table testtable3 add constraint pk_testtable3 primary key (ida, idd);

create table testtable4
(
  ida character varying (10), idb character varying (10), idc character
varying (10),
  detail character varying (100)
);
alter table testtable4 add constraint pk_testtable4 primary key (ida,
idb, idc);

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

insert into testtable2
select
  'a' || a.a, 'bb' || b.b, 'ccc' || 5*c.c,
  (37*a.a + 53*b.b + 71*2*c.c + 101*lvl.lvl) % 512,
  'PARNAME' || p.p,
  (31*a.a + 17*b.b + 7*2*c.c + 11*lvl.lvl) % 8
from generate_series(1, 5) a, generate_series(1, 50) b,
generate_series(1, 250) c,
  generate_series(1, 6) lvl, generate_series(1, 6) p;

insert into testtable3
select
  'a' || a.a, 'dddd' || d.d,
  chr(ascii('A') + (31*a.a + 17*d.d) % 2)
from generate_series(1, 5) a, generate_series(1, 10) d;

insert into testtable4
select
  'a' || a.a, 'bb' || b.b, 'ccc' || 5*c.c,
  'some_comment' || a.a || b.b
from generate_series(1, 5) a, generate_series(1, 50) b,
generate_series(1, 250) c;

create or replace view testview as
select ida, idb, idc, idd, lvl, parname, val
from (
  SELECT tt1.ida, tt1.idb, tt1.idc, tt1.idd, tt1.lvl, tt1.parname, tt1.val
  FROM testtable1 tt1
  JOIN
    ( SELECT tt2.ida, tt2.idb, tt2.idc, tt2.lvl, tt2.parname
      FROM testtable2 tt2
        JOIN testtable4 tt4 ON tt4.ida = tt2.ida AND tt4.idb = tt2.idb
AND tt4.idc = tt2.idc
      WHERE tt2.lvl =
        ( SELECT max(tt2a.lvl) AS max
          FROM testtable2 tt2a
          WHERE tt2.ida = tt2a.ida
          AND tt2.idb = tt2a.idb
          AND tt2.idc = tt2a.idc
          AND tt2a.lvl <= 400
        )
    ) ip ON ip.ida = tt1.ida AND ip.idb = tt1.idb
    AND ip.idc = tt1.idc
  JOIN testtable3 tt3 ON tt3.ida = tt1.ida AND tt3.idd = tt1.idd
    AND tt3.status = 'A'
  WHERE tt1.lvl =
    (
      ( SELECT max(tt1a.lvl) AS max
        FROM testtable1 tt1a
        WHERE tt1.ida = tt1a.ida AND tt1.idb = tt1a.idb
          AND tt1.idc = tt1a.idc AND tt1.idd = tt1a.idd
          AND tt1.parname = tt1a.parname AND tt1a.lvl <= 400
      )
    )
) a
where (ida, idb, idc, idd)
    in
    (
      select ida, idb, idc, idd
      from
      (
        SELECT tt1.ida, tt1.idb, tt1.idc, tt1.idd, tt1.lvl, tt1.parname,
tt1.val
        FROM testtable1 tt1
        JOIN
          ( SELECT tt2.ida, tt2.idb, tt2.idc, tt2.lvl, tt2.parname
            FROM testtable2 tt2
              JOIN testtable4 tt4 ON tt4.ida = tt2.ida AND tt4.idb =
tt2.idb AND tt4.idc = tt2.idc
            WHERE tt2.lvl =
              ( SELECT max(tt2a.lvl) AS max
                FROM testtable2 tt2a
                WHERE tt2.ida = tt2a.ida
                AND tt2.idb = tt2a.idb
                AND tt2.idc = tt2a.idc
                AND tt2a.lvl <= 400
              )
          ) ip ON ip.ida = tt1.ida AND ip.idb = tt1.idb AND ip.idc = tt1.idc
        JOIN testtable3 ug ON ug.ida = tt1.ida AND ug.idd = tt1.idd AND
ug.status = 'A'
        WHERE tt1.lvl =
         (
           ( SELECT max(tt1a.lvl) AS max
             FROM testtable1 tt1a
             WHERE tt1.ida = tt1a.ida AND tt1.idb = tt1a.idb
               AND tt1.idc = tt1a.idc AND tt1.idd = tt1a.idd
               AND tt1.parname = tt1a.parname AND tt1a.lvl <= 400
           )
         )
       ) a
       where parname='PARNAME1' and val=0
    )
and idd <> 'dddd8' and UPPER(idc) LIKE UPPER('CCC5%') and ida='a4';
-- pg 9.1.4: time ~= 231s
-- pg 9.2.1: time ~= 215s
======= END TEST SETUP ==================

select * from testview;
-- pg 9.1.4 time~=2.6s cost=115295.20..119729.68
-- pg 9.2.1 time~=2.6s cost=137353.53..141869.38

DO language plpgsql $$
declare
begin
  execute '' ||
   (
     select 'CREATE OR REPLACE VIEW ' || viewname || ' AS ' ||definition
     from pg_views where schemaname='public' and viewname='testview'
   );
end;
$$

select * from testview;
-- pg 9.1.4 time~=3.8s cost=23619.47..28053.95
-- pg 9.2.1 time~=8.8s cost=309502.48..314173.61


Regards,
Vaclav Juza