> I have a table of about 3 million records. The table A has many
> columns, among them are ID (Primary key, BigInt, auto-increasing), and
> update_time (DateTime).
>
> An ascending query completes in about 1 second:
> 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
>
> Adopted plan is: PLAN(A INDEX(PK_TMC_EVENTS))

This is not "ascending query", but "random order query" !

> but a descending query returns after 2 minutes:
> 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 desc
>
> Adopted plan is: PLAN(A ORDER A_IDX_ID_DESC)

For the tables of that size
- reduce the number of returned rows as much as possible using WHERE clause
- do not use use index for ordering

Ivan
http://www.volny.cz/iprenosil/interbase/