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

Reinhard Max <max@suse.de> writes:
>> The actual rows read from this indexscan seem to be many fewer than
>> the number of rows in the table.  What are the ranges of the id
>> values in tables foo and bar?  I'm wondering if the merge could have
>> stopped far short of the end of the foo table; if so, *that* is the
>> effect that we are failing to model accurately.

> Good guess :)

> I'll tell my colleague (it's his test database, after all) that he
> should take more realistic test data before complaining about bad
> performance...

Actually, is it unrealistic test data?  After thinking about it awhile,
I concluded that this is an effect the planner could and should model.
We have statistics that will tell us the maximum values of both
variables (at least in common cases), so it's not hard to estimate which
input stream will be exhausted first and how much of the other one will
actually be read.  This could make a big difference in the cost of an
indexscan-based merge.

I have committed changes for 7.3 that do this.  It's probably too big a
change to risk back-patching for 7.2.1, but if you care to experiment
with CVS tip then you could try it out.

            regards, tom lane