Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Firebird, InterBase Новый топик    Ответить
Топик располагается на нескольких страницах: Ctrl  назад   1 [2]      все
 Re: Почему FB 2.5.8 не использует FK индекс в запросе  [new]
hvlad
Member

Откуда:
Сообщений: 11555
Dimitry Sibiryakov
Merge (INDEX + INDEX) = 10 фетчей на запись.
Тут ты ошибаешься, для ORDER INDEX только первый ключ требует чтения узлов от корня до листа, дальше идёт перебор ключей только на листовом уровне.
Т.е. можно считать, что для ORDER INDEX требуется 3 фетча на запись (1 btree page, 1 pointer page, 1 data page).
Таким образом MERGE(ORDER INDEX, ORDER INDEX) потребует 6 фетчей на запись (если нет промахов, т.е. соединение 1 к 1).
Точнее 3N + 3M фетчей при наличии N и M записей в таблицах.

Для NATURAL + INDEX потребуется 2N + 5N фетчей.

Однако это далеко не полный анализ стоимости, т.к. он совсем не учитывает IO.

Добавлю также, что в fb3 кол-во фетчей PP сокращено, и для NATURAL оно стремится к кол-ву самих PP, которых сильно меньше чем DP.
7 ноя 21, 21:33    [22392891]     Ответить | Цитировать Сообщить модератору
 Re: Почему FB 2.5.8 не использует FK индекс в запросе  [new]
ggreggory
Member

Откуда:
Сообщений: 199
Dimitry Sibiryakov

ggreggory
но могу написать процедуры, эмулирующие эту ситуацию:

Походу, не смог. У тебя join внутренний, а в топике всё время речь про внешний.


Ну хорошо, select count(*) from (select tbl.text, join_tbl.text as join_text from tbl left join join_tbl on tbl.id = join_tbl.id), без разницы, таблицы полностью идентичны, что left, что right, что inner, всё одно.
7 ноя 21, 21:34    [22392893]     Ответить | Цитировать Сообщить модератору
 Re: Почему FB 2.5.8 не использует FK индекс в запросе  [new]
hvlad
Member

Откуда:
Сообщений: 11555
ggreggory
CREATE TABLE TBL (
    ID    INTEGER NOT NULL,
    TEXT  VARCHAR(4096)
);
А чего не VARCHAR(100500) ? Зачем скромничать ?

ggreggory
insert into join_tbl select * from tbl^
Прекрасно, заливаем дочернюю (детальную) таблицу в порядке возрастания значений FK.
Где такое бывает ?
Где вообще бывает связь PK:FK 1:1 ?
Но, ок, предположим, что у нас 1:1, но FK заполненный строго по возрастанию - это уже слишком.

ggreggory
select count(*) from test_index = 26 секунд
...
Среднее время выполнения select count(*) from (select tbl.text, join_tbl.text as join_text from tbl join join_tbl on tbl.id = join_tbl.id) у меня 10 секунд.
План ?
Разница в 2.6 раза не смущает ?

ggreggory
В обоих случаях всё в памяти, Reads from disk to cache = 0
Оптимизатор не может на это рассчитывать.

ggreggory
Таким образом если бы этот запрос делался не по индексу, а объединением, т.е. быстрее на 7 секунд
С какой стати эта разница PSQL запросов переносится один-в-один на гипотетический "запрос с объединеним" ?
7 ноя 21, 21:41    [22392894]     Ответить | Цитировать Сообщить модератору
 Re: Почему FB 2.5.8 не использует FK индекс в запросе  [new]
hvlad
Member

Откуда:
Сообщений: 11555
ggreggory
Вопрос в том, почему Firebird не хочет использовать merge join в этом случае
Потому что random IO от индекса к таблице убьёт весь потенциальный выигрыш.
Попробуй этот запрос в MSSQL не для кластерного и не для покрывающего индекса.

Сообщение было отредактировано: 7 ноя 21, 21:45
7 ноя 21, 21:44    [22392895]     Ответить | Цитировать Сообщить модератору
 Re: Почему FB 2.5.8 не использует FK индекс в запросе  [new]
