From: | "Campbell, Lance" <lance(at)uiuc(dot)edu> |
---|---|
To: | <pgsql-performance(at)postgresql(dot)org> |
Subject: | index stat |
Date: | 2007-11-05 16:42:46 |
Message-ID: | B10E6810AC2A2F4EA7550D072CDE8760197DD5@SAB-FENWICK.sab.uiuc.edu |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Campbell, Lance | 2007-11-05 16:44:04 | Training Recommendations |
Previous Message | Greg Smith | 2007-11-05 16:32:28 | Re: hp ciss on freebsd |