Hi,

> What is the best way to go about to find out about this?
>
> I have several projects that are using the FB database and I would like
> to cleanup and remove all the indices that are just taking up space and
> thus indirectly reduces performance?

It is not that easy to clearly define the usefulness of an index.
Usually indexes with a low selectivity (high number of duplicates) is a
questionable candidate for a good index, but it depends from a query
perspective.

Imagine you have an index with 1 million nodes pointing to records but
with only 2 unique values (e.g. BOOLEAN), one row only with a value of 0
and the rest with a value of 1. If you query the one row with value 0,
the index will be definitely useful, but for querying 999999 records
with value 1, it is better to scan the entire table instead of the
additional "hop" via the index to locate the row. Histograms
(distribution among unique values) will help the optimizer there to
choose a better execution plan. AFAIK histograms are planned for Firebird 4.

To get an overview across all your indices in your database from various
perspectives (size, index depth, selectivity, duplicates etc.), you can
run the gstat command-line tool and process and interpret the raw output.

But as Alexey has pointed out, there is tool support out there. HQbird
is one. Upscene's FB TraceManager
(http://www.upscene.com/fb_tracemanager/) is another product which can
help you in that area. To get a first impression how this may look lie,
have a look on that screen:
http://www.upscene.com/fb_tracemanager/images/products/fbtm/fbtm_ss_databasestatistics_index_monitoring.png

In combination with the live tracing functionality including execution
plans of executed statements etc., you may find suspicious things.



--
With regards,
Thomas Steinmaurer
http://www.upscene.com/

Professional Tools and Services for Firebird
FB TraceManager, IB LogManager, Database Health Check, Tuning etc.