hi all
LI-T3.0.0.31071.
Радость от тотального превосходства HJ над NL была недолгой...
DDL: 1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
recreate view v_dbg_log as select 1 ware_id, 2 sum_qty from rdb$database;
recreate table dbg_data(id int primary key using index dbg_data_pk, doc_id int, ware_id int);
recreate table dbg_list(id int primary key using index dbg_list_pk);
alter table dbg_data add constraint dbg_data_fk foreign key (doc_id) references dbg_list(id) using index dbg_data_fk;
recreate table dbg_log(id int primary key using index dbg_log_pk, ware_id int, qty int);
commit;
insert into dbg_list values(547);
insert into dbg_data(id, doc_id, ware_id) values(1, 547, 1001);
commit;
insert into dbg_log(id, ware_id, qty)
select 0, 1001 w, rand()*100 from rdb$database
union all
select row_number()over(), rand()*1000, rand()*100
from rdb$types a,rdb$types b,(select 1 i from rdb$types rows 20);
commit;
create index dbg_log_ware on dbg_log(ware_id);
commit;
set statistics index dbg_log_pk;
set statistics index dbg_list_pk;
set statistics index dbg_data_pk;
set statistics index dbg_data_fk;
commit;
recreate view v_dbg_log as
select ware_id, sum(qty) sum_qty
from dbg_log
group by 1;
commit;
Обратите внимание на вьюху
v_ dbg_log: она есть результат группировки по полю ware_id.
Кардинальности:
1.
2.
3.
4.
select count(*) from dbg_list h join dbg_data d on d.doc_id = h.id -- 1 запись
(эти таблицы вообще состоят из одной строки каждая)
select count(*) from dbg_log; -- 1260021 записи
select count(*) from v_dbg_log; -- 1002 записи
Тепеь делаю
SQL> set plan on;
SQL> set explain on;
- и далее три запроса.
var-1.
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
SQL> select h.id, n.sum_qty
CON> from dbg_list h
CON> join dbg_data d on d.doc_id = h.id
CON> left join v_dbg_log n on d.ware_id = n.ware_id;
Select Expression
-> Nested Loop Join (outer)
-> Nested Loop Join (inner)
-> Table "H" Full Scan
-> Filter
-> Table "D" Access By ID
-> Bitmap
-> Index "DBG_DATA_FK" Range Scan (full match)
-> Filter
-> Aggregate
-> Filter
-> Table "N DBG_LOG" Access By ID
-> Index "DBG_LOG_WARE" Range Scan (full match)
- протолкнул предикат внутрь вьюхи (
Filter ) и поэтому выполняется мгновенно, вот его трейс:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
2014-04-22T18:12:28.7320 (18178:0x7fe17972a308) EXECUTE_STATEMENT_FINISH
oltp30 (ATT_17475, SYSDBA:NONE, NONE, TCPv4:127.0.0.1)
/opt/fb30trnk/bin/isql:23028
(TRA_18671196, CONCURRENCY | WAIT | READ_WRITE)
Statement 414505:
-------------------------------------------------------------------------------
select h.id, n.sum_qty
from dbg_list h
join dbg_data d on d.doc_id = h.id
left join v_dbg_log n on d.ware_id = n.ware_id
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
PLAN JOIN (JOIN (H NATURAL, D INDEX (DBG_DATA_FK)), N DBG_LOG ORDER DBG_LOG_WARE)
1 records fetched
0 ms, 16 fetch(es)
Table Natural Index Update Insert Delete
**********************************************************************************
DBG_DATA 1
DBG_LIST 1
DBG_LOG 1
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
SQL> select h.id, n.sum_qty
CON> from dbg_list h
CON> join dbg_data d on d.doc_id = h.id
CON> inner join v_dbg_log n on d.ware_id = n.ware_id;
Select Expression
-> Filter
-> Hash Join (inner)
-> Nested Loop Join (inner)
-> Table "H" Full Scan
-> Filter
-> Table "D" Access By ID
-> Bitmap
-> Index "DBG_DATA_FK" Range Scan (full match)
-> Record Buffer (record length: 33)
-> Aggregate
-> Table "N DBG_LOG" Access By ID
-> Index "DBG_LOG_WARE" Full Scan
- выполняется хреново:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
2014-04-22T18:13:08.9470 (18178:0x7fe17972a308) EXECUTE_STATEMENT_FINISH
oltp30 (ATT_17475, SYSDBA:NONE, NONE, TCPv4:127.0.0.1)
/opt/fb30trnk/bin/isql:23028
(TRA_18671196, CONCURRENCY | WAIT | READ_WRITE)
Statement 414506:
-------------------------------------------------------------------------------
select h.id, n.sum_qty
from dbg_list h
join dbg_data d on d.doc_id = h.id
inner join v_dbg_log n on d.ware_id = n.ware_id
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
PLAN HASH (JOIN (H NATURAL, D INDEX (DBG_DATA_FK)), N DBG_LOG ORDER DBG_LOG_WARE)
1 records fetched
1804 ms, 3780836 fetch(es)
Table Natural Index Update Insert Delete
**********************************************************************************
DBG_DATA 1
DBG_LIST 1
DBG_LOG 1260021
Насколько могу понять, хеш-таблица была построена по связке dbg_list join dbg_data (кардинальность - всего 1 строка), а вот после этого ФБ стал идти по *ВСЕЙ* таблице DBG_LOG (т.к. идёт соединение с "её" вьюхой). В итоге - 1.26 млн индексных чтений, 3.78 млн фетчей и 1.8 сек времени :(
var-3.
Пытаюсь обмануть оптимизатор, задавая условие соединения без строго равенства (чтобы он не соблазнялся на HJ):
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
SQL> select h.id, n.sum_qty
CON> from dbg_list h
CON> join dbg_data d on d.doc_id = h.id
CON> inner join v_dbg_log n on d.ware_id >= n.ware_id and d.ware_id <= n.ware_id;
Select Expression
-> Filter
-> Nested Loop Join (inner)
-> Aggregate
-> Table "N DBG_LOG" Access By ID
-> Index "DBG_LOG_WARE" Full Scan
-> Nested Loop Join (inner)
-> Table "H" Full Scan
-> Filter
-> Table "D" Access By ID
-> Bitmap
-> Index "DBG_DATA_FK" Range Scan (full match)
- и вижу, что он берёт ведущей... ВЬЮХУ!
И получаю почти то же самое в трейсе, что с HJ:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
2014-04-22T18:19:18.6810 (18178:0x7fe17972a308) EXECUTE_STATEMENT_FINISH
oltp30 (ATT_17475, SYSDBA:NONE, NONE, TCPv4:127.0.0.1)
/opt/fb30trnk/bin/isql:23028
(TRA_18671196, CONCURRENCY | WAIT | READ_WRITE)
Statement 414511:
-------------------------------------------------------------------------------
select h.id, n.sum_qty
from dbg_list h
join dbg_data d on d.doc_id = h.id
inner join v_dbg_log n on d.ware_id >= n.ware_id and d.ware_id <= n.ware_id
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
PLAN JOIN (N DBG_LOG ORDER DBG_LOG_WARE, JOIN (H NATURAL, D INDEX (DBG_DATA_FK)))
1 records fetched
1786 ms, 3790846 fetch(es)
Table Natural Index Update Insert Delete
**********************************************************************************
DBG_DATA 1002
DBG_LIST 1002
DBG_LOG 1260021
По варианту-2 вопрос простой: там ничего не менялось в HJ в последнее время ? Ибо я стопудово проверял (месяца 2 взад) какой-то очень близкий к этому раскладу запрос, и там было всё пучком.
По варианту-3 вопрос тоже очевидный: с каког будуна ведущим источником берется тот, что имеет 1002 строки, вместо того, что будет иметь всего 1 строку (я про dbg_list h join dbg_data d on d.doc_id = h.id - см выше) ?
PS. Только не пинайте, что запросы с left join vs inner join не эквивалентны. Ну добавлю я юнионом недостающие записи (хотя особенности схемы гарантируют, что их не будет) - что дальше-то, всё равно огребаем тормоза.
