Note that the only performance images appear when you update a table with indexes, because the specified indexes must be maintained as the data in the table changes. If you select only a table, indexes can usually only improve performance, rather than degrade. Obviously, you need to get rid of the table of any unused indexes, since they waste disk space unnecessarily and must be maintained again as the table changes over time.
Usually you need to check your execution plans to see which indexes are actually used based on your queries. Be sure to collect statistics before doing this to get accurate results. Performing this check of the execution plan is important, b / c, even if you have an index in the column, the optimizer may not select it based on factors such as column power, etc. On large tables where queries are very selective (i.e. you have 1M users, and you ask where userid = 'x', which will return 1 row), you will see that the index to select is worth the cost of maintenance. Small tables, say continental name tables, as a rule, will not benefit from the index as many times as a full table scan would be preferable to an index scan. This is because it will cost more to read the index, and then read the data block referenced by the index, and not just read a small amount of data directly. Again, these things will need to be checked based on your specific tables and needs, and all this is done by looking at the execution plans.
Favorable concatenated indexes with single column indexes if your queries lend themselves to these types of queries. For example, if your where clauses usually do something like "where emp_fname = 'jim' and emp_lname = 'smith', create one concatenated index for fname and lname instead of separate indexes for each column.