Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 36CBD1337BF7 for ; Thu, 17 Mar 2011 12:01:12 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 71847-01-6 for ; Thu, 17 Mar 2011 15:01:04 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by mail.postgresql.org (Postfix) with ESMTP id B9EE21337C8E for ; Thu, 17 Mar 2011 12:01:01 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id p2HF0xVg012607; Thu, 17 Mar 2011 11:00:59 -0400 (EDT) To: "Kevin Grittner" cc: "Derrick Rice" , pgsql-performance@postgresql.org Subject: Re: Updating histogram_bounds after a delete In-reply-to: <4D81D939020000250003BA22@gw.wicourts.gov> References: <4D80EBAB020000250003B9DE@gw.wicourts.gov> <4D81D939020000250003BA22@gw.wicourts.gov> Comments: In-reply-to "Kevin Grittner" message dated "Thu, 17 Mar 2011 09:49:45 -0500" Date: Thu, 17 Mar 2011 11:00:59 -0400 Message-ID: <12606.1300374059@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.91 tagged_above=-10 required=5 tests=BAYES_00=-1.9, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201103/185 X-Sequence-Number: 42864 "Kevin Grittner" writes: > Derrick Rice wrote: >> Kevin Grittner >> there is a feature to probe the end of an index's range in >>> situations where data skew was often causing less than optimal >>> plans to be chosen. >> Was this introduced in 9.0 or was it earlier? > I don't remember when it was added. I took a stab at searching for > it, but didn't get it figured out; if nobody who knows off-hand > jumps in, I'll try again when I have more time. Author: Tom Lane Branch: master Release: REL9_0_BR [40608e7f9] 2010-01-04 02:44:40 +0000 When estimating the selectivity of an inequality "column > constant" or "column < constant", and the comparison value is in the first or last histogram bin or outside the histogram entirely, try to fetch the actual column min or max value using an index scan (if there is an index on the column). If successful, replace the lower or upper histogram bound with that value before carrying on with the estimate. This limits the estimation error caused by moving min/max values when the comparison value is close to the min or max. Per a complaint from Josh Berkus. It is tempting to consider using this mechanism for mergejoinscansel as well, but that would inject index fetches into main-line join estimation not just endpoint cases. I'm refraining from that until we can get a better handle on the costs of doing this type of lookup. regards, tom lane