Re: Weird indices

From: Joseph Shraibman <jks(at)selectacast(dot)net>
To: Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgsql-general(at)postgresql(dot)org
Subject: Re: Weird indices
Date: 2001-02-21 01:09:34
Message-ID: 3A93154E.2FAEE331@selectacast.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Stephan Szabo wrote:
>
> On Tue, 20 Feb 2001, Joseph Shraibman wrote:
>
> > Stephan Szabo wrote:
> >
> > > Where are you seeing something that says the estimator/planner using the
> > > index to get an upper bound? The estimator shouldn't be asking either the
> > > index or the heap for anything, it should be working entirely with the
> > > statistics that were generated from vacuum.
> >
> > Index Scan using usertable_p_key on usertable (cost=0.00..25.68 rows=50
> > width=72)
> >
> > That rows=50, which is an overestimate by the way.
>
> That's because the estimate in this case was 50 and so it's estimating
> that going through the index and checking the heap is faster than a
> sequence scan. The *estimator* didn't use the index to figure that out,
> it's just saying that the best plan to actually *run* the query uses
> the index.
> IIRC, There's something which is effectively :
> estimated rows = <most common value's frequency>*<fraction>
> I think fraction defaults to (is always?) 1/10 for the standard
> index type. That's where the 50 comes from. And the frequency is
> probably from the last vacuum analyze.

Then it should do the same thing no matter what value I use, but when I
do different searches in one case it estimates 50 when there are 16 and
in the other it estimeates 502 where there are 502.

--
Joseph Shraibman
jks(at)selectacast(dot)net
Increase signal to noise ratio. http://www.targabot.com

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Stephan Szabo 2001-02-21 01:13:17 Re: Weird indices
Previous Message Ian Lance Taylor 2001-02-21 01:03:52 Re: Weird indices