Re: need to speed up query

by Gregory.Williamson@[EMAIL PROTECTED] ("Gregory Williamson") May 5, 2008 at 10:08 PM

This is a multi-part message in MIME format.

------_=_NextPart_001_01C8AF2E.DAB416A1
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Justin --

You wrote:
> =20
> i've had to write queries to get trail balance values out of the GL=20
> transaction table and i'm not happy with its performance=20
>=20
>=20
> The table has 76K rows growing about 1000 rows per working day so the=20
> performance is not that great it takes about 20 to 30 seconds to get =
all=20
> the records for the table and when we limit it to single accounting=20
> period it drops down to 2 seconds

So 30 seconds for 76 days (roughly) worth of numbers ? Not terrible but =
not great.

> Here is the query and explain .  PostgreSql  is 8.3.1 on new server =
with=20
> raid 10 Serial SCSI.
<... snipped 'cause I have a lame reader ...>

> "  Sort Method:  quicksort  Memory: 292kB"
<...snip...>
> "Total runtime: 24682.580 ms"

I don't have any immediate thoughts but maybe you could post the table =
schemas and indexes. It looks to my untutored eye as if most of the =
estimates are fair so I am guessing that you have run analyze recently.

What is your sort memory set to ? If work_mem is too low then you'll go =
to disk (if you see tmp files under the postgres $PGDATA/base directory =
you might be seeing the result of this) ...

HTH

Greg Williamson
Senior DBA
DigitalGlobe

Confidentiality Notice: This e-mail message, including any attachments, =
is for the sole use of the intended recipient(s) and may contain =
confidential and privileged information and must be protected in =
accordance with those provisions. Any unauthorized review, use, =
disclosure or distribution is prohibited. If you are not the intended =
recipient, please contact the sender by reply e-mail and destroy all =
copies of the original message.

(My cor****ate masters made me say this.)

------_=_NextPart_001_01C8AF2E.DAB416A1
Content-Type: text/html;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =
charset=3Diso-8859-1">
<META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version =
6.5.7652.24">
<TITLE>RE: [PERFORM] need to speed up query</TITLE>
</HEAD>
<BODY>
<!-- Converted from text/plain format -->

<P><FONT SIZE=3D2>Justin --<BR>
<BR>
You wrote:<BR>
&gt;&nbsp;<BR>
&gt; i've had to write queries to get trail balance values out of the =
GL<BR>
&gt; transaction table and i'm not happy with its performance<BR>
&gt;<BR>
&gt;<BR>
&gt; The table has 76K rows growing about 1000 rows per working day so =
the<BR>
&gt; performance is not that great it takes about 20 to 30 seconds to =
get all<BR>
&gt; the records for the table and when we limit it to single =
accounting<BR>
&gt; period it drops down to 2 seconds<BR>
<BR>
So 30 seconds for 76 days (roughly) worth of numbers ? Not terrible but =
not great.<BR>
<BR>
&gt; Here is the query and explain .&nbsp; PostgreSql&nbsp; is 8.3.1 on =
new server with<BR>
&gt; raid 10 Serial SCSI.<BR>
&lt;... snipped 'cause I have a lame reader ...&gt;<BR>
<BR>
&gt; &quot;&nbsp; Sort Method:&nbsp; quicksort&nbsp; Memory: =
292kB&quot;<BR>
&lt;...snip...&gt;<BR>
&gt; &quot;Total runtime: 24682.580 ms&quot;<BR>
<BR>
<BR>
I don't have any immediate thoughts but maybe you could post the table =
schemas and indexes. It looks to my untutored eye as if most of the =
estimates are fair so I am guessing that you have run analyze =
recently.<BR>
<BR>
What is your sort memory set to ? If work_mem is too low then you'll go =
to disk (if you see tmp files under the postgres $PGDATA/base directory =
you might be seeing the result of this) ...<BR>
<BR>
HTH<BR>
<BR>
Greg Williamson<BR>
Senior DBA<BR>
DigitalGlobe<BR>
<BR>
Confidentiality Notice: This e-mail message, including any attachments, =
is for the sole use of the intended recipient(s) and may contain =
confidential and privileged information and must be protected in =
accordance with those provisions. Any unauthorized review, use, =
disclosure or distribution is prohibited. If you are not the intended =
recipient, please contact the sender by reply e-mail and destroy all =
copies of the original message.<BR>
<BR>
(My cor****ate masters made me say this.)<BR>
<BR>
</FONT>
</P>

</BODY>
</HTML>
------_=_NextPart_001_01C8AF2E.DAB416A1--

11 Posts in Topic:

need to speed up query

justin@[EMAIL PROTECTED]

2008-05-05 21:01:49

Re: need to speed up query

scott.marlowe@[EMAIL PROT

2008-05-05 21:27:03

Re: need to speed up query

justin@[EMAIL PROTECTED]

2008-05-06 00:36:36

Re: need to speed up query

Gregory.Williamson@[EMAIL