Dimitry Sibiryakov
Member

Откуда:
Сообщений: 54803

ggreggory
без разницы

Ты не врубился: у тебя в процедурах идёт исключительно inner join для
примитивного соединения 1:1 по первичному ключу. Вот когда ты их напишешь так,
чтобы они выдавали правильный результат для M:N, тогда их и можно будет
сравнивать с запросом.

Posted via ActualForum NNTP Server 1.5

7 ноя 21, 21:51    [22392897]     Ответить | Цитировать Сообщить модератору
 Re: Почему FB 2.5.8 не использует FK индекс в запросе  [new]
ggreggory
Member

Откуда:
Сообщений: 199
hvlad
А чего не VARCHAR(100500) ? Зачем скромничать ?


Ну, это как-бы эмулирует "другие поля таблицы", не один же ключ в ней должен быть.

hvlad
...но FK заполненный строго по возрастанию - это уже слишком.


Ок, согласен, пример переделал (там еще перепутаны были исходная и присоединяемая таблица):

+
-- Создание таблиц

CREATE TABLE JOIN_TBL (
    ID    INTEGER NOT NULL,
    TEXT  VARCHAR(4096)
);

ALTER TABLE JOIN_TBL ADD CONSTRAINT PK_TBL PRIMARY KEY (ID);

CREATE TABLE TBL (
    ID    INTEGER NOT NULL,
    TEXT  VARCHAR(4096)
);

ALTER TABLE TBL ADD CONSTRAINT FK_TBL FOREIGN KEY (ID) REFERENCES JOIN_TBL (ID);

create generator GEN_JOIN_TBL_ID;

-- Заполнение тестовыми данными

set term ^;

execute block as
declare variable i integer;
begin
  i = 0;
  while (i < 1000000) do
  begin
    insert into join_tbl(id,text) values (gen_id(GEN_JOIN_TBL_ID, 1), cast(rand() as varchar(30)));
    i = i + 1;
  end
end^

insert into tbl select * from join_tbl order by cast(text as double precision)^

-- Тестовые процедуры, иллюстрирующие работу JOIN

create procedure TEST_INDEX
returns (text varchar(4096), join_text varchar(4096))
as
  DECLARE tbl CURSOR FOR (select id, text from tbl order by id);
begin
  open tbl;
  while (true) do
  begin
    fetch tbl;
    if (row_count = 0) then break;
    select text from join_tbl where id = tbl.id into :join_text;
    text = tbl.text;
    suspend;
  end
end^

create procedure TEST_MERGE
returns (text varchar(4096), join_text varchar(4096))
as
  DECLARE tbl CURSOR FOR (select id, text from tbl order by id);
  DECLARE join_tbl CURSOR FOR (select id, text from join_tbl order by id);
begin
  open tbl;
  open join_tbl;
  fetch tbl;
  fetch join_tbl;
  while (true) do
  begin
    if (tbl.id = join_tbl.id) then
    begin
      text = tbl.text;
      join_text = join_tbl.text;
      suspend;
      fetch tbl;
      if (row_count = 0) then break;
    end else
    begin
      if (tbl.id < join_tbl.id) then
        fetch tbl;
      else
        fetch join_tbl;
      if (row_count = 0) then break;
    end
  end
end^


hvlad
Разница в 2.6 раза не смущает ?


Так там PSQL, а тут DML, конечно то же самое процедурой будет медленнее, я про это и написал выше.

hvlad
ggreggory
В обоих случаях всё в памяти, Reads from disk to cache = 0
Оптимизатор не может на это рассчитывать.


Кэш я увеличил для иллюстрации корректности сравнения цифр, чтение с диска всегда сильно портит результат.

hvlad
С какой стати эта разница PSQL запросов переносится один-в-один на гипотетический "запрос с объединеним" ?


Скажем так, это оценка. У меня нет какого-либо другого инструмента, чтобы проверить, "чтобы было если бы Firebird мог так делать".

