Subject Re: GROUP BY clause is very slow
Author Adam
> I am trying the following:

Firstly, the count operator is not able to use an index in a MGA
database like Firebird. Because the index contains records not
visible to your transaction, count will need to visit each data page
for any record that could potentially be counted to see whether that
record is visible to this transaction.

It is able to use an index on User_Key if available to narrow down
this search.

> This query takes 214 seconds to return the value - 785,000 records
> 60,000,000 records.
> The view internally joins 3 tables. It has index on the FUND_PRICE
> (with 60,000,000 odd records) but not on the other lighter tables.

You say it has an index on a single field but not on lighter tables,
the foreign key constraint automatically adds an index. What is the
SQL for the view, and what plan is it using?

It is possible as well that a foreign key index might have poor
selectivity and you can force the join to ignore the index, but
without and SQL, it is a bit hard to tell.