Re: pg_* Tables

>>

>> Environment:

>> 

>> Solaris 10

>> Postgres 8.3.3

>> 

>> I’m getting high sequential scans for some pg_* tables:

>> 

>> <database>=# select relname, sum(seq_scan) as seq_scan,sum(seq_tup_read)

>> as seq_tup_read,sum(idx_scan) as idx_scan, sum(idx_tup_fetch) as idx_tup_fetch,

>> sum(n_tup_ins) as n_tup_ins, sum(n_tup_upd) as n_tup_upd, sum(n_tup_del) as n_tup_del

>> from  pg_stat_all_tables group by 1 order by 2 desc limit 4;

>>              relname             | seq_scan | seq_tup_read | idx_scan | idx_tup_fetch | n_tup_ins | n_tup_upd | n_tup_del

>> ---------------------------------+----------+--------------+----------+---------------+-----------+-----------+-----------

>> <table> |  6896498 |     91295702 |   107663 |        469057 |    103966 |    103966 |    103966

>> pg_authid                       |  3119053 |    125950392 | 12000732 |      12000718 |         1 |         1 |         0

>> pg_am                           |  2642438 |      2642440 |        5 |             5 |         0 |         0 |         0

>> pg_database                     |  1349020 |     14771768 |  6953392 |       6953392 |         0 |         0 |         0

<...>

>> Is this normal?  Is it advisable to index pg_* tables?

Do you vacuum the database regularly ?

What are the number of rows in those tables ?

It is possible that the planner is doing a sequential scan because the tables are small and that's faster than doing indexed reads.

Greg Williamson