RE: Performance (was: The New Slashdot Setup (includes MySql server))

> -----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:
> >> The advantage is that you can then index a bunch more of the system
> >> catalog tables, and on a bunch more attributes.  That produced some
> >> surprising speedups.
> 
> > We have indexes on all system tables that need it.
> 
> There isn't any fundamental reason why the planner can't be using an
> index to scan pg_index; we just need to code it that way.  Right now
> it's coded as a sequential scan.
> 
> Unfortunately there is no index on pg_index's indrelid column in 7.0,
> so this is not fixable without an initdb.  TODO item for 7.1, I guess.
>

I've noticed the fact since before but haven't complained.
As far as I see,pg_index won't so big. In fact Matthias's case has
only 1 page after running vacuum for pg_index.  In such cases
sequential scan is faster than index scan as you know.
I don't agree with you to increase system indexes easily.
Though I implemented REINDEX command to recover system
indexes it doesn't mean index corruption is welcome.

I know another case. pg_attrdef has no index on (adrelid,attnum)
though it has an index on (adrelid).

> More generally, someone should examine the other places where
> heap_getnext() loops occur, and see if any of them look like performance
> bottlenecks...

Please don't lose sequential scan stuff even when changes to
index scan is needed because -P option of standalone postgres
needs sequential scan for system tables.

Regards.

Regards.

Hiroshi Inoue
Inoue@tpf.co.jp