DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	h=X-YMail-OSG:Received:Date:From:Subject:To:MIME-Version:Content-Type:Content-Transfer-Encoding:Message-ID;
	b=iUHyv1Kix0hNqglTT/YIKUyo3gt9CqNSllkDCrzx0j2hUu2mVdXqj+qPxSXLTMXrXES/DYfXtHouWT3oJNafWBFcjk2M13je8gtKMeXrlt8g6z0sOkGAEwHPq0pjvpn3OAF6nQF22R46WiUgE1zCGJEnzd9FXCWR/Mvcunrj5R0=;
Date: Fri, 12 Jan 2007 16:31:09 -0800 (PST)
From: Mark Dobbrow <mdobbrow@yahoo.com>
Subject: Large table performance
To: pgsql-performance@postgresql.org
MIME-Version: 1.0
Content-Type: multipart/alternative; boundary="0-1465165067-1168648269=:48483"
Content-Transfer-Encoding: 8bit
Message-ID: <376735.48483.qm@web35309.mail.mud.yahoo.com>

--0-1465165067-1168648269=:48483
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit

Hello - 

I have a fairly large table (3 million records), and am fetching 10,000 non-contigous records doing a simple select on an indexed column ie 

select grades from large_table where teacher_id = X

This is a test database, so the number of records is always 10,000 and i have 300 different teacher ids.

The problem is, sometimes fetching un-cached records takes 0.5 secs and sometimes (more often) is takes more like 10.0 seconds

(fetching the same records for a given teacher_id a second time takes about 0.25 secs)

Has anyone seen similar behavior or know what the solution might be?

any help much appreciated,
Mark


ps. My shared_buffers is set at 5000 (kernal max), and work_mem=8192


--0-1465165067-1168648269=:48483
Content-Type: text/html; charset=iso-8859-1
Content-Transfer-Encoding: 8bit

Hello - <br><br>I have a fairly large table (3 million records), and am fetching 10,000 non-contigous records doing a simple select on an indexed column ie <br><br>select grades from large_table where teacher_id = X<br><br>This is a test database, so the number of records is always 10,000 and i have 300 different teacher ids.<br><br>The problem is, sometimes fetching un-cached records takes 0.5 secs and sometimes (more often) is takes more like 10.0 seconds<br><br>(fetching the same records for a given teacher_id a second time takes about 0.25 secs)<br><br>Has anyone seen similar behavior or know what the solution might be?<br><br>any help much appreciated,<br>Mark<br><br><br><br>ps. My shared_buffers is set at 5000 (kernal max), and work_mem=8192<br><br><br>
--0-1465165067-1168648269=:48483--