Thread: Additional system indexes

Tom, you mentioned you needed more system indexes.  I would be glad to
create them for you.  Can you tell me which ones?

Also, I see a heap_getnext on pg_attribute in vacuum.c that should be
using index scan.  Are there other places in the code where this needs
to be changed?

--  Bruce Momjian                        |  http://www.op.net/~candle pgman@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026
 

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Tom, you mentioned you needed more system indexes.  I would be glad to
> create them for you.  Can you tell me which ones?

Let's see, we need an index on pg_index's indrelid column (non unique
of course).  Offhand I do not know of any others.  I would like to get
*rid* of the index(es) on pg_listener and revert async.c to its index-
free state; it seems unlikely that indexes on pg_listener will be worth
their maintenance effort.

Another idea that had come up in that thread was to get rid of
pg_attrdef completely and put its info into two new columns in
pg_attribute.  Not sure if anyone but me thought that'd be worth
the trouble.

> Also, I see a heap_getnext on pg_attribute in vacuum.c that should be
> using index scan.  Are there other places in the code where this needs
> to be changed?

Dunno; I haven't had time to go looking for suspicious heap_getnext
loops.

Another thing we had discussed was to try to unify the APIs of the
heap_getnext and index_getnext routines so that it could be fairly
transparent in calling code which one you are using.  That'd allow
support of Hiroshi's disable-system-indexes feature without so much
cruft.  If we are going to do that, it probably ought to happen before
we start adding more call sites that'll have to be fixed.
        regards, tom lane

> Another idea that had come up in that thread was to get rid of
> pg_attrdef completely and put its info into two new columns in
> pg_attribute.  Not sure if anyone but me thought that'd be worth
> the trouble.

Seems worth removing.


--  Bruce Momjian                        |  http://www.op.net/~candle pgman@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026
 

> -----Original Message-----
> From: pgsql-hackers-owner@hub.org [mailto:pgsql-hackers-owner@hub.org]On
> Behalf Of Tom Lane
> 
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Tom, you mentioned you needed more system indexes.  I would be glad to
> > create them for you.  Can you tell me which ones?
> 
> Another idea that had come up in that thread was to get rid of
> pg_attrdef completely and put its info into two new columns in
> pg_attribute.  Not sure if anyone but me thought that'd be worth
> the trouble.
>

I agree with you.
> > Also, I see a heap_getnext on pg_attribute in vacuum.c that should be
> > using index scan.  Are there other places in the code where this needs
> > to be changed?
> 
> Dunno; I haven't had time to go looking for suspicious heap_getnext
> loops.
>

There seems to be some suspicious places e.g. in command.c.
I haven't complained about it mainly because sequential
scan was convenient for my REINDEX stuff.

Though I myself use system indexes if appropiate ones
exist,I've always been suspicous. I don't think that indexes
are always best. Sequential scan is never slower than index
scan for small tables. It is reliable even in case of index
corruption. System indexes may grow big but vacuum
couldn't shrink them as pointed out in another thread..
... and so on.

> Another thing we had discussed was to try to unify the APIs of the
> heap_getnext and index_getnext routines so that it could be fairly
> transparent in calling code which one you are using.  That'd allow
> support of Hiroshi's disable-system-indexes feature without so much
> cruft.  If we are going to do that, it probably ought to happen before
> we start adding more call sites that'll have to be fixed.
>

Yes unification is required. Current separate routines seems to
have misled even main developers if index scan is preferable.

Regards.

Hiroshi Inoue
Inoue@tpf.co.jp