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

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 (view raw or flat)
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

pgsql-bugs by date

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

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