X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 9D39BD1D08C for ; Fri, 16 Apr 2004 12:37:23 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 58571-09 for ; Fri, 16 Apr 2004 12:37:17 -0300 (ADT) Received: from bramble.mmrd.com (unknown [65.217.53.66]) by svr1.postgresql.org (Postfix) with ESMTP id 561F0D1D085 for ; Fri, 16 Apr 2004 12:37:21 -0300 (ADT) Received: from thorn.mmrd.com (thorn.mmrd.com [172.25.10.100]) by bramble.mmrd.com (8.12.8/8.12.8) with ESMTP id i3GFeYcM009750; Fri, 16 Apr 2004 11:40:35 -0400 Received: from gnvex001.mmrd.com (gnvex001.mmrd.com [192.168.3.55]) by thorn.mmrd.com (8.11.6/8.11.6) with ESMTP id i3GFbFl10132; Fri, 16 Apr 2004 11:37:17 -0400 Received: from camel.mmrd.com ([172.25.5.213]) by gnvex001.mmrd.com with SMTP (Microsoft Exchange Internet Mail Service Version 5.5.2657.72) id FVMA9Y9C; Fri, 16 Apr 2004 11:37:13 -0400 Subject: Re: query slows down with more accurate stats From: Robert Treat To: Tom Lane Cc: pgsql-performance@postgresql.org In-Reply-To: <25556.1081883922@sss.pgh.pa.us> References: <1081879359.25537.526.camel@camel> <25556.1081883922@sss.pgh.pa.us> Content-Type: text/plain Content-Transfer-Encoding: 7bit X-Mailer: Ximian Evolution 1.0.8 Date: 16 Apr 2004 11:37:15 -0400 Message-Id: <1082129835.23419.869.camel@camel> Mime-Version: 1.0 X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200404/210 X-Sequence-Number: 6510 On Tue, 2004-04-13 at 15:18, Tom Lane wrote: > Robert Treat writes: > Well, the first problem is why is ANALYZE's estimate of the total row > count so bad :-( ? I suspect you are running into the situation where > the initial pages of the table are thinly populated and ANALYZE > mistakenly assumes the rest are too. That was my thinking, which is somewhat confirmed after a vacuum full on the table; now analyze gives pretty accurate states. Of course the downside is that now the query is consistently slower. > > so i guess i am wondering if there is something I should be doing to > > help get the better plan at the more accurate stats levels and/or why it > > doesn't stick with the original plan (I noticed disabling merge joins > > does seem to push it back to the original plan). > > With the larger number of estimated rows it's figuring the nestloop will > be too expensive. The row estimate for the cl scan went up from 1248 > to 10546, so the estimated cost for the nestloop plan would go to about > 240000 units vs 80000 for the mergejoin plan. This is obviously off > rather badly when the true runtimes are 1.7 vs 8.1 seconds :-(. > > I think this is an example of a case where we really need better > estimation of nestloop costs --- it's drastically overestimating the > relative cost of the nestloop because it's not accounting for the cache > benefits of the repeated index searches. You could probably force the > nestloop to be chosen by lowering random_page_cost, but that's just a > kluge solution ... the real problem is the model is wrong. > Unfortunately playing with random_page_cost doesn't seem to be enough to get it to favor the nested loop... though setting it down to 2 does help overall. played with index_cpu_tuple_cost a bit but that seemed even less useful. aggravating when you know there is a better plan it could pick but no (clean) way to get it to do so... > I have a to-do item to work on this, and will try to bump up its > priority a bit. > I'll keep an eye out, thanks Tom. Robert Treat -- Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL