Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id E49C76501AB for ; Thu, 11 Dec 2008 21:52:04 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 26589-07 for ; Thu, 11 Dec 2008 21:52:02 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from fk-out-0910.google.com (fk-out-0910.google.com [209.85.128.188]) by mail.postgresql.org (Postfix) with ESMTP id EF70764FD96 for ; Thu, 11 Dec 2008 21:52:01 -0400 (AST) Received: by fk-out-0910.google.com with SMTP id e30so807356fke.3 for ; Thu, 11 Dec 2008 17:52:00 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to :subject:cc:in-reply-to:mime-version:content-type :content-transfer-encoding:content-disposition:references; bh=eBWYYRPGFqIo2tvogO9L6RTyiYNox4cwRmjAlxLcy1s=; b=WBYZ7R9c3AFP0Ly4tVn6eojU045uYNyKZzkhUamxQynTAuyF3c5ixoAf4IJQaqrS2r MXsyi3qphuTGGIp9fVNurD4WVptbB+qPWYWGx7lbFF50u928rM/agJkAB/BbgNkBAZX5 7DB9Yj5flx9FXz+iLbFJj2U+vamzVQhzYMp4o= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:cc:in-reply-to:mime-version :content-type:content-transfer-encoding:content-disposition :references; b=q4YATqgs+1IaawQlO4PrpAe0iGEl3CQ+gkf7duSNBmo1ZaFbpu73HXt2A/JG5Nr3Nr /R65nI9HHx28dFoxPxtC0oxqCoXW5o+7hdjmYmuvT1o9+LX7Z5kWGzD6AkyM+/YFVmS7 F2orUhufUVULCCJewAz8qE7tVOznghzGkzS+4= Received: by 10.223.105.140 with SMTP id t12mr3757062fao.12.1229046720333; Thu, 11 Dec 2008 17:52:00 -0800 (PST) Received: by 10.223.106.209 with HTTP; Thu, 11 Dec 2008 17:52:00 -0800 (PST) Message-ID: <603c8f070812111752v693e2da2ydc6dea4d263b7647@mail.gmail.com> Date: Thu, 11 Dec 2008 20:52:00 -0500 From: "Robert Haas" To: "Tom Lane" Subject: Re: benchmarking the query planner Cc: "Gregory Stark" , "Kevin Grittner" , "jd@commandprompt.com" , "Josh Berkus" , "Greg Smith" , pgsql-hackers@postgresql.org In-Reply-To: <12862.1229036217@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <603c8f070812061019r5aca3aajeaa5493b11c5feff@mail.gmail.com> <9897C4AF-6D47-4F44-AD3D-F5A82AC392DB@enterprisedb.com> <87ljuq4rmk.fsf@oxford.xeocode.com> <603c8f070812092025k6325e0fcud001b205a507a634@mail.gmail.com> <4313.1228931932@sss.pgh.pa.us> <603c8f070812101828p1bd7df49yb8c916adedb49fe1@mail.gmail.com> <9565.1229018968@sss.pgh.pa.us> <877i66tjju.fsf@oxford.xeocode.com> <12862.1229036217@sss.pgh.pa.us> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200812/849 X-Sequence-Number: 129478 > I think though that the case for doing so is pretty good. "MCVs" that > are beyond the K'th entry can't possibly have frequencies greater than > 1/K, and in most cases it'll be a lot less. So the incremental > contribution to the accuracy of the join selectivity estimate drops off > pretty quickly, I should think. And it's not like we're ignoring the > existence of those values entirely --- we'd just be treating them as if > they are part of the undifferentiated collection of non-MCV values. > > It might be best to stop when the frequency drops below some threshold, > rather than taking a fixed number of entries. OK, I'll bite. How do we decide where to put the cutoff? If we make it too high, it will have a negative effect on join selectivity estimates; if it's too low, it won't really address the problem we're trying to fix. I randomly propose p = 0.001, which should limit eqjoinsel() to about a million equality tests in the worst case. In the synthetic example we were just benchmarking, that causes the entire MCV array to be tossed out the window (which feels about right). ...Robert