agora inbox for postgres@postgres.berkeley.edu  

help / color / mirror / Atom feed

From: Wolf-D. Ihlenfeldt <wolf@molout.tutkie.tut.ac.jp>
To: postgres@postgres.Berkeley.EDU
Subject: Private indices on system catalogs ?
Date: Tue, 19 Jul 94 19:59:00 jst
Message-ID: <9407191059.AA05521@molout.tutkie.tut.ac.jp> (raw)



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.
==============================================================================

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: postgres@postgres.berkeley.edu
  Cc: wolf@molout.tutkie.tut.ac.jp
  Subject: Re: Private indices on system catalogs ?
  In-Reply-To: <9407191059.AA05521@molout.tutkie.tut.ac.jp>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox