agora inbox for postgres@postgres.berkeley.edu
help / color / mirror / Atom feedPrivate indices on system catalogs ?
2+ messages / 2 participants
[nested] [flat]
* Private indices on system catalogs ?
@ 1970-01-01 00:00 Wolf-D. Ihlenfeldt <wolf@molout.tutkie.tut.ac.jp>
1994-07-21 08:31 ` Re: Private indices on system catalogs ? Paul M. Aoki <aoki@CS.Berkeley.EDU>
0 siblings, 1 reply; 2+ messages in thread
From: Wolf-D. Ihlenfeldt @ 1970-01-01 00:00 UTC (permalink / raw)
To: ucb-postgres
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.
==============================================================================
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Private indices on system catalogs ?
1970-01-01 00:00 Private indices on system catalogs ? Wolf-D. Ihlenfeldt <wolf@molout.tutkie.tut.ac.jp>
@ 1994-07-21 08:31 ` Paul M. Aoki <aoki@CS.Berkeley.EDU>
0 siblings, 0 replies; 2+ messages in thread
From: Paul M. Aoki @ 1994-07-21 08:31 UTC (permalink / raw)
To: Wolf-D. Ihlenfeldt <wolf@molout.tutkie.tut.ac.jp>; +Cc: ucb-postgres
wolf@molout.tutkie.tut.ac.jp (Wolf-D. Ihlenfeldt) writes:
> 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 ?
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
aoki@CS.Berkeley.EDU | Dept. of EECS, Computer Science Division (#1776)
| Berkeley, CA 94720-1776
==============================================================================
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.
==============================================================================
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~1994-07-21 08:31 UTC | newest]
Thread overview: 2+ messages (download: mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1970-01-01 00:00 Private indices on system catalogs ? Wolf-D. Ihlenfeldt <wolf@molout.tutkie.tut.ac.jp>
1994-07-21 08:31 ` Paul M. Aoki <aoki@CS.Berkeley.EDU>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox