On Fri, 2006-04-07 at 15:31, Richard Huxton wrote: > Rafael Martinez Guerrero wrote: > > Hello > > > > I have a sql statement that takes 108489.780 ms with 8.0.7 in a > > RHEL4/amd64linux server with 2xAMD Opteron(tm) Processor 275 2.00GHz / > > 8GB RAM and only 4193.588 ms with 7.4.12 in a RHEL3/386linux server with > > 2xIntel(R) Xeon(TM) CPU 2.40GHz / 4GB RAM. > > > > Some information: > > > > - There is no IO when I am running the sql, but it uses 99% of the cpu. > > - I run VACUUM VERBOSE ANALYZE in both databases before the test. > > - The databases are identical. > > - No other jobs running when testing. > > - Some different parameters between 7.4.12 and 8.0.7 : > > > > 7.4.12: > > ------- > > shared_buffers = 114966 #(15% of ram) > > sort_mem = 16384 > > vacuum_mem = 524288 > > wal_buffers = 64 > > checkpoint_segments = 16 > > effective_cache_size = 383220 #(50% ram) > > random_page_cost = 3 > > default_statistics_target = 100 > > > > 8.0.7: > > ------ > > shared_buffers = 250160 #(25% ram) > > work_mem = 8192 > > maintenance_work_mem = 131072 > > wal_buffers = 128 > > checkpoint_segments = 64 > > effective_cache_size = 500321 #(50% ram) > > random_page_cost = 3 > > default_statistics_target = 100 > > > > Any ideas of what I can test/configurate to find out why this happens? > > Thanks in advance. > > I haven't looked in detail at the plans, but what stands out to me is > that you've got a sort with a lot of columns and you've halved sort_mem > (work_mem). Try increasing it (perhaps to 32000 even). > set work_mem = 32000; > > Give that a quick go and see what happens. If it doesn't work, we'll > look at the plans in more detail. I know that this SQL could be done in a much better way, but I can not change it at the moment. work_mem = 16384: ----------------- After restarting the database and running the explain two times: 107911.229 ms work_mem = 32768: ----------------- After restarting the database and running the explain two times: 103988.337 ms -- Rafael Martinez, <r.m.guerrero@xxxxxxxxxxx> Center for Information Technology Services University of Oslo, Norway PGP Public Key: http://folk.uio.no/rafael/
