Subject: Private indices on system catalogs ?

From: [email-masked] (Wolf-D. Ihlenfeldt)
Date: Tue, 19 Jul 94 19:59:00 jst

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 "[email-masked]"

   If this fails, send mail to "[email-masked]" and
   a human will deal with it.  DO NOT post to the "postgres" mailing list.
==============================================================================

---

From: [email-masked] (Paul M. Aoki)
Date: Thu, 21 Jul 94 01:31:05 -0700

> belonging to user postgres ? Or are there other arcane reasons ?

for user indices, index update is done by the query executor.
whenever you insert a new row, the executor updates all of
the indices for you.

the code that inserts new data into the system catalogs (e.g.,
when you "create" a table, the code that inserts entries into 
pg_class and pg_attribute) knows exactly what indices are 
"supposed" to be defined and updates only those indices.  that 
is, it bypasses the query executor and makes direct access 
method calls based on some hardwired code.

the hash am does seem to be pretty fast but nobody here ever had 
a chance to beat on it enough to instill a lot of confidence in it.
--
  Paul M. Aoki          |  University of California at Berkeley
  [email-masked]  |  Dept. of EECS, Computer Science Division (#1776) 
                        |  Berkeley, CA 94720-1776