Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 1A0569F9FC7 for ; Wed, 10 Oct 2007 17:33:38 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 02654-05 for ; Wed, 10 Oct 2007 17:33:27 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id E503F9FA341 for ; Wed, 10 Oct 2007 17:33:30 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.1/8.14.1) with ESMTP id l9AKWwXS023651; Wed, 10 Oct 2007 16:32:58 -0400 (EDT) To: "Kevin Grittner" cc: "Simon Riggs" , pgsql-performance@postgresql.org Subject: Re: hashjoin chosen over 1000x faster plan In-reply-to: <470CE34D.EE98.0025.0@wicourts.gov> References: <470B99BE.EE98.0025.0@wicourts.gov> <1191997904.4233.125.camel@ebony.site> <470C9813.EE98.0025.0@wicourts.gov> <1192035955.4233.306.camel@ebony.site> <1192035955.4233.306.camel@ebony.site> <20980.1192039650@sss.pgh.pa.us> <470CD3E3.EE98.0025.0@wicourts.gov> <1192042492.4233.334.camel@ebony.site> <470CE34D.EE98.0025.0@wicourts.gov> Comments: In-reply-to "Kevin Grittner" message dated "Wed, 10 Oct 2007 14:35:58 -0500" Date: Wed, 10 Oct 2007 16:32:57 -0400 Message-ID: <23650.1192048377@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200710/112 X-Sequence-Number: 27134 "Kevin Grittner" writes: > The point I'm trying to make is that at planning time the > pg_statistic row for this "Charge"."reopHistSeqNo" column showed > stanullfrac as 0.989; it doesn't seem to have taken this into account > when making its guess about how many rows would be joined when it was > compared to the primary key column of the "CaseHist" table. It certainly does take nulls into account, but the estimate of resulting rows was still nonzero; and even if it were zero, I'd be very hesitant to make it choose a plan that is fast only if there were exactly zero such rows and is slow otherwise. Most of the complaints we've had about issues of this sort involve the opposite problem, ie, the planner is choosing a plan that works well for few rows but falls down because reality involves many rows. "Fast-for-few-rows" plans are usually a lot more brittle than the alternatives in terms of the penalty you pay for too many rows, and so putting a thumb on the scales to push it towards a "fast" corner case sounds pretty unsafe to me. As Simon notes, the only technically sound way to handle this would involve run-time plan changeover, which is something we're not nearly ready to tackle. regards, tom lane