Обсуждение: 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
>>> On Mon, Nov 5, 2007 at 10:42 AM, in message <B10E6810AC2A2F4EA7550D072CDE8760197DD5@SAB-FENWICK.sab.uiuc.edu>, "Campbell, Lance" <lance@uiuc.edu> wrote: > How can I [. . .] get rid of some unnecessary indexes Here's what I periodically run to look for unused indexes: select relname, indexrelname from pg_stat_user_indexes where indexrelname not like '%_pkey' and idx_scan = 0 order by relname, indexrelname ; We omit the primary keys from the list (based on our naming convention) because they are needed to ensure integrity. -Kevin