Hi,

Thanks for that. Yes, that seemed to be the cause. I added an index on the join and clause columns and now the query returns in under a second. 

I didn't realise combining the indexes would be so expensive. Something to remember!

Cheers,


________________________________
From: "Leyne, Sean" <Sean@...>
To: "firebird-support@yahoogroups.com" <firebird-support@yahoogroups.com>
Sent: Wednesday, July 31, 2013 8:54 PM
Subject: RE: [firebird-support] FB 1.5 Query Optimiser Question



 

> From: firebird-support@yahoogroups.com [mailto:firebird-
> support@yahoogroups.com] On Behalf Of vendee928
> Sent: Wednesday, July 31, 2013 6:40 AM
> To: firebird-support@yahoogroups.com
> Subject: [firebird-support] FB 1.5 Query Optimiser Question
>
> I have two tables. TABLE1 contains about 21k records and TABLE2 contains
> around 2.5m records. There are indexes on the joined fields, and both where
> clause fields. I've recalculated statistics on each.
>
> The query below takes around 10 minutes to return with 500 records.
>
> A) SELECT A.F1 FROM TABLE1 A INNER JOIN TABLE2 B ON A.F1=B.F1 WHERE
> A.CLAUSE1=0 AND B.CLAUSE2=100 (FB Suggested plan: PLAN JOIN (A INDEX
> (IDX_TABLE1),B INDEX (IDX_TABLE2,PK_TABLE2)):)

What fields make up the indexes?

Do you have compound indexes defined for Table1 F1 and Clause1 and Table2 F1 and Clause2?

> Any possible explanation would be very welcome. My guess is that there's so
> many records (Around a third) on TABLE2 with a CLAUSE2 value of 100 that
> using the index on this field as part of the select is somehow slowing things
> down to a crawl. The selectivity of this index is 0.2. However, using the index
> on the table alone is still relatively fast, hence my confusion.

Even though you have indexes defined, as the PLAN shows the system is having to AND 2 of the indexes together, which is a slow process.

The query optimizer in FB v1.5 was not the "sharpest knife".

Sean




[Non-text portions of this message have been removed]