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

> "Hiroshi Inoue" <Inoue(at)tpf(dot)co(dot)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(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