Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 4238D9FA217; Sat, 23 Dec 2006 22:33:08 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-new, port 10024) with ESMTP id 03962-07; Sat, 23 Dec 2006 22:33:02 -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 F0DB59FA0B7; Sat, 23 Dec 2006 22:33:02 -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-a1.dreamhost.com (sd-green-bigip-62.dreamhost.com [208.97.132.62]) by svr4.postgresql.org (Postfix) with ESMTP id C7D3E5AF861; Sun, 24 Dec 2006 02:33:01 +0000 (GMT) Received: from [192.168.1.210] (adsl-75-31-110-76.dsl.irvnca.sbcglobal.net [75.31.110.76]) by spunkymail-a1.dreamhost.com (Postfix) with ESMTP id E4F02FF8A8; Sat, 23 Dec 2006 18:32:57 -0800 (PST) Message-ID: <458DE6DB.2060609@araisoft.com> Date: Sat, 23 Dec 2006 18:32:59 -0800 From: Benjamin Arai Organization: Araisoft User-Agent: Thunderbird 1.5.0.9 (Macintosh/20061207) MIME-Version: 1.0 To: Tom Lane 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> <23901.1166925355@sss.pgh.pa.us> In-Reply-To: <23901.1166925355@sss.pgh.pa.us> Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.281 tagged_above=0 required=5 tests=AWL, BAYES_50 X-Spam-Level: X-Archive-Number: 200612/315 X-Sequence-Number: 24107 Hi, I did a vacuum with -z and it fixed the issue. I was not aware that vacuumdb didn't ANALYZE by default. Thanks everybody for all of the help! Benjamin Tom Lane wrote: > Benjamin Arai writes: > >> -> 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) >> > > Hm, so how many rows in mutualfd_weekday_qbid for that date? > And how many satisfy the cusip condition? (I suppose 253, > but it looks like that must be a very small fraction of all > the rows for that date.) > > The selectivity estimators are not great about dealing with > zero-width intervals like this one (in fact, if you look at the > code you'll find it doesn't even bother to distinguish '>' from '>=' > ... something we should probably try to improve sometime). > > You'd probably have better luck if you could fold the WHERE condition > down to "pkey = '2005-12-15'". Dunno how feasible that is for your > application. > > regards, tom lane > >