Andrew,

> I have migrated existing application from Firebird 1.5.2 to 2.0 RC3 on
> the same server. The database was restored from backup with new gbak.
> There is a query that becomes too slow right after migration. Firebird
> 1.5.2 executed the query in 2-4 seconds, but it takes about 25-40 sec
> with Firebird 2.0. I have no idea why it happened: the database is
> small, so number of records involved is not heavy.
> Could you please clarify the situation?
>
> The query is:
>
> select c.id, c.name, /*c.email,*/ i.event_date, i.event_time,
> c.hot_flag, c.no_spam, /*a.name as aname,*/
> cast(1 as smallint) as atype, t.name as itype,
> /*a.subject as template,*/ a.id as action_id, t.id as type_id
> from customers c
> inner join interactions i on i.customer_id = c.id
> and c.deleted = 0 and c.no_spam = 0
> inner join cust_subscr s on s.customer_id = c.id
> and s.type_id = i.type_id
> inner join actions a on a.type_id = i.type_id
> and a.relative = 1 and a.deleted = 0 and a.is_active > 0
> and current_date-a.day_period >= i.event_date
> inner join intr_types t on i.type_id = t.id
> left join last_actions l on l.customer_id = c.id and l.action_id =

a.id

> /* and l.event_tsc > i.event_date*/
> where
> l.event_tsc <
> i.event_date+coalesce(i.event_time, cast('00:00' as

time))+a.day_period

v2.0 has a new on-disk structure for indexes, which solves the
significant performance problem related to indexes with poor
selectivity.

While that has provided a tremendous improvement in performance, it does
have a cost -- additional disk pages for indexes, and additional
overhead when the optimizer needs to construct a bitmap using 2 or more
indexes.

From the plan,

> Adapted Plan
> ------------------------------------------------
> PLAN JOIN (JOIN (A NATURAL, T INDEX (INTEG_34), S INDEX (INTEG_44), C
> INDEX (INTEG_23), I INDEX (INTEG_39, INTEG_42)), L INDEX (INTEG_200,
> INTEG_144))

We see that table I and L are trying to use multiple indexes.

To resolve this, I would recommend that you create some compound indexes
-- an index on Deleted + No_Spam on the Customer and the Type_ID +
Relative + Deleted on the Actions and the Customer_ID and Action_ID in
the Last_Actions.



Sean