The following bug has been logged on the website: Bug reference: 7598 Logged by: Vaclav Juza Email address: vaclav.j...@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): ======== 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 -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs