X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id DD1189DCCF1 for ; Tue, 6 Dec 2005 12:57:22 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 42545-08 for ; Tue, 6 Dec 2005 12:57:21 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 70F3B9DCCE5 for ; Tue, 6 Dec 2005 12:57:20 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id jB6GvJZG004679; Tue, 6 Dec 2005 11:57:19 -0500 (EST) To: rm_pg@cheapcomplexdevices.com cc: pgsql-performance@postgresql.org Subject: Re: Missed index opportunity for outer join? In-reply-to: References: <3267.1133822299@sss.pgh.pa.us> Comments: In-reply-to rm_pg@cheapcomplexdevices.com message dated "Mon, 05 Dec 2005 15:05:04 -0800" Date: Tue, 06 Dec 2005 11:57:19 -0500 Message-ID: <4678.1133888239@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.003 required=5 tests=[AWL=0.003] X-Spam-Score: 0.003 X-Spam-Level: X-Archive-Number: 200512/171 X-Sequence-Number: 15992 rm_pg@cheapcomplexdevices.com writes: > On Mon, 5 Dec 2005, Tom Lane wrote: >> (Note to self: it is a bit odd that fac_id=261 is pushed down to become >> an indexqual in one case but not the other ...) > I speculate that the seq_scan wasn't really the slow part > compared to not using using both parts of the index in the > second part of the plan. The table point_features is tens of > thousands of rows, while the table facets is tens of millions. Agreed, but it's still odd that it would use a seqscan in one case and not the other. I found the reason why the fac_id=261 clause isn't getting used as an index qual; it's a bit of excessive paranoia that goes back to 2002. I've fixed that for 8.1.1, but am still wondering about the seqscan on the other side of the join. regards, tom lane