Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 5B3F19FA4D4; Sat, 23 Dec 2006 21:56:14 -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 85901-02; Sat, 23 Dec 2006 21:56:04 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 802689FA4CA; Sat, 23 Dec 2006 21:56:09 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.6/8.13.6) with ESMTP id kBO1ttk9023902; Sat, 23 Dec 2006 20:55:59 -0500 (EST) To: Benjamin Arai cc: Shoaib Mir , pgsql-general@postgresql.org, pgsql-admin@postgresql.org Subject: Re: [GENERAL] OUTER JOIN IS SLOW In-reply-to: <458D9437.1000609@araisoft.com> References: <458D8D56.6020506@araisoft.com> <458D9437.1000609@araisoft.com> Comments: In-reply-to Benjamin Arai message dated "Sat, 23 Dec 2006 12:40:23 -0800" Date: Sat, 23 Dec 2006 20:55:55 -0500 Message-ID: <23901.1166925355@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200612/314 X-Sequence-Number: 24106 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