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

On 12/10/2012 23:24, Tom Lane wrote:
> vaclav(dot)juza(at)xitee(dot)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