[Firebird-test] Firebird 2.0 performance issue

[Andrew G. <dev...@fe...>]

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