Postgres Pro
Downloads
Home   >   mailing lists

Thread: index stat

index stat

From
"Campbell, Lance"
Date:

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

 

Re: index stat

From
"Kevin Grittner"
Date:
>>> 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