Thread: [PERFORM] Performance degradation from PostgreSQL 8.2.21 to PostgreSQL 9.3.2
[PERFORM] Performance degradation from PostgreSQL 8.2.21 to PostgreSQL 9.3.2
From
fx TATEISHI KOJI
Date:
Issuing exactly the same query as PostgreSQL 8.2.21 and PostgreSQL 9.3.2 will slow the response by 6.4 ms on average. What could be the cause? Measurement method is as follows. ・ PostgreSQL 8.2.21 installation ★Measurement ・ Export DUMP of PostgreSQL 8.2.21 ・ PostgreSQL 8.2.21 uninstallation ・ PostgreSQL 9.3.2 installation ・ Dump import ★Measurement [query] select table4.a as col_0_0_, table4.a as col_1_0_, table4.a as col_2_0_, table4.b as col_0_1_, table4.c, table4.d from table1, table2, table3, table4 where table1.a=table2.a and table1.a="parameter$1" and table2.roleid=table3.roleid and table3.a="parameter$2" and table4.b='3' and table2.a=table4.a;
[PERFORM] Re: Performance degradation from PostgreSQL 8.2.21 to PostgreSQL9.3.2
From
Albe Laurenz
Date:
fx TATEISHI KOJI wrote: > Issuing exactly the same query as PostgreSQL 8.2.21 and PostgreSQL 9.3.2 will slow the > response by 6.4 ms on average. > What could be the cause? > Measurement method is as follows. > ・ PostgreSQL 8.2.21 installation > ★Measurement > ・ Export DUMP of PostgreSQL 8.2.21 > ・ PostgreSQL 8.2.21 uninstallation > ・ PostgreSQL 9.3.2 installation > ・ Dump import > ★Measurement It is impossible to answer this with certainty without EXPLAIN (ANALYZE, BUFFERS) output, but my first guess is that the statistics on the 9.3 installation are not up to date. ANALYZE all involved tables, then try again and see if the performance degradation has vanished. If not, start studying the execution plans. Are the parameters in postgresql.conf set the same? Yours, Laurenz Albe