Планы и результаты:

select count(*) from test_index:
+
------ Performance info ------
Prepare time = 31ms
Execute time = 15s 750ms
Avg fetch time = 15 750,00 ms
Current memory = 1 747 296 368
Max memory = 2 758 462 928
Memory buffers = 102 400
Reads from disk to cache = 0
Writes from cache to disk = 0
Fetches from cache = 7 018 615


select count(*) from test_merge
+
------ Performance info ------
Prepare time = 15ms
Execute time = 12s 63ms
Avg fetch time = 12 063,00 ms
Current memory = 1 747 296 368
Max memory = 2 758 462 928
Memory buffers = 102 400
Reads from disk to cache = 0
Writes from cache to disk = 0
Fetches from cache = 5 018 617


select count(*) from (select tbl.text, join_tbl.text as join_text from tbl left join join_tbl on tbl.id = join_tbl.id)
+
Plan
--------------------------------------------------------------------------------
PLAN JOIN (TBL NATURAL, JOIN_TBL INDEX (PK_TBL))

------ Performance info ------
Prepare time = 31ms
Execute time = 5s 406ms
Avg fetch time = 5 406,00 ms
Current memory = 1 747 310 800
Max memory = 2 758 462 928
Memory buffers = 102 400
Reads from disk to cache = 0
Writes from cache to disk = 0
Fetches from cache = 6 078 060


Разница уменьшилась, теперь 3 секунды. Вы можете попробовать у себя запустить этот тест. Возможно, у вас будут другие соотношения.
7 ноя 21, 23:32    [22392916]     Ответить | Цитировать Сообщить модератору
 Re: Почему FB 2.5.8 не использует FK индекс в запросе  [new]
ggreggory
Member

Откуда:
Сообщений: 199
hvlad
ggreggory
Вопрос в том, почему Firebird не хочет использовать merge join в этом случае
Потому что random IO от индекса к таблице убьёт весь потенциальный выигрыш..


А если все страницы данных будут в кэше? Я конечно понимаю, что есть БД, которые ну никак в ОЗУ не впихиваются, но большинство же вполне влезает.

Dimitry Sibiryakov

Ты не врубился: у тебя в процедурах идёт исключительно inner join для
примитивного соединения 1:1 по первичному ключу. Вот когда ты их напишешь так,
чтобы они выдавали правильный результат для M:N, тогда их и можно будет
сравнивать с запросом.


По TEST_INDEX не согласен, там четко пробегается по всем записям первой таблицы и ищется по PK запись в связанной таблице.

По TEST_MERGE - согласен, написал на скорую руку, но для этого конкретного примера она выдает корректный результат.
7 ноя 21, 23:38    [22392920]     Ответить | Цитировать Сообщить модератору
 Re: Почему FB 2.5.8 не использует FK индекс в запросе  [new]
Dimitry Sibiryakov
Member

Откуда:
Сообщений: 54803

ggreggory
По TEST_INDEX не согласен, там четко пробегается по всем записям первой таблицы
и ищется по PK запись в связанной таблице.

Добавь в JOIN_TBL только каждую вторую запись из TBL, но дважды. "Вот тогда-то
мы и повеселимся..." (с)

Posted via ActualForum NNTP Server 1.5

8 ноя 21, 00:47    [22392931]     Ответить | Цитировать Сообщить модератору
 Re: Почему FB 2.5.8 не использует FK индекс в запросе  [new]
Dimitry Sibiryakov
Member

Откуда:
Сообщений: 54803

И вообще не заморачивайся пока с миллионами и производительностью. Для начала
просто заставь свои процедуры выдавать корректный результат на простых данных:
https://dbfiddle.uk/?rdbms=firebird_3.0&fiddle=580347a8e7361360ec67fda82e3fa78c

Posted via ActualForum NNTP Server 1.5

8 ноя 21, 00:59    [22392935]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: Ctrl  назад   1 [2]      все
Все форумы / Firebird, InterBase Ответить