Re: Cleaning up indexes - Mailing list pgsql-performance

From Gregory S. Williamson
Subject Re: Cleaning up indexes
Date
Msg-id 71E37EF6B7DCC1499CEA0316A256832801D4B846@loki.wc.globexplorer.net
Whole thread Raw
In response to Cleaning up indexes  (Martin Foster <martin@ethereal-realms.org>)
List pgsql-performance
If you have set up the postgres instance to write stats, the tables pg_stat_user_indexes, pg_statio_all_indexes and so
(usethe \dS option at the psql prompt to see these system tables); also check the pg_stat_user_tables table and similar
beastsfor information on total access, etc. Between these you can get a good idea of what indexes are not being used,
andfrom the sequentail scan info on tables perhaps some idea of what may need some indexes. 

HTH,

Greg Williamson
DBA
GlobeXplorer LLC

-----Original Message-----
From:    Martin Foster [mailto:martin@ethereal-realms.org]
Sent:    Thu 9/23/2004 3:16 PM
To:    pgsql-performance@postgresql.org
Cc:
Subject:    [PERFORM] Cleaning up indexes
My database was converted from MySQL a while back and has maintained all
of the indexes which were previously used.   Tt the time however, there
were limitations on the way PostgreSQL handled the indexes compared to
MySQL.

Meaning that under MySQL, it would make use of a multi-column index even
if the rows within did not match.    When the conversion was made more
indexes were created overall to correct this and proceed with the
conversion.

Now the time has come to clean up the used indexes.   Essentially, I
want to know if there is a way in which to determine which indexes are
being used and which are not.   This will allow me to drop off the
unneeded ones and reduce database load as a result.

And have things changed as to allow for mismatched multi-column indexes
in version 7.4.x or even the upcoming 8.0.x?

    Martin Foster
    martin@ethereal-realms.org

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
      joining column's datatypes do not match




pgsql-performance by date:

Previous
From: Martin Foster
Date:
Subject: Cleaning up indexes
Next
From: Kris Jurka
Date:
Subject: Re: Large # of rows in query extremely slow, not using