Hi again Matthias, plenty of comments within:

--- In firebird-support@yahoogroups.com, Matthias Hanft wrote:
>> Sorry Matthias, I answered too quickly and didn't even notice you
>> had 'count(*)' as part of your query. With count(*) Firebird has
>> to check every record anyway, and the only way is to do a natural
>> scan. No index will be of any help. count(*) is always slow
>> without a where clause, though a quicker way to do something
>> similar can be found here:
>> http://www.fbtalk.net/viewtopic.php?id=164
>
> Hmmm... if the "IBExpert" plan analyzer doesn't lie to me, this is
> not completely true:
>
> select min(datetime), count(*) from requests
> where datetime<'31.12.2999';
>
> does use the (ascending) index, whereas

Sure, I worded myself incorrectly. What I meant to say was that
min(datetime) and max(datetime) may benefit from using an index, but
once you add count(*) that benefit is gone. With a where clause you're
only interested in a subset of your data and then indexes are useful.
Though count(*) can never benefit (i.e. perform better) from an index
if you want to count all records in a table and that is what I tried
to say above.

> select min(datetime), max(datetime) from requests;
>
> does not use any index at all, although there is no "count".
>
> Beside this weird effect, one can apparently always make
> Firebird use an index together with count(*) (at least,
> if you have a primary key): Just replace
>
> select count(*) from requests; /* natural */
>
> by
>
> select count(*) from requests
> where id>0; /* primary key index */
>
> At least, the Plan Analyzer tells me so. But with 10,000 rows,
> there is not yet an execution speed difference. Maybe Firebird
> does always the same, internally?

I would expect the latter to be slower than the former. At least, in
the example above, if there is no id <= 0, then using the PK is at
best redundant and I'm actually surprised if you find it not to slow
down the query.

--- Matthias Hanft also wrote:
> You're right. I thought it would be easy for Firebird to get the
> MAX value by an ascending index, too ("just use the last index
> entry"), but maybe I have not understood the index concept fully
> yet :-)

That's part of the difference between a unidirectional and a
bidirectional index ;o) Not all records are necessarily visible to the
current transaction, and what do you do if the last record isn't
visible and you need the second last record?

>> Try adding another index, a DESCending index on your timestamp
>> column. Then I'd expect Firebird to use both indexes. Though don't
>> take my word for it (I haven't tried) - test it yourself.

> Done. Here are the results:
>
> select min(datetime) --->>> uses ascending index
> select max(datetime) --->>> uses descending index
> select min(datetime), max(datetime) --->>> uses natural?!?!?!
>
> I cannot believe that it is faster to read all rows than just
> using two indexes, one after another?!

I agree with you and am surprised about your findings. Is it the same
if the table contains 1 million rows with pretty unique datetime values?

Set