What happens if you try:

CREATE TABLE TEST_TAB (
ID INTEGER NOT NULL PRIMARY KEY,
NUMER INTEGER,
NUMER_NEG INTEGER,
FOREIGN_KEY INTEGER);

SET TERM ^^ ;
CREATE TRIGGER TRG_NUMER_NEG FOR TEST_TAB ACTIVE BEFORE INSERT POSITION 0 AS
begin
new.NUMER_NEG = new.-NUMER
end^^
SET TERM ; ^^

CREATE INDEX TEST_TAB_IDX1 ON TEST_TAB (FOREIGN_KEY, NUMER_NEG);

Is the query

select min(t.numer_neg)
from test_tab t
where t.foreign_key = :some_value

equally slow as your MAX-query? Which Firebird version are you using?

Set
-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of tomjanczkadao
Sent: 18. august 2010 11:08
To: firebird-support@yahoogroups.com
Subject: [firebird-support] query optimization- MAX() from .. WHERE

Hi
I've got table similar to the following example:

CREATE TABLE TEST_TAB (
ID INTEGER NOT NULL PRIMARY KEY,
NUMER INTEGER,
FOREIGN_KEY INTEGER);
CREATE DESCENDING INDEX TEST_TAB_IDX1 ON TEST_TAB (FOREIGN_KEY, NUMER);

and query like this:

select max(t.numer)
from test_tab t
where t.foreign_key = :some_value

PLAN (T INDEX (TEST_TAB_IDX1))

So it uses proper index but Performance Analysis shows lot of reads (as much as count() where foreign_key = :some_value).

Is it any way to improve this query performance

Regards, Tomek