Skip site navigation (1) Skip section navigation (2)

Re: Shouldn't we have a way to avoid "risky" plans?

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Josh Berkus <josh(at)agliodbs(dot)com>
Cc: Claudio Freire <klaussfreire(at)gmail(dot)com>, postgres performance list <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Shouldn't we have a way to avoid "risky" plans?
Date: 2011-03-25 14:24:44
Message-ID: 3210.1301063084@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-performance
Josh Berkus <josh(at)agliodbs(dot)com> writes:
>> If the planner starts operating on the basis of worst case rather than
>> expected-case performance, the complaints will be far more numerous than
>> they are today.

> Yeah, I don't think that's the way to go.  The other thought I had was
> to accumulate a "risk" stat the same as we accumulate a "cost" stat.

> However, I'm thinking that I'm overengineering what seems to be a fairly
> isolated problem, in that we might simply need to adjust the costing on
> this kind of a plan.

mergejoinscansel doesn't currently try to fix up the histogram bounds by
consulting indexes.  At the time I was afraid of the costs of doing
that, and I still am; but it would be a way to address this issue.

Author: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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

In response to

Responses

pgsql-performance by date

Next:From: Strange, John WDate: 2011-03-25 14:25:45
Subject: Re: pg9.0.3 explain analyze running very slow compared to a different box with much less configuration
Previous:From: Tom LaneDate: 2011-03-25 14:12:10
Subject: Re: Shouldn't we have a way to avoid "risky" plans?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group