Hello Sean,

The problem is probably that you are using an indexed walk

If we take a look at your plans:

PLAN(A ORDER PK_A)

It is basically going through the table from first record to last,
checking whether the record matches, then the second record and so on,
stopping when it gets to 1000 records returned.

PLAN(A ORDER A_IDX_ID_DESC)

This is doing the reverse of A.

Your records are probably stored in pretty close to ascending order,
so there would be much more thrashing in a reverse walk. This can be
exagerated if the records at the end of the table don't meet your
where clause and it has to do a lot of walking before hitting the
right one.

Using hard disk technology, seek time is a real killer, so usually it
is quicker to read the data in with storage order, and then sort in
memory. The Firebird optimiser seems to prefer an indexed walk when
you have a first and order by together.

I would attempt to trick it to read in storage order. Try this one:

select first 1000 * from A
where update_time >= '13-JUN-2006 00:00:00'
and update_time <= '13-JUN-2007 00:00:00'
and id <= 3000000
and id >= 2000
order by id+0 desc

Adam