--- In firebird-support@yahoogroups.com, "Adam" <s3057043@...> wrote:

>
> Firebird 1.5 (iSQL set stats)
>
> Reads = 45
> Writes 0
> Fetches = 391
>
> My Plan was PLAN (TMP ORDER IXTMP)
>

Dear Adam,

Thanks for trying it in your machine. How about the RowsAffected
statistics in your case? It seems puzzling to me that the number of
rows affected is equal to the number of rows with Column1 > 100.
Seems that it scanned the whole 89901 records.

> I find it puzzling that your query did so many reads. I wonder if

you

> don't have a lot of garbage in your table, and the slowness is
> actually the records being garbage collected as it goes? Maybe try a
> backup-restore cycle to be sure.

I thought of this too. So I rebuilt the whole database from scratch
for testing.

>
> Also, I am not entirely convinced the plan you posted is valid. At
> least I could not get it to work with my table and index names as it
> complains about the word 'index'.
>

I'm using FB2.0.1.

> By the way, the reason Ivan said you need an order by has nothing to
> do with this performance problem. It is simply that 'get me 10
> arbitrary records that meet this criteria' is not usually a useful
> piece of information. When you talk about the first 10 records, you
> normally are thinking of an ordered set of records.

Agree. Without the order clause, the optimizer might even need to
dig out all the records before return the first 10 records in natural
order. That's why I tried again with the order clause.

By the way, I even thought of the possibility that it might be the
FIRST clause that caused the problem. So I wrote a simple program in
Delphi with dbexpress/ibexpress to simple open the query "select *
from My_Table where Column1 > 100 order by Column1, Column2". The
time is now 4 seconds. This is much better than 28 seconds but it is
still much higher because I expect it to be sub-second if it can
choose the right index.

PK