两个PostgreSQL 8.4版本在FreeBSD 8.2虚拟机上的巨大性能差异



我有一个关于PostgreSQL性能差异的问题。

我在MAC OS X系统上开发一个django web应用程序,该web应用程序必须部署在FreeBSD服务器上。在系统的页面上有一个ajax控制的数据输入字段。在此字段中,您可以输入城市名称,当您输入两个或两个以上字符时,系统开始在数据库中查找城市,并在字符串开头显示符合这两个(或两个以上)字符的城市下拉列表。

在我将它部署到FreeBSD服务器之前,所有这些似乎都工作得很好。第一次部署还可以,但是第二次部署显示了巨大的性能差异。

这是一个测试结果列表:

system1 proc: Intel Core 2 Duo 3.06 GHz, mem: 8GB. :
OS: OS X 10.6.8, 10.8.0 Darwin Kernel Version 10.8.0: Tue Jun  7 16:33:36 PDT 2011; 
root:xnu-1504.15.3~1/RELEASE_I386 i386
DB: PostgreSQL 8.4.5 on i386-apple-darwin10.5.0, compiled by GCC i686-apple-darwin10-gcc-4.2.1 (GCC) 4.2.1 (Apple Inc. build 5664), 64-bit
Tests system1:
query   parameter       time in ms
01      'de%'           909
02      'de%'           886
03      'den%'          132
04      'den %'         115
05      'den h%'        115
06      'den ha%'       117
07      'den haa%'      95
08      'den haag%'     100
host: system1
guest: parallels virtual machine, proc: 2 cpu, mem: 1 GB.
system2 
OS: 8.2-RELEASE FreeBSD 8.2-RELEASE #0: Thu Feb 17 02:41:51 UTC 2011     root@mason.cse.buffalo.edu:/usr/obj/usr/src/sys/GENERIC  amd64
DB: PostgreSQL 8.4.7 on amd64-portbld-freebsd8.2, compiled by GCC cc (GCC) 4.2.1 20070719  [FreeBSD], 64-bit
Tests system2:
query   parameter       time in ms
01      'de%'           1178
02      'de%'           857
03      'den%'          298
04      'den %'         233
05      'den h%'        134
06      'den ha%'       132
07      'den haa%'      132
08      'den haag%'     136
host: system1
guest: parallels virtual machine, proc: 2 cpu, mem: 1 GB.
system3
OS: 8.3-RELEASE FreeBSD 8.3-RELEASE #0: Mon Apr  9 21:23:18 UTC 2012     root@mason.cse.buffalo.edu:/usr/obj/usr/src/sys/GENERIC  amd64
DB: PostgreSQL 8.4.11 on amd64-portbld-freebsd8.3, compiled by GCC cc (GCC) 4.2.2 20070831 prerelease [FreeBSD], 64-bit
Tests system3:
query   parameter       time in ms
01      'de%'           7096
02      'de%'           7012
03      'den%'          6228
04      'den %'         6237
05      'den h%'        6145
06      'den ha%'       5640
07      'den haa%'      5512
08      'den haag%'     5561

参数模拟在ajax应用程序中输入数据的方式。这些结果来自通过pgAdmin3直接对数据库的查询,而不是通过应用程序。我没有把这个问题放在这里,因为我认为它不应该相关。数据库是相同的,并且在所有三个数据库实例上使用相同的查询。

现在我可以理解system1: OS X系统,裸机和system2:运行FreeBSD的虚拟机的性能差异了。

我不明白的是system2和system3之间的巨大性能差异,它们都是vm在同一主机下运行。测试是在每个vm单独运行的情况下完成的。

有人知道为什么会发生这种情况吗?

对于所有的系统,我先做了一个VACUUM analysis。

