User-Agent: Microsoft-Entourage/11.3.2.061213
Date: Sun, 14 Jan 2007 11:20:35 -0800
Subject: Re: Large table performance
From: "Luke Lonergan" <llonergan@greenplum.com>
To: "Mark Dobbrow" <mdobbrow@yahoo.com>,
	pgsql-performance@postgresql.org
Message-ID: <C1CFC283.18064%llonergan@greenplum.com>
Thread-Topic: [PERFORM] Large table performance
Thread-Index: Acc4ERDATzwxj6QEEdu0xAAWy4o9DA==
In-Reply-To: <376735.48483.qm@web35309.mail.mud.yahoo.com>
MIME-Version: 1.0
Content-Type: multipart/alternative;
 boundary=B_3251618436_675815

> 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_3251618436_675815
Content-Type: text/plain;
 charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable

Mark,

Note that selecting an index column means that Postgres fetches the whole
rows from disk.  I think your performance problem is either: 1) slow disk o=
r
2) index access of distributed data.  If it=B9s (1), there are plenty of
references from this list on how to check for that and fix it.  If it=B9s (2)=
,
see below.=20

The performance of index accessed data in Postgres 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 the select is going to =B3hop, skip
and jump=B2 across the disk to get the records you need.  If those records ar=
e
stored more closely together, then the fetching from disk is going to be
sequential.  A good example of the best situation for an index is an index
on a date column when the data is loaded sequentially by date.  A query
against a specific date range will result in an ordered fetch from the disk=
,
which leverages fast sequential access.

The difference in performance between ordered and distributed access is
similar to the difference between =B3random seek=B2 and =B3sequential=B2 performanc=
e
of the disk subsystem.  The random seek performance of typical disk
subsystems with one thread (for one user in postgres) is 120 seeks per
second.  If your data was randomly distributed, you=B9d expect about
10,000/120 =3D 83 seconds to gather these records.  Since you=B9re getting 10
seconds, I expect that your data is lumped into groups and you are getting =
a
mix of sequential reads and seeks.

Note that adding more disks into a RAID does not help the random seek
performance within Postgres, but may linearly improve the ordered access
speed.  So even with 44 disks in a RAID10 pool on a Sun X4500, the seek
performance of Postgres (and other DBMS=B9s without async or threaded I/O) is
that of a single disk =AD 120 seeks per second.  Adding more users allows the
seeks to scale on such a machine as users are added, up to the number of
disks in the RAID.  But for your one user example =AD no help.

If your problem is (2), you can re-order the data on disk by using a CREATE
TABLE statement like this:
  CREATE TABLE fast_table AS SELECT * FROM slow_table ORDER BY teacher_id;
  CREATE INDEX teacher_id_ix ON fast_table;
  VACUUM ANALYZE fast_table;

You should then see ordered access when you do index scans on teacher_id.

- Luke


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

> Hello -=20
>=20
> 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
>=20
> select grades from large_table where teacher_id =3D X
>=20
> This is a test database, so the number of records is always 10,000 and i =
have
> 300 different teacher ids.
>=20
> The problem is, sometimes fetching un-cached records takes 0.5 secs and
> sometimes (more often) is takes more like 10.0 seconds
>=20
> (fetching the same records for a given teacher_id a second time takes abo=
ut
> 0.25 secs)
>=20
> Has anyone seen similar behavior or know what the solution might be?
>=20
> any help much appreciated,
> Mark
>=20
>=20
>=20
> ps. My shared_buffers is set at 5000 (kernal max), and work_mem=3D8192
>=20
>=20
>=20


--B_3251618436_675815
Content-Type: text/html;
 charset=iso-8859-1
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>
Note that selecting an index column means that Postgres fetches the whole r=
ows from disk. &nbsp;I think your performance problem is either: 1) slow dis=
k or 2) index access of distributed data. &nbsp;If it&#8217;s (1), there are=
 plenty of references from this list on how to check for that and fix it. &n=
bsp;If it&#8217;s (2), see below. <BR>
<BR>
The performance of index accessed data in Postgres depends on how the data =
is loaded into the DBMS. &nbsp;If the records you are fetching are distribut=
ed widely among the 3M records on disk, then the select is going to &#8220;h=
op, skip and jump&#8221; across the disk to get the records you need. &nbsp;=
If those records are stored more closely together, then the fetching from di=
sk is going to be sequential. &nbsp;A good example of the best situation for=
 an index is an index on a date column when the data is loaded sequentially =
by date. &nbsp;A query against a specific date range will result in an order=
ed fetch from the disk, which leverages fast sequential access. <BR>
<BR>
The difference in performance between ordered and distributed access is sim=
ilar to the difference between &#8220;random seek&#8221; and &#8220;sequenti=
al&#8221; performance of the disk subsystem. &nbsp;The random seek performan=
ce of typical disk subsystems with one thread (for one user in postgres) is =
120 seeks per second. &nbsp;If your data was randomly distributed, you&#8217=
;d expect about 10,000/120 =3D 83 seconds to gather these records. &nbsp;Since=
 you&#8217;re getting 10 seconds, I expect that your data is lumped into gro=
ups and you are getting a mix of sequential reads and seeks.<BR>
<BR>
Note that adding more disks into a RAID does not help the random seek perfo=
rmance within Postgres, but may linearly improve the ordered access speed. &=
nbsp;So even with 44 disks in a RAID10 pool on a Sun X4500, the seek perform=
ance of Postgres (and other DBMS&#8217;s without async or threaded I/O) is t=
hat of a single disk &#8211; 120 seeks per second. &nbsp;Adding more users a=
llows the seeks to scale on such a machine as users are added, up to the num=
ber of disks in the RAID. &nbsp;But for your one user example &#8211; no hel=
p.<BR>
<BR>
If your problem is (2), you can re-order the data on disk by using a CREATE=
 TABLE statement like this:<BR>
&nbsp;&nbsp;CREATE TABLE fast_table AS SELECT * FROM slow_table ORDER BY te=
acher_id;<BR>
&nbsp;&nbsp;CREATE INDEX teacher_id_ix ON fast_table;<BR>
&nbsp;&nbsp;VACUUM ANALYZE fast_table;<BR>
<BR>
You should then see ordered access when you do index scans on teacher_id.<B=
R>
<BR>
- Luke<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_3251618436_675815--