
> > Okay, that's a reasonable case to
> > try to optimize, though I'd like to think the problem will go away
> > in a release or two when we implement VACUUM-time index shrinking.
> > 
> > However, I'm not sure about the "lot faster" part.  The only win
> > I can see is that when rebuilding a btree index, you could skip
> > the sort step by reading the old index in index order.
> 
> Don't we have to scan the (possibly larger) heap table ?

Yes, but that is done with a seq scan, but the index has to be read in 
random order, since the index pages are not physically sorted on disk
from lowest to highest value. Of course you can spare the sort, 
but overall ...

Imho spending effort on VACUUM is more fruitful, and has the potential to 
allow much more concurrency than REINDEX, no ?

Andreas

Wed, 30 Jan 2002 11:21:42 +0100
"Zeugswetter Andreas SB SD" <ZeugswetterA@spardat.at>
Re: Improving backend launch time by preloading relcache
Zeugswetter Andreas SB SD wrote:
> 
> > > Okay, that's a reasonable case to
> > > try to optimize, though I'd like to think the problem will go away
> > > in a release or two when we implement VACUUM-time index shrinking.
> > >
> > > However, I'm not sure about the "lot faster" part.  The only win
> > > I can see is that when rebuilding a btree index, you could skip
> > > the sort step by reading the old index in index order.
> >
> > Don't we have to scan the (possibly larger) heap table ?
> 
> Yes, but that is done with a seq scan, but the index has to be read in
> random order, since the index pages are not physically sorted on disk
> from lowest to highest value. Of course you can spare the sort,
> but overall ...

Reading a index file is not faster than reading the heap file ?
Does sorting finish in a moment ?
If so we have to use sequential scan much more often.

Anyway there seems no point on changing REINDEX.
The only thing I have to do is to remove the needless
check in tcop/utility.c as soon as 7.2 is released.

regards,
Hiroshi Inoue

Thu, 31 Jan 2002 11:07:56 +0900
Hiroshi Inoue <Inoue@tpf.co.jp>
Re: Improving backend launch time by preloading relcache
Hiroshi Inoue <Inoue@tpf.co.jp> writes:
> Anyway there seems no point on changing REINDEX.
> The only thing I have to do is to remove the needless
> check in tcop/utility.c as soon as 7.2 is released.

I don't believe it's needless, and I suggest you not remove it,
until we do something about making the pg_internal unlink happen
at the right time.  With the unlink where it is, I think it's quite
unsafe to reindex system indexes in a live database.

			regards, tom lane

Wed, 30 Jan 2002 21:34:49 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Improving backend launch time by preloading relcache 
Tom Lane wrote:
> 
> Hiroshi Inoue <Inoue@tpf.co.jp> writes:
> > Anyway there seems no point on changing REINDEX.
> > The only thing I have to do is to remove the needless
> > check in tcop/utility.c as soon as 7.2 is released.
> 
> I don't believe it's needless, and I suggest you not remove it,
> until we do something about making the pg_internal unlink happen
> at the right time.  With the unlink where it is, I think it's quite
> unsafe to reindex system indexes in a live database.

Currently there are just a few relations info kept in
pg_internal.init and they are all nailed. I'm not
allowing REINDEX for nailed relations though there's
a #ifdef'd trial implementation. I'm intending the
change for 7.2.1 not 7.3. If it isn't allowed in 7.2.x
I would strongly object to the 7.2 release itself.

regards,
Hiroshi Inoue

Thu, 31 Jan 2002 11:52:18 +0900
Hiroshi Inoue <Inoue@tpf.co.jp>
Re: Improving backend launch time by preloading relcache
Hiroshi Inoue <Inoue@tpf.co.jp> writes:
> Currently there are just a few relations info kept in
> pg_internal.init and they are all nailed. I'm not
> allowing REINDEX for nailed relations 

Oh, okay.

> a #ifdef'd trial implementation. I'm intending the
> change for 7.2.1 not 7.3. If it isn't allowed in 7.2.x
> I would strongly object to the 7.2 release itself.

If you think it should be changed then change it now.  I see no
reason to wait.

			regards, tom lane

Wed, 30 Jan 2002 22:12:17 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Improving backend launch time by preloading relcache 