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

> -----Original Message-----
> From: pgsql-hackers-owner(at)hub(dot)org [mailto:pgsql-hackers-owner(at)hub(dot)org]On
> Behalf Of Tom Lane
>
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)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(at)tpf(dot)co(dot)jp