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-03-01 14:37:40
Message-ID: 28067.1014993460@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 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

In response to

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2002-03-01 15:03:41 Re: Bug #605: timestamp(timestamp('a timestamp)) no longer works
Previous Message Victor Krasinsky 2002-03-01 13:19:29 BUG: bigint and indexes ?