Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 22EEF9FA456 for ; Sat, 23 Dec 2006 16:21:23 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-new, port 10024) with ESMTP id 61424-07 for ; Sat, 23 Dec 2006 16:21:14 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from nz-out-0506.google.com (nz-out-0506.google.com [64.233.162.224]) by postgresql.org (Postfix) with ESMTP id A8ECD9F95E3 for ; Sat, 23 Dec 2006 16:21:16 -0400 (AST) Received: by nz-out-0506.google.com with SMTP id i11so1378981nzh for ; Sat, 23 Dec 2006 12:21:15 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=dh1w8G7Q8sM9qDfWobv97keD56qzfO5Xsxg969bf5yMxE+xTmsQHLwZM6bwdl3b+njFDGYgTpy9QLpEVDa0qUSDX2jetm3lExGTGZwU9WvrtmUmze7khBYxIOCohkNWYdq1HUxt/BQl6t28Wt1mhMLnBNMARSNLAZONISBODdT8= Received: by 10.64.193.8 with SMTP id q8mr2657468qbf.1166905275743; Sat, 23 Dec 2006 12:21:15 -0800 (PST) Received: by 10.65.206.17 with HTTP; Sat, 23 Dec 2006 12:21:15 -0800 (PST) Message-ID: Date: Sun, 24 Dec 2006 01:21:15 +0500 From: "Shoaib Mir" To: "Benjamin Arai" Subject: Re: OUTER JOIN IS SLOW Cc: pgsql-general@postgresql.org, pgsql-admin@postgresql.org In-Reply-To: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_45631_27373257.1166905275662" References: <458D8D56.6020506@araisoft.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200612/309 X-Sequence-Number: 24101 ------=_Part_45631_27373257.1166905275662 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline ....adding to the last email, for now try the work_mem but you should be adding ANALYZE along with the VACUUM (with a cron job I guess) you do regularly. ------------ Shoaib Mir EntperpriseDB (www.enterprisedb.com) On 12/24/06, Shoaib Mir wrote: > > Try increasing the work_mem first to see the change, that might help. > > ------------- > Shoaib Mir > EnterpriseDB (www.enterprisedb.com) > > On 12/24/06, Benjamin Arai wrote: > > > > I have been running pieces of my PL function by hand and I have found > > that the following queries work by themselves taking less than a second > > to execute. > > > > getDateRange"('12/1/2005','12/1/2006') <- simply generates a date > > list. Doesn't even access a table > > > > SELECT * FROM mutualfd_weekday_qbid WHERE cusip='92193920' AND pkey >= > > '12/15/2005' AND pkey <= '12/15/2006'; > > > > But when combined as below it takes 10 seconds to execute. > > > > SELECT d1 as date, d2.data as data FROM > > "getDateRange"('12/1/2005','12/1/2006') d1 FULL OUTER JOIN (SELECT * > > FROM mutualfd_weekday_qbid WHERE cusip='92193920' AND pkey >= > > '12/15/2005' AND pkey <= '12/15/2006') d2 ON d1=d2.pkey; > > > > Do I need to increase the work_mem or is this possible still a ANALYZE > > issue? > > > > Benjamin > > > > ---------------------------(end of broadcast)--------------------------- > > > > TIP 7: You can help support the PostgreSQL project by donating at > > > > http://www.postgresql.org/about/donate > > > > ------=_Part_45631_27373257.1166905275662 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline ....adding to the last email, for now try the work_mem but you should be adding ANALYZE along with the VACUUM (with a cron job I guess) you do regularly.

------------
Shoaib Mir
EntperpriseDB ( www.enterprisedb.com)

On 12/24/06, Shoaib Mir <shoaibmir@gmail.com> wrote:
Try increasing the work_mem first to see the change, that might help.

-------------
Shoaib Mir
EnterpriseDB ( www.enterprisedb.com)

On 12/24/06, Benjamin Arai <benjamin@araisoft.com> wrote:
I have been running pieces of my PL function by hand and I have found
that the following queries work by themselves taking less than a second
to execute.

getDateRange"('12/1/2005','12/1/2006')  <- simply generates a date
list.  Doesn't even access a table

SELECT * FROM mutualfd_weekday_qbid WHERE cusip='92193920' AND  pkey >=
'12/15/2005' AND pkey <= '12/15/2006';

But when combined as below it takes 10 seconds to execute.

SELECT d1 as date, d2.data as data FROM
"getDateRange"('12/1/2005','12/1/2006') d1 FULL OUTER JOIN (SELECT *
FROM mutualfd_weekday_qbid WHERE cusip='92193920' AND  pkey >=
'12/15/2005' AND pkey <= '12/15/2006') d2 ON d1=d2.pkey;

Do I need to increase the work_mem or is this possible still a ANALYZE
issue?

Benjamin

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

                http://www.postgresql.org/about/donate


------=_Part_45631_27373257.1166905275662--