I was tested FB1.5 RC1 win32 SS (syntetic test).

Also sql1 test was discovered on
FB1.0.2 on Win32 and Linux with real large DB (SS).

Slavek

results from wisql:
See the elapsed time for all combination.
sql1:

SELECT FIRST 5 tDate FROM T WHERE tDate<'1.1.2002' AND id=0
ORDER BY tDate;

PLAN (T ORDER AB)
TDATE
===========

1-JAN-2001
1-JAN-2001
1-JAN-2001
1-JAN-2001
1-JAN-2001

Current memory = 4636212
Delta memory = 13004
Max memory = 4966508
Elapsed time= 0.09 sec
Buffers = 2048
Reads = 34
Writes 0
Fetches = 49

SELECT FIRST 5 tDate FROM T WHERE tDate<'1.1.2002' AND id=0
PLAN (T INDEX (AA))
ORDER BY tDate;

PLAN SORT ((T INDEX (AA)))
TDATE
===========

1-JAN-2001
1-JAN-2001
1-JAN-2001
1-JAN-2001
1-JAN-2001

Current memory = 4646288
Delta memory = 10076
Max memory = 4966508
Elapsed time= 0.19 sec
Buffers = 2048
Reads = 101
Writes 0
Fetches = 14640

SELECT FIRST 5 tDate FROM T WHERE tDate<'1.1.2002' AND id=100
ORDER BY tDate;

PLAN (T ORDER AB) Current memory = 4641728
Delta memory = -4560
Max memory = 4966508
Elapsed time= 1.59 sec
Buffers = 2048
Reads = 3376
Writes 0
Fetches = 3391

SELECT FIRST 5 tDate FROM T WHERE tDate<'1.1.2002' AND id=100
PLAN (T INDEX (AA))
ORDER BY tDate;

PLAN SORT ((T INDEX (AA))) Current memory = 4641716
Delta memory = -12
Max memory = 4966508
Elapsed time= 0.02 sec
Buffers = 2048
Reads = 14
Writes 0
Fetches = 19

SELECT FIRST 5 tDate FROM T WHERE tDate<'1.1.2002' AND id=-100
ORDER BY tDate;

PLAN (T ORDER AB) Current memory = 4641728
Delta memory = 12
Max memory = 4966508
Elapsed time= 1.00 sec
Buffers = 2048
Reads = 3380
Writes 0
Fetches = 3390

SELECT FIRST 5 tDate FROM T WHERE tDate<'1.1.2002' AND id=-100
PLAN (T INDEX (AA))
ORDER BY tDate;

PLAN SORT ((T INDEX (AA))) Current memory = 4641716
Delta memory = -12
Max memory = 4966508
Elapsed time= 0.00 sec
Buffers = 2048
Reads = 14
Writes 0
Fetches = 19


sql2:
SELECT id FROM T WHERE id=0 AND tDate BETWEEN '1.1.2000' AND '2.1.2000';

PLAN (T INDEX (AA)) Current memory = 4575680
Delta memory = 6380
Max memory = 4912600
Elapsed time= 0.04 sec
Buffers = 2048
Reads = 8
Writes 0
Fetches = 10

SELECT id FROM T WHERE id IN(0,1) AND tDate BETWEEN '1.1.2000' AND '2.1.2000';

PLAN (T INDEX (AA,AA)) Current memory = 4593580
Delta memory = 17900
Max memory = 4912600
Elapsed time= 0.47 sec
Buffers = 2048
Reads = 470
Writes 0
Fetches = 58483

SELECT id FROM T WHERE id=0 AND tDate BETWEEN '1.1.2000' AND '2.1.2000'
UNION
SELECT id FROM T WHERE id=1 AND tDate BETWEEN '1.1.2000' AND '2.1.2000';

PLAN (T INDEX (AA))
PLAN (T INDEX (AA)) Current memory = 4579012
Delta memory = -14568
Max memory = 4953144
Elapsed time= 0.00 sec
Buffers = 2048
Reads = 0
Writes 0
Fetches = 18

SELECT id FROM T WHERE id=0 AND tDate BETWEEN '1.1.2004' AND '2.1.2004';

PLAN (T INDEX (AA)) Current memory = 4576028
Delta memory = -2984
Max memory = 4953144
Elapsed time= 0.03 sec
Buffers = 2048
Reads = 0
Writes 0
Fetches = 9

SELECT id FROM T WHERE id IN(0,1) AND tDate BETWEEN '1.1.2004' AND '2.1.2004';

PLAN (T INDEX (AA,AA)) Current memory = 4593580
Delta memory = 17552
Max memory = 4953144
Elapsed time= 0.63 sec
Buffers = 2048
Reads = 0
Writes 0
Fetches = 58483

SELECT id FROM T WHERE id=0 AND tDate BETWEEN '1.1.2004' AND '2.1.2004'
UNION
SELECT id FROM T WHERE id=1 AND tDate BETWEEN '1.1.2004' AND '2.1.2004';

PLAN (T INDEX (AA))
PLAN (T INDEX (AA)) Current memory = 4579012
Delta memory = -14568
Max memory = 4953144
Elapsed time= 0.01 sec
Buffers = 2048
Reads = 0
Writes 0
Fetches = 18

> First (SQL example sql1):
> > AB indexd is needed for Max/Min functions in real database.
> > FB select index AB, but if I plan manualy index AA, avarege speed was incresed.
>
> > Second (SQL examlpe sql2):
> > Slow evaluating of IN (BETWEEN, OR, ...) clausule.
> > When I roll up into UNIONS speed was radicaly incresed.
>
> Which version of Firebird are you using? And, on which platform?