system1: PostgreSQL 8.4.5 on i386-apple-darwin10.5.0
"Limit  (cost=3894.65..3894.70 rows=20 width=36) (actual time=1445.667..1445.671 rows=20 loops=1)"
"  ->  Sort  (cost=3894.65..3903.99 rows=3736 width=36) (actual time=1445.666..1445.666 rows=20 loops=1)"
"        Sort Key: public.geopoint_language.name_language, 

"                                                  ->  Seq Scan on geopoint  (cost=0.00..618.94 rows=625 width=11) (actual time=0.018..6.277 rows=728 loops=1)"
"                                                        Filter: (((admin2_code)::text = ''::text) AND (country_id = 154))"
"Total runtime: 1446.377 ms"

system2: PostgreSQL 8.4.7 on amd64-portbld-freebsd8.2
"Limit  (cost=3794.26..3794.31 rows=20 width=36) (actual time=64848.595..64848.903 rows=20 loops=1)"
"  ->  Sort  (cost=3794.26..3800.41 rows=2460 width=36) (actual time=64848.583..64848.679 rows=20 loops=1)"
"        Sort Key: public.geopoint_language.name_language, adm2_state_language.name_language, adm1_state_language.name_language"
"        Sort Method:  top-N heapsort  Memory: 27kB"
"        ->  HashAggregate  (cost=3704.20..3728.80 rows=2460 width=36) (actual time=64843.710..64846.020 rows=417 loops=1)"
"              ->  Append  (cost=1055.08..3667.30 rows=2460 width=36) (actual time=1201.210..64839.849 rows=417 loops=1)"
"                    ->  Hash Semi Join  (cost=1055.08..1784.76 rows=163 width=48) (actual time=1201.199..64707.965 rows=362 loops=1)"
"                          Hash Cond: (adm1_state_language.language_id = public.language.id)"
"                          ->  Merge Join  (cost=1049.36..1776.47 rows=287 width=64) (actual time=1200.852..64703.138 rows=362 loops=1)"
"                                Merge Cond: (public.state.pstate_id = public.state.id)"
"                                ->  Nested Loop  (cost=843.55..3856.49 rows=470 width=72) (actual time=1199.848..64693.793 rows=362 loops=1)"
"                                      ->  Nested Loop  (cost=843.55..2641.98 rows=4159 width=58) (actual time=39.390..64356.535 rows=13768 loops=1)"
"                                            Join Filter: ((public.state.admin2_code)::text = (public.geopoint.admin2_code)::text)"
"                                            ->  Nested Loop Semi Join  (cost=217.62..758.39 rows=8 width=51) (actual time=38.388..146.171 rows=862 loops=1)"
"                                                  ->  Nested Loop  (cost=217.62..754.28 rows=15 width=47) (actual time=38.361..124.606 rows=862 loops=1)"
"                                                        ->  Nested Loop Semi Join  (cost=217.62..616.50 rows=20 width=34) (actual time=38.332..87.939 rows=918 loops=1)"
"                                                              ->  Merge Join  (cost=217.62..606.79 rows=35 width=30) (actual time=38.300..58.248 rows=1066 loops=1)"
"                                                                    Merge Cond: (adm1_state_language.state_id = public.state.pstate_id)"
"                                                                    ->  Index Scan using idx_state_language_1 on state_language adm1_state_language  (cost=0.00..392.25 rows=7120 width=21) (actual time=0.019..16.666 rows=2826 loops=1)"
"                                                                          Index Cond: (preferred = true)"
"                                                                    ->  Sort  (cost=196.31..196.48 rows=68 width=9) (actual time=6.551..12.945 rows=1065 loops=1)"
"                                                                          Sort Key: public.state.pstate_id"
"                                                                          Sort Method:  quicksort  Memory: 45kB"
"                                                                          ->  Seq Scan on state  (cost=0.00..194.24 rows=68 width=9) (actual time=0.019..3.928 rows=431 loops=1)"
"                                                                                Filter: (((admin2_code)::text <> ''::text) AND (country_id = 154) AND ((admin3_code)::text = ''::text) AND ((admin4_code)::text = ''::text))"
"                                                              ->  Index Scan using pk_language_all on language  (cost=0.00..0.27 rows=1 width=4) (actual time=0.011..0.011 rows=1 loops=1066)"
"                                                                    Index Cond: (public.language.id = adm1_state_language.language_id)"
"                                                                    Filter: public.language.enabled"
"                                                        ->  Index Scan using idx_state_language_1 on state_language adm2_state_language  (cost=0.00..6.87 rows=1 width=21) (actual time=0.011..0.018 rows=1 loops=918)"
"                                                              Index Cond: ((adm2_state_language.state_id = public.state.id) AND (adm2_state_language.language_id = adm1_state_language.language_id) AND (adm2_state_language.preferred = true))"
"                                                  ->  Index Scan using pk_language_all on language  (cost=0.00..0.27 rows=1 width=4) (actual time=0.008..0.008 rows=1 loops=862)"
"                                                        Index Cond: (public.language.id = adm1_state_language.language_id)"
"                                                        Filter: public.language.enabled"
"                                            ->  Materialize  (cost=625.93..695.80 rows=6987 width=12) (actual time=0.005..36.752 rows=6884 loops=862)"
"                                                  ->  Seq Scan on geopoint  (cost=0.00..618.94 rows=6987 width=12) (actual time=0.015..45.945 rows=6884 loops=1)"
"                                                        Filter: (((admin2_code)::text <> ''::text) AND (country_id = 154))"
"                                      ->  Index Scan using idx_geopoint_language_3 on geopoint_language  (cost=0.00..0.28 rows=1 width=18) (actual time=0.013..0.013 rows=0 loops=13768)"
"                                            Index Cond: ((public.geopoint_language.geopoint_id = public.geopoint.id) AND (public.geopoint_language.language_id = adm1_state_language.language_id))"
"                                            Filter: (public.geopoint_language.endonym AND ((public.geopoint_language.name_language)::text ~~* 'de%%'::text))"
"                                ->  Sort  (cost=205.72..206.42 rows=282 width=4) (actual time=0.984..3.008 rows=342 loops=1)"
"                                      Sort Key: public.state.id"
"                                      Sort Method:  quicksort  Memory: 25kB"
"                                      ->  Seq Scan on state  (cost=0.00..194.24 rows=282 width=4) (actual time=0.427..0.850 rows=12 loops=1)"
"                                            Filter: ((country_id = 154) AND ((admin2_code)::text = ''::text) AND ((admin3_code)::text = ''::text) AND ((admin4_code)::text = ''::text))"
"                          ->  Hash  (cost=5.51..5.51 rows=17 width=4) (actual time=0.235..0.235 rows=17 loops=1)"
"                                ->  Seq Scan on language  (cost=0.00..5.51 rows=17 width=4) (actual time=0.015..0.127 rows=17 loops=1)"
"                                      Filter: enabled"
"                    ->  Subquery Scan "*SELECT* 2"  (cost=1844.22..1880.91 rows=2297 width=35) (actual time=125.544..127.175 rows=55 loops=1)"
"                          ->  Merge Join  (cost=1844.22..1857.94 rows=2297 width=35) (actual time=125.533..126.563 rows=55 loops=1)"
"                                Merge Cond: ((adm1_state_language.language_id = public.geopoint_language.language_id) AND ((public.state.admin1_code)::text = (public.geopoint.admin1_code)::text))"
"                                ->  Sort  (cost=392.00..392.29 rows=117 width=26) (actual time=82.529..82.647 rows=24 loops=1)"
"                                      Sort Key: adm1_state_language.language_id, public.state.admin1_code"
"                                      Sort Method:  quicksort  Memory: 26kB"
"                                      ->  Hash Join  (cost=209.42..387.98 rows=117 width=26) (actual time=30.234..82.289 rows=25 loops=1)"
"                                            Hash Cond: (adm1_state_language.language_id = public.language.id)"
"                                            ->  Hash Semi Join  (cost=203.49..380.10 rows=206 width=22) (actual time=29.732..81.508 rows=25 loops=1)"
"                                                  Hash Cond: (adm1_state_language.language_id = public.language.id)"
"                                                  ->  Hash Join  (cost=197.77..371.13 rows=364 width=18) (actual time=29.442..80.926 rows=29 loops=1)"
"                                                        Hash Cond: (adm1_state_language.state_id = public.state.id)"
"                                                        ->  Seq Scan on state_language adm1_state_language  (cost=0.00..143.03 rows=7120 width=21) (actual time=0.008..39.728 rows=7120 loops=1)"
"                                                              Filter: preferred"
"                                                        ->  Hash  (cost=194.24..194.24 rows=282 width=5) (actual time=2.366..2.366 rows=12 loops=1)"
"                                                              ->  Seq Scan on state  (cost=0.00..194.24 rows=282 width=5) (actual time=1.185..2.271 rows=12 loops=1)"
"                                                                    Filter: (((admin2_code)::text = ''::text) AND ((admin3_code)::text = ''::text) AND ((admin4_code)::text = ''::text) AND (country_id = 154))"
"                                                  ->  Hash  (cost=5.51..5.51 rows=17 width=4) (actual time=0.224..0.224 rows=17 loops=1)"
"                                                        ->  Seq Scan on language  (cost=0.00..5.51 rows=17 width=4) (actual time=0.008..0.118 rows=17 loops=1)"
"                                                              Filter: enabled"
"                                            ->  Hash  (cost=5.72..5.72 rows=17 width=4) (actual time=0.445..0.445 rows=17 loops=1)"
"                                                  ->  HashAggregate  (cost=5.55..5.72 rows=17 width=4) (actual time=0.233..0.323 rows=17 loops=1)"
"                                                        ->  Seq Scan on language  (cost=0.00..5.51 rows=17 width=4) (actual time=0.010..0.125 rows=17 loops=1)"
"                                                              Filter: enabled"
"                                ->  Sort  (cost=1452.22..1453.21 rows=396 width=25) (actual time=42.856..43.140 rows=55 loops=1)"
"                                      Sort Key: public.geopoint_language.language_id, public.geopoint.admin1_code"
"                                      Sort Method:  quicksort  Memory: 29kB"
"                                      ->  Hash Join  (cost=626.75..1435.14 rows=396 width=25) (actual time=23.266..42.430 rows=55 loops=1)"
"                                            Hash Cond: (public.geopoint_language.geopoint_id = public.geopoint.id)"
"                                            ->  Seq Scan on geopoint_language  (cost=0.00..802.94 rows=396 width=18) (actual time=3.081..25.949 rows=515 loops=1)"
"                                                  Filter: (endonym AND ((name_language)::text ~~* 'de %%'::text))"
"                                            ->  Hash  (cost=618.94..618.94 rows=625 width=11) (actual time=13.194..13.194 rows=728 loops=1)"
"                                                  ->  Seq Scan on geopoint  (cost=0.00..618.94 rows=625 width=11) (actual time=0.015..8.770 rows=728 loops=1)"
"                                                        Filter: (((admin2_code)::text = ''::text) AND (country_id = 154))"
"Total runtime: 64850.211 ms"

