Re: Performance (was: The New Slashdot Setup (includes MySql server))

> "Hiroshi Inoue" <Inoue@tpf.co.jp> writes:
> >> Unfortunately there is no index on pg_index's indrelid column in 7.0,
> >> so this is not fixable without an initdb.  TODO item for 7.1, I guess.
> 
> > I've noticed the fact since before but haven't complained.
> > As far as I see,pg_index won't so big. In fact Matthias's case has
> > only 1 page after running vacuum for pg_index.  In such cases
> > sequential scan is faster than index scan as you know.
> 
> True, but the differential isn't very big either when dealing with
> a small table.  I think I'd rather use an index and be assured that
> performance doesn't degrade drastically when the database contains
> many indexes.

Agreed.

> 
> I've also been thinking about ways to implement the relcache-based
> caching of index information that I mentioned before.  That doesn't
> address the scanning problem in general but it should improve
> performance for this part of the planner quite a bit.  The trick is to
> ensure that other backends update their cached info whenever an index
> is added or deleted.  I thought of one way to do that: force an update
> of the owning relation's pg_class tuple during CREATE or DROP INDEX,
> even when we don't have any actual change to make in its contents ---
> that'd force a relcache invalidate cycle at other backends.  (Maybe
> we don't even need to change the pg_class tuple, but just send out a
> shared-cache-invalidate message as if we had.)

Oh, good idea.  Just invalidate the relation so we reload.

BTW, Hiroshi is the one who gave me the recursion-prevision fix for the
system index additions for 7.0.


--  Bruce Momjian                        |  http://www.op.net/~candle 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