Dear all,

I am having a problem with a query running very slowly and would appreciate any insight you may have!

The query is run on the following TABLE:

/* Table: FORECAST, Owner: EPLAN */

CREATE TABLE "FORECAST"
(
"CUST_ID" INTEGER NOT NULL,
"FCSTGENTIMESTAMP" TIMESTAMP NOT NULL,
"FCSTSTARTDATE" DATE,
"CRITICALTIMESTAMP" TIMESTAMP,
"RUNOUTTIMESTAMP" TIMESTAMP NOT NULL,
"VOLUMETIMESTAMP" TIMESTAMP,
"VOLUME" DECIMAL(7,1),
"USAGERATE" DECIMAL(9,3),
"DEMAND" CHAR(1) NOT NULL,
"FLEXIBILITY" INTEGER NOT NULL,
CONSTRAINT "IDX_FORECAST" PRIMARY KEY ("CUST_ID", "FCSTGENTIMESTAMP", "RUNOUTTIMESTAMP", "DEMAND")
);

THE QUERY:

select cust_id, min(fcstgentimestamp) AS maxgentimestampforcust
from forecast
group by cust_id

In words, this query pulls out the latest record for each cust_id.

THE PLAN:
PLAN (FORECAST ORDER RDB$PRIMARY4)

As you can see, it is using the index you would hope it to use. However, when run my statistics show indexed reads to the entire number of rows in the table.

This is not what I would expect, as sure it could use a clustered index starting with cust_id, forecastgettimestamp to apply the group by and the MIN() without having to read every single record? Does anyone have any help with this please? How can I make this type of query run instantly rather than taking about one minute?

Thanks,
Tim