Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 155BB9FA43C for ; Sat, 23 Dec 2006 16:17:53 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-new, port 10024) with ESMTP id 09972-04 for ; Sat, 23 Dec 2006 16:17:44 -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.231]) by postgresql.org (Postfix) with ESMTP id A4A109F95E3 for ; Sat, 23 Dec 2006 16:17:46 -0400 (AST) Received: by nz-out-0506.google.com with SMTP id i11so1378631nzh for ; Sat, 23 Dec 2006 12:17:45 -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=RLYDVYvlLPc8SUm7uazUn76riyv6XmgIhtqkZYXL/nVQ1+4Gp7+InfS5XwmTHI3vUMox6SyPywl5EA+un0+FcYwzr2ikiZBrjwE8Ul7ZjxG8hfKIvODDGqXGUvdo+9wIWl/kApXR5Wxzj+VP5nBeOhuTlTnhnAGwJgM9/qsbuC0= Received: by 10.65.147.1 with SMTP id z1mr2652663qbn.1166905065734; Sat, 23 Dec 2006 12:17:45 -0800 (PST) Received: by 10.65.206.17 with HTTP; Sat, 23 Dec 2006 12:17:45 -0800 (PST) Message-ID: Date: Sun, 24 Dec 2006 01:17:45 +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: <458D8D56.6020506@araisoft.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_45591_21280043.1166905065597" References: <458D8D56.6020506@araisoft.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200612/308 X-Sequence-Number: 24100 ------=_Part_45591_21280043.1166905065597 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline 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_45591_21280043.1166905065597 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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_45591_21280043.1166905065597--