Received: from localhost (unknown [200.46.204.184]) by developer.postgresql.org (Postfix) with ESMTP id 969102E0042 for ; Tue, 6 May 2008 02:48:39 -0300 (ADT) Received: from developer.postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 77402-09 for ; Tue, 6 May 2008 02:48:29 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from mail.emproshunts.com (mail.emproshunts.com [66.64.191.19]) by developer.postgresql.org (Postfix) with ESMTP id 36FD52E003C for ; Tue, 6 May 2008 02:48:30 -0300 (ADT) Received: from [192.168.1.49] by mail.emproshunts.com (GMS 15.00.3626/KX5895.00.ca7cb0e7) with ESMTP id huxlhaaa for pgsql-performance@postgresql.org; Tue, 6 May 2008 01:48:29 -0400 Message-ID: <481FF12D.4080600@emproshunts.com> Date: Tue, 06 May 2008 00:48:29 -0500 From: Justin User-Agent: Thunderbird 2.0.0.14 (Windows/20080421) MIME-Version: 1.0 To: Gregory Williamson CC: pgsql-performance@postgresql.org Subject: Re: need to speed up query References: <481FBC0D.7010509@emproshunts.com> <8B319E5A30FF4A48BE7EEAAF609DB233015E3832@COMAIL01.digitalglobe.com> In-Reply-To: <8B319E5A30FF4A48BE7EEAAF609DB233015E3832@COMAIL01.digitalglobe.com> Content-Type: multipart/alternative; boundary="------------000304060303070006050402" X-Originating-IP: [192.168.1.49] X-DNSBL: 0 X-AntiSpam: Checked for restricted content by Gordano's AntiSpam Software X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200805/70 X-Sequence-Number: 30038 This is a multi-part message in MIME format. --------------000304060303070006050402 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Gregory Williamson wrote: > > Justin -- > > You wrote: > > > > i've had to write queries to get trail balance values out of the GL > > transaction table and i'm not happy with its performance > > > > > > The table has 76K rows growing about 1000 rows per working day so the > > performance is not that great it takes about 20 to 30 seconds to get all > > the records for the table and when we limit it to single accounting > > 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 > > raid 10 Serial SCSI. > <... snipped 'cause I have a lame reader ...> > not according to the bench marks i have done, which were posted a couple of months ago. > > > > " 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) ... > i need to look into work mem its set at 25 megs which is fine for most work unless we get into the accounting queries which have to be more complicated than they need to be because how some of the tables are laid out which i did not lay out. > > > 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 corporate masters made me say this.) > --------------000304060303070006050402 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Gregory Williamson wrote:
RE: [PERFORM] need to speed up query

Justin --

You wrote:
> 
> i've had to write queries to get trail balance values out of the GL
> transaction table and i'm not happy with its performance
>
>
> The table has 76K rows growing about 1000 rows per working day so the
> performance is not that great it takes about 20 to 30 seconds to get all
> the records for the table and when we limit it to single accounting
> 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
> raid 10 Serial SCSI.
<... snipped 'cause I have a lame reader ...>

not according to the bench marks i have done,  which were posted a couple of months ago.


> "  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) ...

i need to look into work mem its set at 25 megs which is fine for most work unless we get into the accounting queries which have to be more complicated than they need to be because how some of the tables are laid out which i did not lay out.


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 corporate masters made me say this.)

--------------000304060303070006050402--