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 460179DD612 for ; Tue, 6 Dec 2005 16:33:33 -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 83738-04 for ; Tue, 6 Dec 2005 16:33:33 -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 E921A9DD602 for ; Tue, 6 Dec 2005 16:33:30 -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 jB6KXVxa015963; Tue, 6 Dec 2005 15:33:31 -0500 (EST) To: Ron Mayer cc: pgsql-performance@postgresql.org Subject: Re: Missed index opportunity for outer join? In-reply-to: <4395F1F3.1000803@cheapcomplexdevices.com> References: <3267.1133822299@sss.pgh.pa.us> <4678.1133888239@sss.pgh.pa.us> <4395F1F3.1000803@cheapcomplexdevices.com> Comments: In-reply-to Ron Mayer message dated "Tue, 06 Dec 2005 12:17:55 -0800" Date: Tue, 06 Dec 2005 15:33:31 -0500 Message-ID: <15962.1133901211@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.002 required=5 tests=[AWL=0.002] X-Spam-Score: 0.002 X-Spam-Level: X-Archive-Number: 200512/188 X-Sequence-Number: 16009 Ron Mayer writes: > 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. Yeah, I had considered the same explanation and rejected it for the same reason. Also, the difference in estimated cost is significant (265.85 for the seqscan vs 172.17 for the bitmap scan) so it's hard to think that a small change in stats --- so small as to not reflect in estimated row count --- would change the estimate by that much. [ thinks some more... ] Of course, what we have to remember is that the planner is actually going to choose based on the ultimate join cost, not on the subplan costs. The reason the seqscan survived initial comparisons at all is that it has a cheaper startup cost (less time to return the first tuple) than the bitmap scan, and this will be reflected into a cheaper startup cost for the overall nestloop. The extra hundred units of total cost would only reflect into the nestloop total cost --- and there, they would be considered "down in the noise" compared to a 90k total estimate. So probably what happened is that the planner preferred this plan on the basis that the total costs are the same to within estimation error while the startup cost is definitely less. In this explanation, the reason for the change in plans over time could be a change in the statistics for the other table. Is "facets" more dynamic than "point_features"? regards, tom lane