system3: PostgreSQL 8.4.11 on amd64-portbld-freebsd8.3
"Limit  (cost=2715.73..2715.78 rows=20 width=35) (actual time=28363.433..28363.721 rows=20 loops=1)"
"  ->  Sort  (cost=2715.73..2716.02 rows=113 width=35) (actual time=28363.421..28363.516 rows=20 loops=1)"
"        Sort Key: public.geopoint_language.name_language, adm2_state_language.name_language, adm1_state_language.name_language"
"        Sort Method:  top-N heapsort  Memory: 27kB"
"        ->  HashAggregate  (cost=2711.60..2712.73 rows=113 width=35) (actual time=28358.553..28360.849 rows=419 loops=1)"
"              ->  Append  (cost=571.63..2709.90 rows=113 width=35) (actual time=349.502..28354.791 rows=419 loops=1)"
"                    ->  Hash Semi Join  (cost=571.63..884.24 rows=3 width=48) (actual time=349.492..28233.388 rows=362 loops=1)"
"                          Hash Cond: (adm1_state_language.language_id = public.language.id)"
"                          ->  Nested Loop  (cost=565.91..878.47 rows=5 width=64) (actual time=349.172..28228.620 rows=362 loops=1)"
"                                Join Filter: ((public.state.admin2_code)::text = (public.geopoint.admin2_code)::text)"
"                                ->  Merge Join  (cost=565.91..811.16 rows=25 width=61) (actual time=278.147..20849.826 rows=292218 loops=1)"
"                                      Merge Cond: (public.state.pstate_id = adm1_state_language.state_id)"
"                                      Join Filter: (adm1_state_language.language_id = adm2_state_language.language_id)"
"                                      ->  Nested Loop  (cost=233.49..1199.63 rows=277 width=44) (actual time=12.014..10783.473 rows=292218 loops=1)"
"                                            ->  Nested Loop  (cost=233.49..725.31 rows=1 width=26) (actual time=7.617..42.736 rows=862 loops=1)"
"                                                  ->  Merge Join  (cost=233.49..720.62 rows=1 width=13) (actual time=7.585..16.639 rows=431 loops=1)"
"                                                        Merge Cond: (public.state.id = public.state.pstate_id)"
"                                                        ->  Index Scan using pk_state on state  (cost=0.00..523.51 rows=282 width=4) (actual time=1.000..2.268 rows=12 loops=1)"
"                                                              Filter: ((country_id = 154) AND ((admin2_code)::text = ''::text) AND ((admin3_code)::text = ''::text) AND ((admin4_code)::text = ''::text))"
"                                                        ->  Sort  (cost=196.31..196.48 rows=68 width=9) (actual time=6.566..9.020 rows=431 loops=1)"
"                                                              Sort Key: public.state.pstate_id"
"                                                              Sort Method:  quicksort  Memory: 45kB"
"                                                              ->  Seq Scan on state  (cost=0.00..194.24 rows=68 width=9) (actual time=0.015..3.942 rows=431 loops=1)"
"                                                                    Filter: (((admin2_code)::text <> ''::text) AND (country_id = 154) AND ((admin3_code)::text = ''::text) AND ((admin4_code)::text = ''::text))"
"                                                  ->  Index Scan using idx_state_language_1 on state_language adm2_state_language  (cost=0.00..4.68 rows=1 width=21) (actual time=0.012..0.026 rows=2 loops=431)"
"                                                        Index Cond: ((adm2_state_language.state_id = public.state.id) AND (adm2_state_language.preferred = true))"
"                                            ->  Index Scan using idx_geopoint_language_3 on geopoint_language  (cost=0.00..471.76 rows=205 width=18) (actual time=2.051..8.854 rows=339 loops=862)"
"                                                  Index Cond: (public.geopoint_language.language_id = adm2_state_language.language_id)"
"                                                  Filter: (public.geopoint_language.endonym AND ((public.geopoint_language.name_language)::text ~~* 'de%%'::text))"
"                                      ->  Sort  (cost=331.82..337.54 rows=2286 width=29) (actual time=237.631..3511.098 rows=625149 loops=1)"
"                                            Sort Key: adm1_state_language.state_id"
"                                            Sort Method:  quicksort  Memory: 746kB"
"                                            ->  Hash Join  (cost=11.87..204.28 rows=2286 width=29) (actual time=0.996..192.413 rows=7023 loops=1)"
"                                                  Hash Cond: (adm1_state_language.language_id = public.language.id)"
"                                                  ->  Hash Join  (cost=5.94..180.49 rows=4035 width=25) (actual time=0.514..116.195 rows=7023 loops=1)"
"                                                        Hash Cond: (adm1_state_language.language_id = public.language.id)"
"                                                        ->  Seq Scan on state_language adm1_state_language  (cost=0.00..143.03 rows=7120 width=21) (actual time=0.007..38.638 rows=7120 loops=1)"
"                                                              Filter: preferred"
"                                                        ->  Hash  (cost=5.72..5.72 rows=17 width=4) (actual time=0.452..0.452 rows=17 loops=1)"
"                                                              ->  HashAggregate  (cost=5.55..5.72 rows=17 width=4) (actual time=0.260..0.350 rows=17 loops=1)"
"                                                                    ->  Seq Scan on language  (cost=0.00..5.51 rows=17 width=4) (actual time=0.008..0.136 rows=17 loops=1)"
"                                                                          Filter: enabled"
"                                                  ->  Hash  (cost=5.72..5.72 rows=17 width=4) (actual time=0.425..0.425 rows=17 loops=1)"
"                                                        ->  HashAggregate  (cost=5.55..5.72 rows=17 width=4) (actual time=0.231..0.322 rows=17 loops=1)"
"                                                              ->  Seq Scan on language  (cost=0.00..5.51 rows=17 width=4) (actual time=0.010..0.118 rows=17 loops=1)"
"                                                                    Filter: enabled"
"                                ->  Index Scan using pk_city on geopoint  (cost=0.00..2.68 rows=1 width=12) (actual time=0.008..0.011 rows=1 loops=292218)"
"                                      Index Cond: (public.geopoint.id = public.geopoint_language.geopoint_id)"
"                                      Filter: (((public.geopoint.admin2_code)::text <> ''::text) AND (public.geopoint.country_id = 154))"
"                          ->  Hash  (cost=5.51..5.51 rows=17 width=4) (actual time=0.230..0.230 rows=17 loops=1)"
"                                ->  Seq Scan on language  (cost=0.00..5.51 rows=17 width=4) (actual time=0.014..0.124 rows=17 loops=1)"
"                                      Filter: enabled"
"                    ->  Subquery Scan "*SELECT* 2"  (cost=1014.04..1825.63 rows=110 width=35) (actual time=100.899..116.913 rows=57 loops=1)"
"                          ->  Hash Join  (cost=1014.04..1824.53 rows=110 width=35) (actual time=100.889..116.323 rows=57 loops=1)"
"                                Hash Cond: (((public.geopoint.admin1_code)::text = (public.state.admin1_code)::text) AND (public.geopoint_language.language_id = adm1_state_language.language_id))"
"                                ->  Hash Join  (cost=626.75..1432.19 rows=19 width=25) (actual time=19.280..34.017 rows=57 loops=1)"
"                                      Hash Cond: (public.geopoint_language.geopoint_id = public.geopoint.id)"
"                                      ->  Seq Scan on geopoint_language  (cost=0.00..802.94 rows=616 width=18) (actual time=1.942..16.204 rows=720 loops=1)"
"                                            Filter: (endonym AND ((name_language)::text ~~* 'de%%'::text))"
"                                      ->  Hash  (cost=618.94..618.94 rows=625 width=11) (actual time=13.437..13.437 rows=728 loops=1)"
"                                            ->  Seq Scan on geopoint  (cost=0.00..618.94 rows=625 width=11) (actual time=0.012..8.923 rows=728 loops=1)"
"                                                  Filter: (((admin2_code)::text = ''::text) AND (country_id = 154))"
"                                ->  Hash  (cost=385.53..385.53 rows=117 width=26) (actual time=81.568..81.568 rows=25 loops=1)"
"                                      ->  Hash Join  (cost=209.64..385.53 rows=117 width=26) (actual time=29.986..81.410 rows=25 loops=1)"
"                                            Hash Cond: (adm1_state_language.language_id = public.language.id)"
"                                            ->  Hash Join  (cost=203.70..378.69 rows=206 width=22) (actual time=29.457..80.612 rows=25 loops=1)"
"                                                  Hash Cond: (adm1_state_language.language_id = public.language.id)"
"                                                  ->  Hash Join  (cost=197.77..371.13 rows=364 width=18) (actual time=29.011..79.869 rows=29 loops=1)"
"                                                        Hash Cond: (adm1_state_language.state_id = public.state.id)"
"                                                        ->  Seq Scan on state_language adm1_state_language  (cost=0.00..143.03 rows=7120 width=21) (actual time=0.008..38.849 rows=7120 loops=1)"
"                                                              Filter: preferred"
"                                                        ->  Hash  (cost=194.24..194.24 rows=282 width=5) (actual time=2.245..2.245 rows=12 loops=1)"
"                                                              ->  Seq Scan on state  (cost=0.00..194.24 rows=282 width=5) (actual time=1.139..2.167 rows=12 loops=1)"
"                                                                    Filter: (((admin2_code)::text = ''::text) AND ((admin3_code)::text = ''::text) AND ((admin4_code)::text = ''::text) AND (country_id = 154))"
"                                                  ->  Hash  (cost=5.72..5.72 rows=17 width=4) (actual time=0.423..0.423 rows=17 loops=1)"
"                                                        ->  HashAggregate  (cost=5.55..5.72 rows=17 width=4) (actual time=0.226..0.317 rows=17 loops=1)"
"                                                              ->  Seq Scan on language  (cost=0.00..5.51 rows=17 width=4) (actual time=0.008..0.119 rows=17 loops=1)"
"                                                                    Filter: enabled"
"                                            ->  Hash  (cost=5.72..5.72 rows=17 width=4) (actual time=0.449..0.449 rows=17 loops=1)"
"                                                  ->  HashAggregate  (cost=5.55..5.72 rows=17 width=4) (actual time=0.254..0.344 rows=17 loops=1)"
"                                                        ->  Seq Scan on language  (cost=0.00..5.51 rows=17 width=4) (actual time=0.010..0.119 rows=17 loops=1)"
"                                                              Filter: enabled"
"Total runtime: 28366.784 ms"

