> SQL databases are always in for a surprise about performance of simple
> statements.
>
> I have a table with the column 'ID' as BIGINT unique primary key.
>
> The table has about 4000000 entries, the ID counts up without gaps.
>
> A simple
>
> select max(ID) from T
>
> takes about 14 seconds complete.
>
> Execution plan:
>
> PLAN (T NATURAL)
>
> So Firebird seems to do plan a full table scan. (Hallo, anyone at home?
> I have an unique index on that column ;-) )
>
> Some databases can do so much better here: The same statement on a 100%
> identical Derby database completes immediately, as does on an Oracle 10g XE.
>
> Do I miss something ? Any suggestions ?

You need an DESCENDING index on the primary key column to speed up a MAX
operation, but I wonder what you are doing with that value then?
Hopefully not incrementing the value by 1 and use that as primary key
value then?


--
With regards,
Thomas Steinmaurer (^TS^)
Firebird Technology Evangelist

http://www.upscene.com/
http://www.firebirdsql.org/en/firebird-foundation/