Received: from localhost (maia-5.hub.org [200.46.204.182])
	by postgresql.org (Postfix) with ESMTP id ADE7F9FB31C
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Sun, 14 Jan 2007 15:02:38 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.182]) (amavisd-new, port 10024)
	with ESMTP id 98923-08 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Sun, 14 Jan 2007 15:02:36 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.4
Received: from exprod6og50.obsmtp.com (exprod6og50.obsmtp.com [64.18.1.181])
	by postgresql.org (Postfix) with SMTP id 75C9F9FB26E
	for <pgsql-performance@postgresql.org>;
	Sun, 14 Jan 2007 15:02:37 -0400 (AST)
Received: from source ([63.240.6.46]) (using TLSv1) by exprod6ob50.postini.com
	([64.18.5.12]) with SMTP; Sun, 14 Jan 2007 11:02:35 PST
Received: from 172.16.1.114 by mail.mi8.com with ESMTP (- Welcome to Mi8
	Corporation www.Mi8.com (D2)); Sun, 14 Jan 2007 14:02:29 -0500
X-Server-Uuid: 7829E76E-BB9E-4995-8473-3C0929DF7DD1
Received: from MI8NYCMAIL08.Mi8.com ([172.16.1.164]) by
	d01smtp07.Mi8.com with Microsoft SMTPSVC(6.0.3790.1830); Sun, 14 Jan
	2007 14:02:26 -0500
Received: from 69.181.100.71 ([69.181.100.71]) by MI8NYCMAIL08.Mi8.com (
	[172.16.1.190]) via Exchange Front-End Server mi8owa.mi8.com (
	[172.16.1.106]) with Microsoft Exchange Server HTTP-DAV ; Sun, 14 Jan
	2007 19:02:26 +0000
User-Agent: Microsoft-Entourage/11.3.2.061213
Date: Sun, 14 Jan 2007 11:02:24 -0800
Subject: Re: Large table performance
From: "Luke Lonergan" <llonergan@greenplum.com>
To: "Mark Dobbrow" <mdobbrow@yahoo.com>,
	pgsql-performance@postgresql.org
Message-ID: <C1CFBE40.1805B%llonergan@greenplum.com>
Thread-Topic: [PERFORM] Large table performance
Thread-Index: Acc4DoZ3xPs5V6QBEdu0xAAWy4o9DA==
In-Reply-To: <376735.48483.qm@web35309.mail.mud.yahoo.com>
MIME-Version: 1.0
X-OriginalArrivalTime: 14 Jan 2007 19:02:26.0955 (UTC)
	FILETIME=[883A05B0:01C7380E]
X-WSS-ID: 69B4A1CF3Q08293120-01-01
Content-Type: multipart/alternative;
 boundary=B_3251617344_567864
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200701/225
X-Sequence-Number: 22826

> This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.

--B_3251617344_567864
Content-Type: text/plain;
 charset=us-ascii
Content-Transfer-Encoding: 7bit

Mark,

This behavior likely depends on how the data is loaded into the DBMS.  If
the records you are fetching are distributed widely among the 3M records on
disk, then 


On 1/12/07 4:31 PM, "Mark Dobbrow" <mdobbrow@yahoo.com> wrote:

> 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
> 
> 
> 



--B_3251617344_567864
Content-Type: text/html;
 charset=us-ascii
Content-Transfer-Encoding: quoted-printable

<HTML>
<HEAD>
<TITLE>Re: [PERFORM] Large table performance</TITLE>
</HEAD>
<BODY>
<FONT FACE=3D"Verdana, Helvetica, Arial"><SPAN STYLE=3D'font-size:14.0px'>Mark,=
<BR>
<BR>
This behavior likely depends on how the data is loaded into the DBMS. &nbsp=
;If the records you are fetching are distributed widely among the 3M records=
 on disk, then <BR>
<BR>
<BR>
On 1/12/07 4:31 PM, &quot;Mark Dobbrow&quot; &lt;mdobbrow@yahoo.com&gt; wro=
te:<BR>
<BR>
</SPAN></FONT><BLOCKQUOTE><FONT FACE=3D"Verdana, Helvetica, Arial"><SPAN STYL=
E=3D'font-size:14.0px'>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 =3D X<BR>
<BR>
This is a test database, so the number of records is always 10,000 and i ha=
ve 300 different teacher ids.<BR>
<BR>
The problem is, sometimes fetching un-cached records takes 0.5 secs and som=
etimes (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=3D8192<BR>
<BR>
<BR>
<BR>
</SPAN></FONT></BLOCKQUOTE><FONT FACE=3D"Verdana, Helvetica, Arial"><SPAN STY=
LE=3D'font-size:14.0px'><BR>
</SPAN></FONT>
</BODY>
</HTML>


--B_3251617344_567864--