"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.
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.)
> I know another case. pg_attrdef has no index on (adrelid,attnum)
> though it has an index on (adrelid).
Doesn't look to me like we need an index on (adrelid,attnum), at
least not in any paths that are common enough to justify maintaining
another index. The (adrelid) index supports loading attrdef data
into the relcache, which is the only path I'm particularly concerned
about performance of...
regards, tom lane
