Subject: Re: Same SQL, 104296ms of difference between 7.4.12 and
From: Rafael Martinez Guerrero <r.m.guerrero@usit.uio.no>
To: Richard Huxton <dev@archonet.com>
Cc: pgsql-performance@postgresql.org
In-Reply-To: <443669A5.2000603@archonet.com>
References: <1144413933.24060.253.camel@bbking.uio.no>
	<443669A5.2000603@archonet.com>
Content-Type: text/plain
Organization: University of Oslo
Message-Id: <1144418444.24060.269.camel@bbking.uio.no>
Mime-Version: 1.0
Date: Fri, 07 Apr 2006 16:00:44 +0200
Content-Transfer-Encoding: 7bit

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@usit.uio.no>
Center for Information Technology Services
University of Oslo, Norway

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