Re: Same SQL, 104296ms of difference between 7.4.12 and

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(dot)m(dot)guerrero(at)usit(dot)uio(dot)no>
Center for Information Technology Services
University of Oslo, Norway

PGP Public Key: http://folk.uio.no/rafael/