Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 613A99F95E3; Sat, 23 Dec 2006 16:11:20 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-new, port 10024) with ESMTP id 06631-08; Sat, 23 Dec 2006 16:11:15 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159]) by postgresql.org (Postfix) with ESMTP id C06FC9FA334; Sat, 23 Dec 2006 16:11:14 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.3 X-Greylist: domain auto-whitelisted by SQLgrey-1.7.3 Received: from spunkymail-a16.dreamhost.com (sd-green-bigip-119.dreamhost.com [208.97.132.119]) by svr4.postgresql.org (Postfix) with ESMTP id 28FC45AF883; Sat, 23 Dec 2006 20:11:14 +0000 (GMT) Received: from [192.168.1.238] (adsl-75-31-110-76.dsl.irvnca.sbcglobal.net [75.31.110.76]) by spunkymail-a16.dreamhost.com (Postfix) with ESMTP id 6C1237BC19; Sat, 23 Dec 2006 12:11:09 -0800 (PST) Message-ID: <458D8D56.6020506@araisoft.com> Date: Sat, 23 Dec 2006 12:11:02 -0800 From: Benjamin Arai User-Agent: Thunderbird 1.5.0.8 (Windows/20061025) MIME-Version: 1.0 To: pgsql-general@postgresql.org, pgsql-admin@postgresql.org Subject: OUTER JOIN IS SLOW Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=2.169 tagged_above=0 required=5 tests=AWL, BAYES_95, SUBJ_ALL_CAPS X-Spam-Level: ** X-Archive-Number: 200612/307 X-Sequence-Number: 24099 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