Hi Andrew, mind if I add a few remarks even though I'm not a Firebird
2.0 guy?

It isn't ideal to use several indexes for tables near the end of the
plan, if one of them is on fields with low selectivity I'd try to avoid
using the index altogether. In your case, that means i.type_id and
l.action_id. Moreover, I don't see why you use LEFT [outer] JOIN and not
a simple [inner] JOIN when referring to a field of the right table in
the WHERE clause (it may occasionally be used for forcing a plan). In
your case, they produce the same result. Finally, some of your logic is
more suited in the where clause than the join clause (though I doubt
this will influence the optimizer), and due to my own preference I moved
a bit more that I'm uncertain whether belongs to the where or join
clause ;o) Try the following and report back to the list the suggested
plan and result:

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
inner join cust_subscr s on s.customer_id = c.id
and s.type_id = i.type_id+0
inner join actions a on a.type_id = i.type_id+0
inner join intr_types t on i.type_id = t.id
inner join last_actions l on l.customer_id = c.id
and l.action_id+0 = 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
and c.deleted = 0 and c.no_spam = 0
and a.relative = 1 and a.deleted = 0 and a.is_active > 0
and current_date-a.day_period >= i.event_date

Now, I do not know anything about Firebird 2.0, the above changes are
what I would have done to a Firebird 1.5 database. But it will be
interesting to see what effect my suggested changes has on the result.

HTH,
Set

Andrew Guts wrote:

> Hello Firebird team,
>
> 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
>
> Results on my powerful PC, windows (the server is much slower)
>
> Plan
> ------------------------------------------------
> PLAN JOIN (JOIN (A NATURAL, T INDEX (RDB$PRIMARY13), S INDEX
> (RDB$FOREIGN17), C INDEX (RDB$PRIMARY9), I INDEX (RDB$FOREIGN15,
> RDB$FOREIGN16)), L INDEX (RDB$FOREIGN13, RDB$FOREIGN2))