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
