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))

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))

Query Time
------------------------------------------------
Prepare : 0.00 ms
Execute : 13 156.00 ms
Avg fetch time: 548.17 ms

Memory
------------------------------------------------
Current: 1 985 876
Max : 2 019 904
Buffers: 8 192

Operations
------------------------------------------------
Read : 0
Writes : 8
Fetches: 378 270


Enchanced Info:
+--------------------------+-----------+-----------+-------------+---------+---------+---------+
| Table Name | Records | Indexed | Non-Indexed |
Updates | Deletes | Inserts |
| | Total | reads | reads
| | | |
+--------------------------+-----------+-----------+-------------+---------+---------+---------+
| ACTIONS| 0 | 0 | 50 |
0 | 0 | 0 |
| CUSTOMERS| 0 | 14940 | 0 |
0 | 0 | 0 |
| CUST_SUBSCR| 0 | 14940 | 0 |
0 | 0 | 0 |
| INTERACTIONS| 0 | 14529 | 0 |
0 | 0 | 0 |
| INTR_TYPES| 0 | 6 | 0 |
0 | 0 | 0 |
| LAST_ACTIONS| 0 | 11255 | 0 |
0 | 0 | 0 |
+--------------------------+-----------+-----------+-------------+---------+---------+---------+


These are involved tables:

create table customers (
id integer not null primary key,
name varchar(100) not null,
email varchar(100) not null,
phone varchar(100),
name_alias varchar(100),
company_name varchar(100),
www varchar(250),
info blob sub_type 1,
no_spam smallint default 0,
hot_flag smallint default 0,
deleted smallint default 0 not null
);
/* 6327 records */

create index idx_cust_email on customers(email);
create index idx_cust_name on customers(name);
create index idx_customer_alias on customers(name_alias);

/* customer interaction kinds */
create table intr_types (
id smallint not null primary key,
name varchar(100) not null,
ord_num smallint,
ntype smallint default 0
);
/* 29 records */

/* customer interactions history */
create table interactions (
id integer not null primary key,
type_id smallint not null references intr_types(id),
event_date date not null,
event_time time default current_time,
product_id integer references products(id),
customer_id integer not null references customers(id),
auction_id integer references auctions(id),
campaign_id integer references campaigns(id),
ref_num varchar(50),
revenue numeric(18, 4),
period integer,
details blob sub_type 1
);
/* 7983 records */

create index idx_intr_date on interactions(event_date);
create index idx_intr_refn on interactions(ref_num);

/* kinds of postings to subscribers */
create table actions (
id smallint not null primary key,
name varchar(100),
type_id smallint references intr_types(id),
start_date date,
day_period smallint,
relative smallint not null,
from_addr varchar(100),
subject varchar(250),
text blob sub_type 1,
is_active smallint default 1,
deleted smallint default 0 not null
);
/* 50 records */

/* customer subscriptions */
create table cust_subscr (
type_id smallint not null references intr_types(id),
customer_id integer not null references customers(id)
);
/* 7391 records */

create table last_actions (
action_id smallint not null references actions(id) on delete cascade,
customer_id integer not null references customers(id) on delete cascade,
event_tsc timestamp not null
);
/* 38078 records */

create index idx_last_action on last_actions(event_tsc);

I'm ready to send you the database backup if it helps.

Thank you,

Andrew