Resent-From: POSTGRES mailing list <postman@postgres.Berkeley.EDU>
Resent-Message-Id: <199407191058.DAA16741@nobozo.CS.Berkeley.EDU>
Sender: owner-postman@postgres.Berkeley.EDU
Date: Tue, 19 Jul 94 19:59:00 jst
From: wolf@molout.tutkie.tut.ac.jp (Wolf-D. Ihlenfeldt)
Message-Id: <9407191059.AA05521@molout.tutkie.tut.ac.jp>
To: postgres@postgres.Berkeley.EDU
Subject: Private indices on system catalogs ?
Resent-To: postgres-redist@postgres.Berkeley.EDU
Resent-Date: Tue, 19 Jul 94 03:58:16 -0700
Resent-XMts: smtp


In order to speed up some queries, I defined a private index 
on pg_attribute:

* define index cvs_pgattnumidx on pg_attribute using hash (attnum int2_ops)\g

This index worked and brought a notable speed increase in queries like

retrieve (iname=idx.relname,oname=onrel.relname,a.attname,idx.relam)
from idx in pg_class, onrel in pg_class, i in pg_index, a in pg_attribute 
where idx.oid = i.indexrelid and onrel.oid = i.indrelid and i.indproc = \"0\"::oid and
a.attrelid = onrel.oid and i.indkey[0] = a.attnum sort by oname using <


However, it seems that this index is not updated. If I add more
relations with associated indices, the query above will return 
only the OLD indices, omitting those which were newly introduced.
Destroying the index with
remove index cvs_pgattnumidx
and rebuilding it cures the symptoms, but why is the pg_attribute
index not updated when new attributes are added (which happens when
I create new relations). Is it because the index in mine, not 
belonging to user postgres ? Or are there other arcane reasons ?

WDI

==============================================================================
   To add/remove yourself to/from the POSTGRES mailing list: send mail with 
   the subject line ADD or DEL to "postgres-request@postgres.Berkeley.EDU"

   If this fails, send mail to "post_questions@postgres.Berkeley.EDU" and
   a human will deal with it.  DO NOT post to the "postgres" mailing list.
==============================================================================