index stat

PostgreSQL:8.2.4

I am collecting statistics info now on my database. I have used the
following two queries:

select * from pg_stat_all_indexes;

select * from pg_statio_all_indexes;

How can I use the information from these two queries to better optimize
my indexes? Or maybe even get rid of some unnecessary indexes.

Example output:

relid | indexrelid | schemaname | relname |
indexrelname | idx_blks_read | idx_blks_hit

---------+------------+---------------+-----------------------+---------
--------------------------+---------------+--------------

16801 | 57855 | a | screen |
screen_index1 | 1088 | 213618

16801 | 57857 | a | screen |
screen_index3 | 905 | 201219

16803 | 16805 | pg_toast | pg_toast_16801 |
pg_toast_16801_index | 3879 | 1387471

16978 | 16980 | pg_toast | pg_toast_16976 |
pg_toast_16976_index | 0 | 0

942806 | 942822 | b | question_result_entry |
question_result_entry_index1 | 18 | 0

942806 | 942824 | b | question_result_entry |
question_result_entry_index2 | 18 | 0

942806 | 942828 | b | question_result_entry |
question_result_entry_index3 | 18 | 0

relid | indexrelid | schemaname | relname |
indexrelname | idx_scan | idx_tup_read | idx_tup_fetch

---------+------------+---------------+-----------------------+---------
--------------------------+-----------+--------------+---------------

16801 | 57855 | a | screen
| screen_index1 | 48693 | 1961745 |
1899027

16801 | 57857 | a | screen
| screen_index3 | 13192 | 132214 |
87665

16803 | 16805 | pg_toast | pg_toast_16801 |
pg_toast_16801_index | 674183 | 887962 |
887962

16978 | 16980 | pg_toast | pg_toast_16976 |
pg_toast_16976_index | 0 | 0 |
0

942806 | 942822 | b | question_result_entry |
question_result_entry_index1 | 0 | 0 |
0

942806 | 942824 | b | question_result_entry |
question_result_entry_index2 | 0 | 0 |
0

942806 | 942828 | b | question_result_entry |
question_result_entry_index3 | 0 | 0 |
0

Thanks,

Lance Campbell

Project Manager/Software Architect

Web Services at Public Affairs

University of Illinois

217.333.0382

http://webservices.uiuc.edu