Re: Planned simplification of catalog index updates

Wow, that is a huge difference for such a small change; makes you
wonder what other optimizations are sitting out there.

---------------------------------------------------------------------------

Tom Lane wrote:
> I said:
> > An even more radical approach is to get rid of the hardwired index name
> > lists in indexing.h, and instead expect CatalogOpenIndices to make use
> > of the index OID lists that are maintained by the relcache (since 7.1 or
> > so). Then the typical call would reduce to
> > CatalogUpdateIndexes(Relation, HeapTuple);
> > This would simplify development/maintenance at the cost of a small
> > amount of CPU time building the index OID list whenever it wasn't
> > already cached. (OTOH ... I'm unsure whether opening an index by OID
> > is any faster than opening it by name, but it's certainly plausible that
> > it might be --- so we could find we buy back the time spent building
> > relcache index lists by making the actual index open step quicker.)
>
> Indeed, it seems this *is* faster. I used the regression tests as a
> crude benchmark --- pg_bench wouldn't help since it doesn't do any
> catalog updates in the inner loop. Over ten runs of "make installcheck"
> on a RH 7.2 system, yesterday's CVS tip gave the following results for
> elapsed real time in seconds:
>
> min | max | avg | stddev
> -------+-------+------------------+-------------------
> 26.18 | 28.12 | 27.3590909090909 | 0.767247737637082
>
> With modifications to do all system catalog index updates as above,
> I instead got:
>
> min | max | avg | stddev
> ------+-------+--------+-------------------
> 24.3 | 26.72 | 25.833 | 0.674372959784605
>
> So it seems to be a fairly reliable 5% win on the regression tests,
> on top of being a lot simpler and more maintainable.
>
> I'm pretty pleased, and will be committing this as soon as CVS tip
> is back to a non-broken state ...
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026