--- In firebird-support@yahoogroups.com, Svein Erling Tysvær <svein.erling.tysvaer@...> wrote:

>
> Title:
> >select id1, max(id2) from table group by id1 SLOW
>
> Query:
> >select cust_id, min(fcstgentimestamp) AS maxgentimestampforcust
> > from forecast
> > group by cust_id
>
> Hi Tim,
> do you mean min(fcstgentimestamp) or max(fcstgentimestamp)? The primer I would expect to be as quick as possible and not do a table scan, but if the latter, you might require a descending index on fcstgentimestamp. Not knowing whether you have 10 customers with 1000000 fcstgentimestamps each or 1000000 customers with 10 fcstgentimestamps each, it is impossible to guess whether such an index would be useful or not. An answer might also depend on which Firebird version you use, e.g. I think an expression index might be a possible solution, but that is recently introduced into Firebird, so if you're on, say, Fb 1.5, then that is not an option.
>
> Also note that the more common way for primary keys to be defined in Firebird, would be on a single field with no meaning attached (e.g. add a field PK_FORECAST that you typically fill through a BEFORE INSERT trigger accessing a generator). That makes changes easier, e.g. if you at one point in time discover that it would be good to be able to store more than one row with the same CUST_ID, FCSTGENTIMESTAMP, RUNOUTTIMESTAMP and DEMAND or want to change the field definition for one of those fields.
>
> HTH,
> Set
>

This do table scan by all index values
i suppose that group by should be improved by FBTeam in this case

if index can be used for aggregate then query should go through only that index without look at every row in table

of course if you add some where or having clause then situation will be different..

Karol Bieniaszewski