From: Tom Lane <tgl@sss.pgh.pa.us>
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>
Cc: Derrick Rice <derrick.rice@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Updating histogram_bounds after a delete
Date: Thu, 17 Mar 2011 11:00:59 -0400
Message-ID: <12606.1300374059@sss.pgh.pa.us> (raw)
In-Reply-To: <4D81D939020000250003BA22@gw.wicourts.gov>
References: <AANLkTik6FnXpdEF+edr7Jh-cmEUfjsaPRoGOmMaxxPFO@mail.gmail.com>
<4D80EBAB020000250003B9DE@gw.wicourts.gov>
<AANLkTikrVqwXp3ugoMLR8-6BOegSDByRCuTCFtGVBZ+E@mail.gmail.com>
<4D81D939020000250003BA22@gw.wicourts.gov>
"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> Derrick Rice <derrick.rice@gmail.com> wrote:
>> Kevin Grittner <Kevin.Grittner@wicourts.gov wrote:
>>> 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 <tgl@sss.pgh.pa.us>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, Kevin.Grittner@wicourts.gov, derrick.rice@gmail.com
Subject: Re: Updating histogram_bounds after a delete
In-Reply-To: <12606.1300374059@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox