Thread: Use of system indexes

I am looking at how the system indexes are used.

In the past, I went through and changed all system table lookups that
return a single value into system cache lookups.

I now see several cases where we are doing heap scans of system tables,
rather than using indexes.  There are cases that can return several
rows, so we can't use the cache.  However, we could use index scans
rather than heap scans.

An interesting case is the pg_listener index in commands/async.c.  Our
previous index was by relname/pid.  By changing this index to
pid/relname, I can add index scans based in pid to prevent the many heap
scans in the file.  I am sure there are other places that can be
improved.

I can start fixing them, but as I remember, someone was thinking of
making heap/index scans use the same interface.  Can I get a status on
that?

--  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 Bruce Momjian
>
> I am looking at how the system indexes are used.
>
> In the past, I went through and changed all system table lookups that
> return a single value into system cache lookups.
>
> I now see several cases where we are doing heap scans of system tables,
> rather than using indexes.  There are cases that can return several
> rows, so we can't use the cache.  However, we could use index scans
> rather than heap scans.
>

[snip]

>
> I can start fixing them, but as I remember, someone was thinking of
> making heap/index scans use the same interface.  Can I get a status on
> that?
>

In my trial implementation of ALTER TABLE DROP COLUMN in command.c,
there is a trial using
systable_beginscan(),systable_endscan(),systable_getnext()
functions.
However there would better unification of system table scan.

Regards.

Hiroshi Inoue
Inoue@tpf.co.jp

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> An interesting case is the pg_listener index in commands/async.c.  Our
> previous index was by relname/pid.  By changing this index to
> pid/relname, I can add index scans based in pid to prevent the many heap
> scans in the file.  I am sure there are other places that can be
> improved.

My opinion about that is that the pg_listener operations are better off
with no indexes at all.  pg_listener is small and *very* frequently
updated...
        regards, tom lane