Странное время выполнения left join stored_poc FB 2.0.3 & FB 2.1

Thu, 06 Sep 2007 14:15:43 -0700 

При переходе с Yaffil на ФБ заметили "странное" поведение запросов с
left join stored_poc
проверялось на фб 2.0.2, 2.0.3 и 2.1.16459 Firebird 2.1 Beta 1

Пример базы:
Версия ОДС      11.1
Размер страницы 8192
Размер файла БД 90 MB

SET SQL DIALECT 3;

SET NAMES WIN1251;

CREATE TABLE GD_GOOD (ID INTEGER NOT NULL, USR$MN_MAINGOODKEY
INTEGER);
ALTER TABLE GD_GOOD ADD CONSTRAINT PK_GD_GOOD PRIMARY KEY (ID);
CREATE INDEX GD_GOOD_IDX1 ON GD_GOOD (USR$MN_MAINGOODKEY);

CREATE TABLE INV_MOVEMENT (ID INTEGER NOT NULL, MOVEMENTDATE DATE,
CONTACTKEY INTEGER, GOODKEY INTEGER);
ALTER TABLE INV_MOVEMENT ADD CONSTRAINT PK_INV_MOVEMENT PRIMARY KEY
(ID);
CREATE DESCENDING INDEX INV_MOVEMENT_IDX1 ON INV_MOVEMENT
(MOVEMENTDATE);
CREATE DESCENDING INDEX INV_MOVEMENT_IDX2 ON INV_MOVEMENT (GOODKEY,
CONTACTKEY, MOVEMENTDATE);

CREATE PROCEDURE USR$INV_GETREMAINS (goodkey integer, contactkey
integer, movementdate date) returns (mdate date)
as
begin
   for
     select first(1) m.movementdate from
        inv_movement m
     where
         m.goodkey = :goodkey
        and m.contactkey = :contactkey
        and m.movementdate <= :movementdate
     order by
        m.movementdate desc
     into :mdate
   do
   begin
      suspend;
    end
end

Селективность индексов:

gd_good

ПК Индекс На поле Выражение Уникальный Активность Тип сортировки
Статистика
0 GD_GOOD_IDX1 USR$MN_MAINGOODKEY 0 1 По возрастанию
0,000288433802779763937
1 PK_GD_GOOD ID 1 1 По возрастанию 0,000170765022630803287

inv_movement
ПК Индекс На поле Выражение Уникальный Активность Тип сортировки
Статистика
0 INV_MOVEMENT_IDX1 MOVEMENTDATE 0 1 По убыванию
0,00291545200161635876
0 INV_MOVEMENT_IDX2 GOODKEY,CONTACTKEY,MOVEMENTDATE 0 1 По убыванию
0,00000129859336084336974
1 PK_INV_MOVEMENT ID 1 1 По возрастанию 0,000000885739609657321125

Кол-во записей:
inv_movement - 1129000
gd_good - 5856

Выполнение запроса с процедурой:

select
  g.id
from
  gd_good g
  left join usr$inv_getremains( g.id, :contactkey, :movementdate) b on
1 = 1
where
  g.usr$mn_maingoodkey = :USR$MAINGOODKEY

План
PLAN JOIN (G INDEX (GD_GOOD_IDX1), M ORDER INV_MOVEMENT_IDX1 INDEX
(INV_MOVEMENT_IDX2))

Адаптированный план
PLAN JOIN (G INDEX (GD_GOOD_IDX1), M ORDER INV_MOVEMENT_IDX1 INDEX
(INV_MOVEMENT_IDX2))

------ Performance info ------
Prepare time = 0ms
Execute time = 1s 110ms
Avg fetch time = 111,00 ms
Current memory = 17 485 228
Max memory = 17 786 000
Memory buffers = 2 048
Reads from disk to cache = 0
Writes from cache to disk = 0
Fetches from cache = 5 109

Удивляет долгое выполнение запроса по сравнению со схожим запросом :

Выполнение запроса с вложенным селктом :

select
  g.id
from
  gd_good g
  left join (SELECT first(1) m.goodkey, m.contactkey, m.movementdate
FROM
                  inv_movement m
              ORDER by
                m.movementdate desc) b on
       b.goodkey = g.id
      and b.contactkey = :contactkey
     and b.movementdate <= :MovementDate
where
   g.usr$mn_maingoodkey = :USR$MAINGOODKEY

План
PLAN JOIN (G INDEX (GD_GOOD_IDX1), B M ORDER INV_MOVEMENT_IDX1)

Адаптированный план
PLAN JOIN (G INDEX (GD_GOOD_IDX1), B M ORDER INV_MOVEMENT_IDX1)

------ Performance info ------
Prepare time = 0ms
Execute time = 0ms
Avg fetch time = 0,00 ms
Current memory = 17 487 168
Max memory = 17 786 000
Memory buffers = 2 048
Reads from disk to cache = 0
Writes from cache to disk = 0
Fetches from cache = 75

Если отключить индекс inv_movement по movementdate

то результат первого запроса:

План
PLAN JOIN (G INDEX (GD_GOOD_IDX1), SORT (M INDEX (INV_MOVEMENT_IDX2)))

Адаптированный план
PLAN JOIN (G INDEX (GD_GOOD_IDX1), SORT (M INDEX (INV_MOVEMENT_IDX2)))

------ Performance info ------
Prepare time = 16ms
Execute time = 0ms
Avg fetch time = 0,00 ms
Current memory = 17 487 248
Max memory = 17 625 028
Memory buffers = 2 048
Reads from disk to cache = 0
Writes from cache to disk = 0
Fetches from cache = 994

второго

План
PLAN JOIN (G INDEX (GD_GOOD_IDX1), SORT (B M NATURAL))

Адаптированный план
PLAN JOIN (G INDEX (GD_GOOD_IDX1), SORT (B M NATURAL))

------ Performance info ------
Prepare time = 0ms
Execute time = 38s 94ms
Avg fetch time = 3 809,40 ms
Current memory = 17 489 228
Max memory = 76 211 692
Memory buffers = 2 048
Reads from disk to cache = 85 491
Writes from cache to disk = 0
Fetches from cache = 22 751 155

С уважением, Леонид Агафонов

Ответить