I have the following SQL statement:

select * from ClientINfo
where Client_Id in (Select Max(Client_ID) from ClientInfo)

where Client_ID is the primary key of ClientInfo.

But the SQL above is very slow (almost 2 minutes). The plan is nautrual:

PLAN (CLIENTINFO NATURAL)
PLAN (CLIENTINFO NATURAL)

Adapted Plan
PLAN (CLIENTINFO NATURAL) PLAN (CLIENTINFO NATURAL)

------ Performance info ------
Prepare time = 0ms
Execute time = 1m 45s 422ms
Avg fetch time = 105,422.00 ms
Current memory = 852,332
Max memory = 1,035,096
Memory buffers = 2,048
Reads from disk to cache = 0
Writes from cache to disk = 0
Fetches from cache = 112,181,717


The DDL is:

CREATE TABLE CLIENTINFO (
CLIENT_ID DM_KEY NOT NULL /* DM_KEY = INTEGER NOT NULL */,
...
);


ALTER TABLE CLIENTINFO ADD PRIMARY KEY (CLIENT_ID);

...

In IBExpert, it shows an index on the CLIENT_ID field.

Couple questions: (1) how can I fix this? and (2) why isn't it picking
up the index?

Thanks
Ed Dressel