system2和system3显示的时间对我来说没有任何意义。

我在explain.depesz.com: system2, system3中对您的两个解释方案提出了质疑,以便于理解和解释。

现在这些计划是相当大的,所以在两个不同版本的postgresql下执行它们是不同的也就不足为奇了。即使是补丁发布也会对计划产生影响,在一个地方修复错误或进行小的优化也会改变另一个地方的决定。

令我吃惊的是,system3计划在执行中许多步骤的估计行基数与实际行基数之间存在明显差异。例如,第7行和第8行实际有292K行,但估计有25行和277行;第16行,625K vs 2286。相比之下,system2没有这样严重错误估计的计划线,在相当适度的总数中,有一些计划线被遗漏了一个数量级,但是没有任何计划线被遗漏了几个数量级,从而产生了100K+范围内的行基数。

这些差异可能表明您在system3上有不正确的统计数据,这导致pg实例选择效率较低的计划,因为它有关于其选择成本的不准确数据。您可能想尝试重新分析所有表,并可能增加统计数据目标。

您是否验证了每个系统上的数据是相同的?还要检查两个系统上的所有查询规划设置是否相同。

我也想知道为什么你甚至要麻烦这么老版本的postgres。8.4已经有很多年的历史了,从8.4到9.2在特性、性能和并发性方面有了许多实质性的改进。花费大量精力调优一个几乎过时的postgres版本,而不是瞄准最新的稳定版本,这似乎是愚蠢的,除非出于其他令人信服的原因,您必须使用它。

最新更新