Re: Indexes not always used after inserts/updates/vacuum analyze

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Reinhard Max <max(at)suse(dot)de>
Cc: "Michael G(dot) Martin" <michael(at)vpmonline(dot)com>, pgsql-bugs(at)postgresql(dot)org
Subject: Re: Indexes not always used after inserts/updates/vacuum analyze
Date: 2002-02-28 15:15:35
Message-ID: 13234.1014909335@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

Reinhard Max <max(at)suse(dot)de> writes:
>> The major estimation error is evidently in this indexscan. What
>> statistics does pg_stats show for this table?

> See attached file.

Okay. It looks like foo.id has a pretty strong but not perfect
descending order (the correlation statistic is -0.563276). The
planner is evidently not rating that effect strongly enough.

If you look in cost_index (see approx. lines 270-340 in
src/backend/optimizer/path/costsize.c) you'll see that it computes
access cost estimates for both the perfectly sequential case and
the perfectly uncorrelated case, and then tries to interpolate
between them. I have reasonable faith in both of the endpoint
estimation methods, but very little in the interpolation equation ---
it was chosen on the spur of the moment and hasn't really been tested.

It might be interesting to replace csquared with just
fabs(indexCorrelation) to see if the results are better. Also, if you
cared to step through the code with a debugger or add some printout
statements, we could learn what the min and max costs are that it's
interpolating between; that'd be interesting to know as well.

regards, tom lane

In response to

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message free 2002-02-28 15:55:48 RSERV AND POSTGRESQL 7.2
Previous Message Reinhard Max 2002-02-28 15:00:47 Re: Indexes not always used after inserts/updates/vacuum