Subject Re: Compound Index
Author pokka_chi
--- In, "Adam" <s3057043@...> wrote:
> I don't have time today to upgrade my laptop to test this problem,
> perhaps you could try 1.5 to see if this is a new issue in the 2.x
> optimiser.

I've tried 1.5.4 and found the result to be similar to 2.0.1. But
then, 1.5.4 is the version with some bug fixes in 2.0 back ported to
1.5. Not sure if this is related.

> It certainly should be sub-second.

I thought so. I have a hypothesis. Perhaps Ann or Helen can comment.
I think this might be related to MGA. When FB receives my SQL
statement (select first 10 * from MY_TABLE where Column1 > 100 order by
Column1, Column2), it doesn't use the parameter for FIRST. So it will
tag all the 89901 records with my transaction id. This essentially is
equivalent to a table scan. The parameter for FIRST is only used in
the output stage to restrict the number of records returned.