|
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 217.333.0382 http://webservices.uiuc.edu |
