Hi All;

I am still working over how to optimize this query to make it faster.
Trying with Postgre, I found my query slow too. So I started trying
to add some indexes combining PROC+FECH, PROC+FECH+TIPO and so, and I
found Postgres does use of that indexs making my query much faster
(50 seconds VS. 7 seconds for guetting the full records subset).
Clearly it uses that new indexes at the first step of the plan.

Then I started the same tests with Firebird, but I can't make
Firebird to take advantage of those new indexes. Here I am copying
down here the table DDL in where you can see the new added index
called MOVI_PRFE; looking at the query plan I note Firebird does not
use this new index; moreover, I note Firebird does not use the
MOVI_FECH index which to me sounds as very important for this query.

On Firebird, the results of running this query in Flamerobin is that
Firebird returns in 3 or 4 seconds the first package of records, but
then it takes very long time for retrieving every next package
records for going to the end of the resulting record subset.

Best regards,

Dany

CREATE TABLE MOVI
(
MOVI Char(10) NOT NULL,
PROC Char(10) NOT NULL,
MIEM Char(10) NOT NULL,
TIPO Char(1) NOT NULL,
AVIS Char(2) NOT NULL,
HECH Char(1) NOT NULL,
FECH Char(8) NOT NULL,
HORA Char(4) NOT NULL,
DSCR Varchar(200) NOT NULL,
FOJA Varchar(10) NOT NULL,
PRES Char(1) NOT NULL,
TEXT Blob sub_type 0,
CONSTRAINT MOVI_PK PRIMARY KEY (MOVI)
);

CREATE INDEX MOVI_FECH ON MOVI (FECH);
CREATE INDEX MOVI_HECH ON MOVI (HECH);
CREATE INDEX MOVI_MIEM ON MOVI (MIEM);
CREATE INDEX MOVI_PRES ON MOVI (PRES);
CREATE INDEX MOVI_PRFE ON MOVI (PROC,FECH);
CREATE INDEX MOVI_PROC ON MOVI (PROC);
CREATE INDEX MOVI_TIPO ON MOVI (TIPO);
GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
ON MOVI TO LEX WITH GRANT OPTION;


Preparing query: select m1."PROC",m1."FECH",m1."HORA",m1."DSCR"
from "MOVI" m1 where m1."FECH"=(select max(m2."FECH") from "MOVI" m2
where m2."PROC"=m1."PROC" and m2."TIPO" = '1' and m2."HECH" = 'P')
and m1."TIPO" = '1' and m1."HECH" = 'P'
Prepare time: 00:00:00.
Field #01: MOVI.PROC Alias:PROC Type:STRING(10)
Field #02: MOVI.FECH Alias:FECH Type:STRING(8)
Field #03: MOVI.HORA Alias:HORA Type:STRING(4)
Field #04: MOVI.DSCR Alias:DSCR Type:STRING(200)
PLAN (M2 INDEX (MOVI_PROC))
PLAN (M1 INDEX (MOVI_TIPO, MOVI_HECH))
Script execution finished.