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 3BF8F9DCC3D for ; Tue, 6 Dec 2005 16:11:17 -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 77821-06 for ; Tue, 6 Dec 2005 16:11:17 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from floppy.pyrenet.fr (news.pyrenet.fr [194.116.145.2]) by postgresql.org (Postfix) with ESMTP id 21C209DCAAC for ; Tue, 6 Dec 2005 16:11:14 -0400 (AST) Received: by floppy.pyrenet.fr (Postfix, from userid 106) id 3A6443093F; Tue, 6 Dec 2005 21:11:15 +0100 (MET) From: Ron Mayer X-Newsgroups: pgsql.performance Subject: Re: Missed index opportunity for outer join? Date: Tue, 06 Dec 2005 12:17:55 -0800 Organization: Hub.Org Networking Services Lines: 34 Message-ID: <4395F1F3.1000803@cheapcomplexdevices.com> References: <3267.1133822299@sss.pgh.pa.us> <4678.1133888239@sss.pgh.pa.us> Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Complaints-To: usenet@news.hub.org To: Tom Lane User-Agent: Mozilla Thunderbird 1.0 (Windows/20041206) X-Accept-Language: en-us, en In-Reply-To: <4678.1133888239@sss.pgh.pa.us> To: pgsql-performance@postgresql.org X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0 required=5 tests=[none] X-Spam-Score: 0 X-Spam-Level: X-Archive-Number: 200512/186 X-Sequence-Number: 16007 Tom Lane wrote: > rm_pg@cheapcomplexdevices.com writes: >>On Mon, 5 Dec 2005, Tom Lane wrote: > >>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. Hmm. Unfortunately that was happening on a production system and the amount of data in the tables has changed - and now I'm no longer getting a seq_scan when I try to reproduce it. That system is still using 8.1.0. The "point_features" table is pretty dynamic and it's possible that the data changed between my 'explain analyze' statement in the first post in this thread. However since both of them show an estimate of "rows=948" and returned an actual of 917 I don't think that happened. > 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. I now have a development system with cvs-tip; but have not yet reproduced the seq scan on it either. I'm using the same data that was in "point_features" with "featureid=120" - but don't have any good way of knowing what other data may have been in the table at the time. If desired, I could set up a cron job to periodically explain analyze that query and see if it recurs.