Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id E1BAA9FA0EF; Tue, 2 Jan 2007 06:58:02 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-new, port 10024) with ESMTP id 56075-01; Tue, 2 Jan 2007 06:57:54 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from mx1.magproductions.nl (magfour.magproductions.nl [87.249.97.162]) by postgresql.org (Postfix) with ESMTP id 6A0C99F938C; Tue, 2 Jan 2007 06:57:57 -0400 (AST) Received: from cc756500-a.ensch1.ov.home.nl ([82.75.196.240] helo=[172.27.1.6]) by mx1.magproductions.nl with esmtpsa (TLS-1.0:DHE_RSA_AES_256_CBC_SHA:32) (Exim 4.50) id 1H1hL9-00040K-2q; Tue, 02 Jan 2007 11:57:55 +0100 Message-ID: <459A3AA0.4070307@magproductions.nl> Date: Tue, 02 Jan 2007 11:57:36 +0100 From: Alban Hertroys User-Agent: Thunderbird 1.5.0.7 (X11/20060918) MIME-Version: 1.0 To: Benjamin Arai CC: Shoaib Mir , pgsql-general@postgresql.org, pgsql-admin@postgresql.org Subject: Re: [GENERAL] OUTER JOIN IS SLOW References: <458D8D56.6020506@araisoft.com> <458D9437.1000609@araisoft.com> In-Reply-To: <458D9437.1000609@araisoft.com> Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200701/3 X-Sequence-Number: 24159 Benjamin Arai wrote: > Just to make things more clear I ran EXPLAIN ANALYZE on the slow query. > I got > -> Index Scan using mutualfd_weekday_qbid_pkey_idx on > mutualfd_weekday_qbid (cost=0.00..6.01 rows=1 width=19) (actual > time=34.579..8510.801 rows=253 loops=1) > Index Cond: ((pkey >= '2005-12-15'::date) AND (pkey <= > '2006-12-15'::date)) > Filter: (cusip = '92193920'::text) This is the relevant part (including the index details). It's filtering (text) over all index items within a period of a full year. I imagine can be quite a lot of data (unless pkey is a primary key on a date field; only 366 records). As mentioned, ANALYZE helps, but you may want to check: 1) Are you comparing integers as text strings? You may not want to. 2) Do you have an index on cusip? You probably have, just in case. -- Alban Hertroys alban@magproductions.nl magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World //