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@postgresql.org)
> 

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