Re: Cleaning up indexes

Martin Foster wrote:
> 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.

Just for clarification, PostgreSQL will use an a,b,c index for a, (a,b),
and (a,b,c), but not for (a,c). Are you saying MySQL uses the index for
(a,c)? This item is on our TODO list:

* Use index to restrict rows returned by multi-key index when used with
non-consecutive keys to reduce heap accesses

For an index on col1,col2,col3, and a WHERE clause of col1 = 5 and
col3 = 9, spin though the index checking for col1 and col3 matches,
rather than just col1

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

As someone already pointed out, the pg_stat* tables will show you what
indexes are used.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073