> Unfortunately it took the same time.
>
> Adopted plan was still: PLAN(A ORDER A_IDX_ID_DESC).
>
> Why does the primary key (PK_A) work much better than the descending
> index A_IDX_ID_DESC? I thought the indexes are equivalent.

At the first European Firebird Conference, I spoke a bit with Arno, and
he surprised me by saying that ascending indexes were superior to
descending in all cases but for MAX. This will have been around 17th -
20th May 2003, and I think he spoke about InterBase 6.0, a Firebird beta
version or Firebird 1.0 (I don't remember when Firebird 1.5 was
released). Things have improved a lot since then, but it may well be
true that ascending indexes are quicker than descending in Firebird 1.5.

In your case, this of course only matters if there are lots of records
meeting the criteria before considering FIRST. There are two ways to try
to make this quicker, one of them to tighten your requirements:

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

The other is to try without any index at all (since a very large portion
of the data satisfy your where clause):

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

If this doesn't help, I would consider writing a stored procedure.

Still, I thought 16 minutes simply seemed too long, so I set up a small
test environment with 3.2 million records - admittedly only three rows
that were duplicated (the ID field set through a trigger so that wasn't
duplicated) and using a DATE field rather than a DATETIME field.

Running

select first 1000 *
from TESTA
where ID between 2000 and 3000000
and UPDATEDATE between '1.5.2006' and '30.4.2007'
order by ID desc

I do get the same plan as you report, but it executes in less than half
a second! Changing to 'order by ID+0' increases the time to 12 seconds,
which is also far less than 16 minutes. So there is something very
different between our two Firebird 1.5.4 databases. Is 'A' really a
table in your case or a view/stored procedure? What does the exact
statement look like?

Set