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

Откуда:
Сообщений: 673
Всем доброго дня,

столкнулся с проблемой,
есть простой запрос:
 select * from prouserroles pur where pur.proid is not null

вот план
PLAN (PUR NATURAL)

почему не используется индекс FK_PROUSERROLES_PRO?

+
/******************************************************************************/
/****              Generated by IBExpert 04.11.2021 09:27:58               ****/
/******************************************************************************/

/******************************************************************************/
/****     Following SET SQL DIALECT is just for the Database Comparer      ****/
/******************************************************************************/
SET SQL DIALECT 3;



/******************************************************************************/
/****                                Tables                                ****/
/******************************************************************************/



CREATE TABLE PROUSERROLES (
    FIRMENID  INTEGER NOT NULL,
    USERID    INTEGER NOT NULL,
    ROLEID    CHAR(38) NOT NULL,
    PROID     CHAR(38) NOT NULL,
    LCH_DATE  TIMESTAMP,
    LCH_USER  VARCHAR(20)
);



/******************************************************************************/
/****                             Primary keys                             ****/
/******************************************************************************/

ALTER TABLE PROUSERROLES ADD CONSTRAINT PKPROUSERROLES PRIMARY KEY (FIRMENID, USERID, ROLEID, PROID);


/******************************************************************************/
/****                             Foreign keys                             ****/
/******************************************************************************/

ALTER TABLE PROUSERROLES ADD CONSTRAINT FK_PROUSERROLES_PRO FOREIGN KEY (PROID) REFERENCES PROJEKTE (PROID) ON DELETE NO ACTION ON UPDATE NO ACTION;


/******************************************************************************/
/****                               Triggers                               ****/
/******************************************************************************/



SET TERM ^ ;



/******************************************************************************/
/****                         Triggers for tables                          ****/
/******************************************************************************/



/* Trigger: TR_PROUSERROLES_FID */
CREATE OR ALTER TRIGGER TR_PROUSERROLES_FID FOR PROUSERROLES
ACTIVE BEFORE INSERT OR UPDATE POSITION 2
AS
BEGIN
 if (new.FIRMENID is null) then
   new.FIRMENID = 1;
END
^

/* Trigger: TR_PROUSERROLES_LCH */
CREATE OR ALTER TRIGGER TR_PROUSERROLES_LCH FOR PROUSERROLES
ACTIVE BEFORE INSERT OR UPDATE POSITION 1
AS
BEGIN
 new.LCH_DATE = current_timestamp;
 new.LCH_USER = RDB$GET_CONTEXT('USER_SESSION', 'CURR_USER');
END
^
SET TERM ; ^



/******************************************************************************/
/****                              Privileges                              ****/
/******************************************************************************/
4 ноя 21, 11:31    [22391879]     Ответить | Цитировать Сообщить модератору
 Re: Почему FB 2.5.8 не использует FK индекс в запросе  [new]
hlopotun
Member

Откуда:
Сообщений: 673
hlopotun,

это был упрощённый пример, тут он тоже не используется:
select distinct pro.proid as pid from projekte pro LEFT JOIN prouserroles pur ON pro.proid=pur.proid


PLAN SORT (JOIN (PRO NATURAL, PUR INDEX (FK_PROUSERROLES_PRO)))
4 ноя 21, 11:36    [22391882]     Ответить | Цитировать Сообщить модератору
 Re: Почему FB 2.5.8 не использует FK индекс в запросе  [new]
hlopotun
Member

Откуда:
Сообщений: 673
а если так то используется:
select * from prouserroles pur where pur.proid like '{%'
4 ноя 21, 11:38    [22391885]     Ответить | Цитировать Сообщить модератору
 Re: Почему FB 2.5.8 не использует FK индекс в запросе  [new]
a7exander
Member

Откуда:
Сообщений: 39
hlopotun
почему не используется индекс FK_PROUSERROLES_PRO?


Здесь как минимум потому что индекс бесполезен) У вас поле "not null" и вы условие поставили is not null - зачем индекс??
4 ноя 21, 11:43    [22391889]     Ответить | Цитировать Сообщить модератору
 Re: Почему FB 2.5.8 не использует FK индекс в запросе  [new]
hlopotun
Member

Откуда:
Сообщений: 673
a7exander,

ок, not null опустим
почему тут не используется?
select distinct pro.proid as pid from projekte pro LEFT JOIN prouserroles pur ON pur.proid = pro.proid

Plan
--------------------------------------------------------------------------------
PLAN SORT (JOIN (PRO NATURAL, PUR INDEX (FK_PROUSERROLES_PRO)))
4 ноя 21, 11:44    [22391891]     Ответить | Цитировать Сообщить модератору
 Re: Почему FB 2.5.8 не использует FK индекс в запросе  [new]
hlopotun
Member

Откуда:
Сообщений: 673
hlopotun,

сорри, просмотрел, там DISTINCT и на нём спотыкается а индекс используется
4 ноя 21, 11:46    [22391892]     Ответить | Цитировать Сообщить модератору
 Re: Почему FB 2.5.8 не использует FK индекс в запросе  [new]
a7exander
Member

Откуда:
Сообщений: 39
hlopotun
a7exander,

ок, not null опустим
почему тут не используется?
PUR INDEX (FK_PROUSERROLES_PRO)



может кто то не умеет читать план?
4 ноя 21, 11:50    [22391895]     Ответить | Цитировать Сообщить модератору
 Re: Почему FB 2.5.8 не использует FK индекс в запросе  [new]
hlopotun
Member

Откуда:
Сообщений: 673
hlopotun,

блин, но почему тогда
select distinct pro.proid from projekte pro

не пользует primary key

PLAN SORT ((PRO NATURAL))
+
/******************************************************************************/
/****              Generated by IBExpert 04.11.2021 09:49:40               ****/
/******************************************************************************/

/******************************************************************************/
/****     Following SET SQL DIALECT is just for the Database Comparer      ****/
/******************************************************************************/
SET SQL DIALECT 3;



/******************************************************************************/
/****                                Tables                                ****/
/******************************************************************************/



CREATE TABLE PROJEKTE (
    PROID      CHAR(38) NOT NULL,
    FIRMENID   INTEGER NOT NULL,
    MANDANT    INTEGER NOT NULL,
    PRONR      VARCHAR(40),
    SUCH       VARCHAR(60),
    AUFTRTYP   CHAR(40),
    SUBDATUM   TIMESTAMP,
    RVNR       VARCHAR(40),
    KOSTELLE1  VARCHAR(20),
    KDNR       VARCHAR(20),
    KDSUCH     VARCHAR(40),
    KATUNR     INTEGER,
    KATBEZ     VARCHAR(40),
    PHASE      VARCHAR(40),
    STATUS     VARCHAR(40),
    INFO       VARCHAR(40),
    BLUSERUNR  INTEGER,
    DELETED    CHAR(1),
    LCH_DATE   TIMESTAMP,
    LCH_USER   VARCHAR(20)
);



/******************************************************************************/
/****                             Primary keys                             ****/
/******************************************************************************/

ALTER TABLE PROJEKTE ADD PRIMARY KEY (PROID);


/******************************************************************************/
/****                             Foreign keys                             ****/
/******************************************************************************/

ALTER TABLE PROJEKTE ADD FOREIGN KEY (FIRMENID) REFERENCES FIRMENINFO (ID);


/******************************************************************************/
/****                               Indices                                ****/
/******************************************************************************/

CREATE DESCENDING INDEX IDX_PROJEKTE_FIRMA ON PROJEKTE (FIRMENID, DELETED);


/******************************************************************************/
/****                               Triggers                               ****/
/******************************************************************************/



SET TERM ^ ;



/******************************************************************************/
/****                         Triggers for tables                          ****/
/******************************************************************************/



/* Trigger: TR_PROJEKTE_DEL */
CREATE OR ALTER TRIGGER TR_PROJEKTE_DEL FOR PROJEKTE
ACTIVE BEFORE INSERT OR UPDATE POSITION 2
AS
BEGIN
 if (new.DELETED is null or new.DELETED = '') then
   new.DELETED = 'F';
END
^

/* Trigger: TR_PROJEKTE_FID */
CREATE OR ALTER TRIGGER TR_PROJEKTE_FID FOR PROJEKTE
ACTIVE BEFORE INSERT OR UPDATE POSITION 2
AS
BEGIN
 if (new.FIRMENID is null) then
   new.FIRMENID = 1;
END
^

/* Trigger: TR_PROJEKTE_GEN */
CREATE OR ALTER TRIGGER TR_PROJEKTE_GEN FOR PROJEKTE
ACTIVE BEFORE INSERT OR UPDATE POSITION 0
AS
BEGIN
 if (new.PROID is null)
   then new.PROID = '{' || uuid_to_char(gen_uuid()) || '}';
END
^

/* Trigger: TR_PROJEKTE_LCH */
CREATE OR ALTER TRIGGER TR_PROJEKTE_LCH FOR PROJEKTE
ACTIVE BEFORE INSERT OR UPDATE POSITION 1
AS
BEGIN
 new.LCH_DATE = current_timestamp;
 new.LCH_USER = RDB$GET_CONTEXT('USER_SESSION', 'CURR_USER');
END
^

/* Trigger: TR_PROJEKTE_LOG */
CREATE OR ALTER TRIGGER TR_PROJEKTE_LOG FOR PROJEKTE
ACTIVE AFTER INSERT OR UPDATE POSITION 3
AS
   DECLARE v_ch_fields BLOB SUB_TYPE TEXT = '';
   DECLARE v_newvalues BLOB SUB_TYPE TEXT = '';
   DECLARE v_GroupID VARCHAR (38);
   DECLARE v_inserting CHAR(1);
 BEGIN
      IF (INSERTING OR (new.PROID IS DISTINCT FROM old.PROID)) THEN BEGIN
        v_ch_fields = v_ch_fields || 'PROID' || ';';
        v_newvalues = v_newvalues || 'PROID' || '=' || COALESCE(new.PROID, '') || ';';
   END
   IF (INSERTING OR (new.FIRMENID IS DISTINCT FROM old.FIRMENID)) THEN BEGIN
        v_ch_fields = v_ch_fields || 'FIRMENID' || ';';
        v_newvalues = v_newvalues || 'FIRMENID' || '=' || COALESCE(new.FIRMENID, '') || ';';
   END
   IF (INSERTING OR (new.MANDANT IS DISTINCT FROM old.MANDANT)) THEN BEGIN
        v_ch_fields = v_ch_fields || 'MANDANT' || ';';
        v_newvalues = v_newvalues || 'MANDANT' || '=' || COALESCE(new.MANDANT, '') || ';';
   END
   IF (INSERTING OR (new.PRONR IS DISTINCT FROM old.PRONR)) THEN BEGIN
        v_ch_fields = v_ch_fields || 'PRONR' || ';';
        v_newvalues = v_newvalues || 'PRONR' || '=' || COALESCE(new.PRONR, '') || ';';
   END
   IF (INSERTING OR (new.SUCH IS DISTINCT FROM old.SUCH)) THEN BEGIN
        v_ch_fields = v_ch_fields || 'SUCH' || ';';
        v_newvalues = v_newvalues || 'SUCH' || '=' || COALESCE(new.SUCH, '') || ';';
   END
   IF (INSERTING OR (new.AUFTRTYP IS DISTINCT FROM old.AUFTRTYP)) THEN BEGIN
        v_ch_fields = v_ch_fields || 'AUFTRTYP' || ';';
        v_newvalues = v_newvalues || 'AUFTRTYP' || '=' || COALESCE(new.AUFTRTYP, '') || ';';
   END
   IF (INSERTING OR (new.SUBDATUM IS DISTINCT FROM old.SUBDATUM)) THEN BEGIN
        v_ch_fields = v_ch_fields || 'SUBDATUM' || ';';
        v_newvalues = v_newvalues || 'SUBDATUM' || '=' || COALESCE(new.SUBDATUM, '') || ';';
   END
   IF (INSERTING OR (new.RVNR IS DISTINCT FROM old.RVNR)) THEN BEGIN
        v_ch_fields = v_ch_fields || 'RVNR' || ';';
        v_newvalues = v_newvalues || 'RVNR' || '=' || COALESCE(new.RVNR, '') || ';';
   END
   IF (INSERTING OR (new.KOSTELLE1 IS DISTINCT FROM old.KOSTELLE1)) THEN BEGIN
        v_ch_fields = v_ch_fields || 'KOSTELLE1' || ';';
        v_newvalues = v_newvalues || 'KOSTELLE1' || '=' || COALESCE(new.KOSTELLE1, '') || ';';
   END
   IF (INSERTING OR (new.KDNR IS DISTINCT FROM old.KDNR)) THEN BEGIN
        v_ch_fields = v_ch_fields || 'KDNR' || ';';
        v_newvalues = v_newvalues || 'KDNR' || '=' || COALESCE(new.KDNR, '') || ';';
   END
   IF (INSERTING OR (new.KDSUCH IS DISTINCT FROM old.KDSUCH)) THEN BEGIN
        v_ch_fields = v_ch_fields || 'KDSUCH' || ';';
        v_newvalues = v_newvalues || 'KDSUCH' || '=' || COALESCE(new.KDSUCH, '') || ';';
   END
   IF (INSERTING OR (new.KATUNR IS DISTINCT FROM old.KATUNR)) THEN BEGIN
        v_ch_fields = v_ch_fields || 'KATUNR' || ';';
        v_newvalues = v_newvalues || 'KATUNR' || '=' || COALESCE(new.KATUNR, '') || ';';
   END
   IF (INSERTING OR (new.KATBEZ IS DISTINCT FROM old.KATBEZ)) THEN BEGIN
        v_ch_fields = v_ch_fields || 'KATBEZ' || ';';
        v_newvalues = v_newvalues || 'KATBEZ' || '=' || COALESCE(new.KATBEZ, '') || ';';
   END
   IF (INSERTING OR (new.PHASE IS DISTINCT FROM old.PHASE)) THEN BEGIN
        v_ch_fields = v_ch_fields || 'PHASE' || ';';
        v_newvalues = v_newvalues || 'PHASE' || '=' || COALESCE(new.PHASE, '') || ';';
   END
   IF (INSERTING OR (new.STATUS IS DISTINCT FROM old.STATUS)) THEN BEGIN
        v_ch_fields = v_ch_fields || 'STATUS' || ';';
        v_newvalues = v_newvalues || 'STATUS' || '=' || COALESCE(new.STATUS, '') || ';';
   END
   IF (INSERTING OR (new.INFO IS DISTINCT FROM old.INFO)) THEN BEGIN
        v_ch_fields = v_ch_fields || 'INFO' || ';';
        v_newvalues = v_newvalues || 'INFO' || '=' || COALESCE(new.INFO, '') || ';';
   END
   IF (INSERTING OR (new.BLUSERUNR IS DISTINCT FROM old.BLUSERUNR)) THEN BEGIN
        v_ch_fields = v_ch_fields || 'BLUSERUNR' || ';';
        v_newvalues = v_newvalues || 'BLUSERUNR' || '=' || COALESCE(new.BLUSERUNR, '') || ';';
   END
   IF (INSERTING OR (new.DELETED IS DISTINCT FROM old.DELETED)) THEN BEGIN
        v_ch_fields = v_ch_fields || 'DELETED' || ';';
        v_newvalues = v_newvalues || 'DELETED' || '=' || COALESCE(new.DELETED, '') || ';';
   END


      IF (INSERTING) THEN
     v_inserting = 'T';
   ELSE
     v_inserting = 'F';

   v_GroupID =  new.PROID;

   IF (v_ch_fields <> '') THEN BEGIN
     INSERT INTO CH_LOG
       (CH_TABLE, CH_RECORDID, CH_DISPLAYRECORDID, CH_GROUPID, CH_FIELDS, CH_NEW_VALS, CH_CREATED, CH_USER, CH_DATE)
     VALUES
       ('PROJEKTE', new.PROID, new.PRONR, :v_GROUPID, :v_ch_fields, :v_newvalues, :v_inserting, new.LCH_USER, new.LCH_DATE);
     END
 END
^
SET TERM ; ^



/******************************************************************************/
/****                              Privileges                              ****/
/******************************************************************************/
4 ноя 21, 11:50    [22391896]     Ответить | Цитировать Сообщить модератору
 Re: Почему FB 2.5.8 не использует FK индекс в запросе  [new]
KreatorXXI
Member

Откуда: Москва
Сообщений: 1119
hlopotun,

ну какой primary key? Вы же условие никакое не ставите! Поставьте хотя order. Может будет перебирать по primary key. Опять же - какой смысл? Что Вы хотите? В Вашем случае по натуралу самый быстрый поиск. ФБ рулит!!! Я бы возмутился в обратном случае - зачем дёргать индексы, если надо проверить тупо все записи?
4 ноя 21, 14:29    [22391930]     Ответить | Цитировать Сообщить модератору
 Re: Почему FB 2.5.8 не использует FK индекс в запросе  [new]
hvlad
Member

Откуда:
Сообщений: 11551
hlopotun,

какой смысл в distinct, если выбирается только PK ?
4 ноя 21, 14:33    [22391932]     Ответить | Цитировать Сообщить модератору
 Re: Почему FB 2.5.8 не использует FK индекс в запросе  [new]
hlopotun
Member

Откуда:
Сообщений: 673
hvlad
hlopotun,

какой смысл в distinct, если выбирается только PK ?


да, действительно. Извиняюсь. Неудачно обрезал пример, потерялся смысл, полный код выглядит так:
select PO_STATUS from
(select * from v_55_uebersicht v INNER JOIN
(select distinct pro.proid as pid, u.id, u.username, u.isadmin from projekte pro LEFT JOIN prouserroles pur ON pro.proid=pur.proid RIGHT JOIN (select username, isadmin, id from v_85_zavuserroles where (username = 'SYSOP'))
u ON u.ID = pur.userid or u.isadmin = 'T')
 roles ON cast (case when (v.proid is null) or (v.proid = 'min') then '{00000000-0000-0000-0000-000000000000}'
 else v.proid end as varchar (38)) = roles.pid)
 where (AUFTRID = '{0002152D-FBD7-45FA-9468-DD400F89CC7F}')

и проблема в этой части
...select distinct pro.proid as pid, u.id, u.username, u.isadmin from projekte pro LEFT JOIN prouserroles pur ON pro.proid=pur.proid RIGHT JOIN ....


Plan
--------------------------------------------------------------------------------
PLAN SORT ((V TK_TECHORDERS INDEX (IDX_TK_TECHORDERS_UPDT)))
PLAN (V TK_TECHOBJECTS INDEX (RDB$FOREIGN25))
PLAN (V TK_SITEINFOS INDEX (IDX_TK_SITEINFOS_TO))
PLAN (V TK_SITEINFOS INDEX (IDX_TK_SITEINFOS_TO))
PLAN SORT ((V TK_PURCHASEORDERS INDEX (IDX_TK_PURCHASEORDERS_UPDT)))
PLAN MERGE (SORT (SORT (JOIN (JOIN (JOIN (ROLES U V_85_ZAVUSERROLES U INDEX (IDX_ZAVUSER_USERNAME), ROLES U V_85_ZAVUSERROLES RA INDEX (PKUSERROLES)), ROLES U V_85_ZAVUSERROLES RS INDEX (PKUSERROLES)), JOIN (ROLES PRO NATURAL, ROLES PUR INDEX (FK_PROUSERROLES_PRO))))), SORT (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (V A INDEX (RDB$PRIMARY4), V PRO INDEX (RDB$PRIMARY2)), V BL INDEX (RDB$PRIMARY17)), V BEARB INDEX (RDB$PRIMARY17)), V AUFMBEARB INDEX (RDB$PRIMARY17)), V TECHORD INDEX (RDB$PRIMARY22)), V TECHOBJ INDEX (RDB$PRIMARY24)), V SI_A INDEX (RDB$PRIMARY27)), V SI_B INDEX (RDB$PRIMARY27)), V POORD INDEX (RDB$PRIMARY30)), V B INDEX (RDB$PRIMARY4)), V SEN SE ORDER IDX_TK_SERVICEENTRIES_DTAG_ASC INDEX (IDX_TK_SERVICEENTRIES_DTAG_ASC))))

Adapted Plan
--------------------------------------------------------------------------------
PLAN SORT ((V TK_TECHORDERS INDEX (IDX_TK_TECHORDERS_UPDT)))
PLAN (V TK_TECHOBJECTS INDEX (INTEG_86))
PLAN (V TK_SITEINFOS INDEX (IDX_TK_SITEINFOS_TO))
PLAN (V TK_SITEINFOS INDEX (IDX_TK_SITEINFOS_TO))
PLAN SORT ((V TK_PURCHASEORDERS INDEX (IDX_TK_PURCHASEORDERS_UPDT)))
PLAN MERGE (SORT (SORT (JOIN (JOIN (JOIN (ROLES U V_85_ZAVUSERROLES U INDEX (IDX_ZAVUSER_USERNAME), ROLES U V_85_ZAVUSERROLES RA INDEX (PKUSERROLES)), ROLES U V_85_ZAVUSERROLES RS INDEX (PKUSERROLES)), JOIN (ROLES PRO NATURAL, ROLES PUR INDEX (FK_PROUSERROLES_PRO))))), SORT (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (V A INDEX (INTEG_12), V PRO INDEX (INTEG_6)), V BL INDEX (INTEG_57)), V BEARB INDEX (INTEG_57)), V AUFMBEARB INDEX (INTEG_57)), V TECHORD INDEX (INTEG_62)), V TECHOBJ INDEX (INTEG_64)), V SI_A INDEX (INTEG_67)), V SI_B INDEX (INTEG_67)), V POORD INDEX (INTEG_100)), V B INDEX (INTEG_12)), V SEN SE ORDER IDX_TK_SERVICEENTRIES_DTAG_ASC INDEX (IDX_TK_SERVICEENTRIES_DTAG_ASC))))
4 ноя 21, 15:38    [22391958]     Ответить | Цитировать Сообщить модератору
 Re: Почему FB 2.5.8 не использует FK индекс в запросе  [new]
hvlad
Member

Откуда:
Сообщений: 11551
hlopotun
и проблема в этой части
...select distinct pro.proid as pid, u.id, u.username, u.isadmin from projekte pro LEFT JOIN prouserroles pur ON pro.proid=pur.proid RIGHT JOIN ....


JOIN (ROLES PRO NATURAL, ROLES PUR INDEX (FK_PROUSERROLES_PRO))

Тут нет проблемы, ибо невозможно использовать индексный доступ по условию джойна для обеих таблиц.
Если есть доп. условия, то могут быть использованы доп. индексы. Но здесь не этот случай.
4 ноя 21, 15:46    [22391961]     Ответить | Цитировать Сообщить модератору
 Re: Почему FB 2.5.8 не использует FK индекс в запросе  [new]
hlopotun
Member

Откуда:
Сообщений: 673
hlopotun,

если точнее вот вырезанный кусок:
select distinct pro.proid as pid, u.id, u.username, u.isadmin from projekte pro
LEFT JOIN prouserroles pur ON pur.proid=pro.proid
RIGHT JOIN (select username, isadmin, id from v_85_zavuserroles where (username = 'SYSOP'))
u ON u.ID = pur.userid or u.isadmin = 'T'

Plan
--------------------------------------------------------------------------------
PLAN SORT (JOIN (JOIN (JOIN (U V_85_ZAVUSERROLES U INDEX (IDX_ZAVUSER_USERNAME), U V_85_ZAVUSERROLES RA INDEX (PKUSERROLES)), U V_85_ZAVUSERROLES RS INDEX (PKUSERROLES)), JOIN (PRO NATURAL, PUR INDEX (FK_PROUSERROLES_PRO))))


и его Performance Analysis

К сообщению приложен файл. Размер - 76Kb
4 ноя 21, 15:52    [22391966]     Ответить | Цитировать Сообщить модератору
 Re: Почему FB 2.5.8 не использует FK индекс в запросе  [new]
hlopotun
Member

Откуда:
Сообщений: 673
hvlad,

ok, понятно. Спасибо
4 ноя 21, 15:53    [22391967]     Ответить | Цитировать Сообщить модератору
 Re: Почему FB 2.5.8 не использует FK индекс в запросе  [new]
kdv
Member

Откуда: iBase.ru
Сообщений: 30244
hlopotun
JOIN (PRO NATURAL, PUR INDEX (FK_PROUSERROLES_PRO))))

в left/right join без дополнительных условий таблица со стороны left (или right) всегда читается натуралом.
Как вы себе представляете альтернативу? На пальцах попробуйте придумать (я не шучу, перебрать соответствие всех
пальцев одной руки и части пальцев на другой).
6 ноя 21, 22:22    [22392662]     Ответить | Цитировать Сообщить модератору
 Re: Почему FB 2.5.8 не использует FK индекс в запросе  [new]
ggreggory
Member

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

Как вы себе представляете альтернативу?


Если соединяется по условию PK=FK, то по-идее merge join должен использовать индексы обеих таблиц для сортировки, а соединение получаться уже даром.

Вопрос в том, почему Firebird не хочет использовать merge join в этом случае. Мне кажется, что join двух больших таблиц должен в этом случае работать значительно быстрее.
7 ноя 21, 13:06    [22392746]     Ответить | Цитировать Сообщить модератору
 Re: Почему FB 2.5.8 не использует FK индекс в запросе  [new]
kdv
Member

Откуда: iBase.ru
Сообщений: 30244
ggreggory
Если соединяется по условию PK=FK, то по-идее merge join должен использовать индексы обеих таблиц для сортировки

Еще раз:
a left join b on a.id = b.id
здесь из A выбираются ВСЕ записи, зачем в этом случае использовать индекс, merge join и прочие ужасы?
Тут достаточно прохода по всем записям по A с индексным поиском в B. И план выглядит как
PLAN (A NATURAL, B INDEX ...)
не надо тут больше ничего придумывать, это максимально оптимально выполняется.
Вот если есть доп. условие по A - да, будет индекс использоваться.
Или если нет индекса по B, то тогда будет hash join (в ФБ 3).
Вы лучше почитайте
https://www.ibase.ru/dataaccesspaths
7 ноя 21, 14:36    [22392766]     Ответить | Цитировать Сообщить модератору
 Re: Почему FB 2.5.8 не использует FK индекс в запросе  [new]
ggreggory
Member

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

не надо тут больше ничего придумывать, это максимально оптимально выполняется.


Угу. Не только вы можете кидаться статейками, я тоже могу:

автор
...соединение слиянием очень часто является лучшим выбором для соединения больших наборов данных..


Ведь элементарная логика. Если в таблице А 1000000 (миллион) записей, а таблице В миллион записей, то чтобы присоединить их по вашему алгоритму, надо считать миллион записей из первой таблицы и для каждой логарифм от миллиона записей второй таблицы, т.е. 30 миллионов. А алгоритм слияния считывает обе по индексу и в итоге считывается 2 миллиона записей, т.е. он будет в 15 раз быстрее работать. Грубая оценка, но всё же...

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

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

ggreggory
Грубая оценка, но всё же...

Не только грубая, но и просто неверная. При "считывает обе по индексу"
получается тот же самый логарифм, только с обеих сторон.

Posted via ActualForum NNTP Server 1.5

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

Откуда: iBase.ru
Сообщений: 30244
ggreggory
Не только вы можете кидаться статейками, я тоже могу:

я вам про Firebird, вы мне про MS SQL
ggreggory
А алгоритм слияния считывает обе по индексу и в итоге считывается 2 миллиона записей, т.е. он будет в 15 раз быстрее работать.

если вы про самое начало статьи, то там есть фраза:
"Например, если мы имеем предикат соединения "T1.a = T2.b", таблица T1 должна быть отсортирована по T1.a, а таблица T2 должна быть сортирована по T2.b.
Соединение слиянием одновременно считывает и сравнивает два отсортированных входных потока, по одной строке за шаг."


в Firebird это называется SORT MERGE.
В плане это
MERGE (TABLE1 SORT, TABLE2 SORT). Причем, SORT это либо действительно сортировка (во временном файле), либо это проход в порядке индекса. Но в обоих случаях это однозначно ХУЖЕ чем PLAN (TABLE1 NATURAL, TABLE2 index ...).
Кстати, вместо MERGE SORT в ФБ 3 уже есть HASH JOIN.
В общем, кое-что, конечно, в упомянутой вами статье является общим для оптимизаторов, но детали могут отличаться, поэтому не стоит верить что вот если "этот метод доступа лучше другого в сервере А". В сервере Б это не обязательно будет так.
(и примеры тому были на Хабре, когда там забубенная статья про использование индексов для where у PG, MS SQL и Oracle, а в ФБ не так и в половине случаев быстрее).
7 ноя 21, 15:18    [22392776]     Ответить | Цитировать Сообщить модератору
 Re: Почему FB 2.5.8 не использует FK индекс в запросе  [new]
ggreggory
Member

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

я вам про Firebird, вы мне про MS SQL


Это понятно, я ведь вначале написал "вопрос в том, почему Firebird не хочет". И да, я пробовал планы подсовывать - не кушает.

ggreggory
либо это проход в порядке индекса


При соединении PK=FK всегда есть индексы ключей. Поэтому там никакой сортировки во временных файлах или памяти не нужно. Если 3-ий Firebird, SuperServer и кэш большой, то это совсем не затратно (о чём там Dimitry Sibiryakov ??? - непонятно).

kdv
В сервере Б это не обязательно будет так.


В Firebird-е на чтение накладывает свой отпечаток наличие версий и необходимости считывать страницы данных. Но эта же проблема будет и при поиске по индексу. Так что вопрос остается открытым.

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

Откуда: iBase.ru
Сообщений: 30244
ggreggory,

продолжаем.
ggreggory
"надо считать миллион записей из первой таблицы и для каждой логарифм от миллиона записей второй таблицы, т.е. 30 миллионов.

непонятно, откуда такая логика.
При плане
JOIN (A NATURAL, B INDEX (BYID)))
механизм выборки данных такой:
по мере прохода по всем записям A берем значение поля связи и ищем его в индексе BYID таблицы B. Полученные из индекса номера записей складываем в "битовую маску".
Где тут логарифмы и 30 миллионов - вообще непонятно.
ggreggory
"вопрос в том, почему Firebird не хочет"

потому что Firebird это не MS SQL. Он по другому делает много чего, в т.ч. и оптимизатор работает иначе. Вы еще к Ораклу претензию дайте, почему он не так как MS SQL оптимизирует.
ggreggory
При соединении PK=FK всегда есть индексы ключей. Поэтому там никакой сортировки во временных файлах или памяти не нужно

Ну вот опять, еще раз - в плане выше как раз используется индекс по FK таблицы B. Вам не нравится, что не используется индекс по ПК таблицы А. Но нафиг он тут нужен, если из-за A LEFT JOIN B надо прочитать всю таблицу A? Чтение индекса таблицы A будет абсолютно лишним.
ggreggory
В Firebird-е на чтение накладывает свой отпечаток наличие версий и необходимости считывать страницы данных.

А другие серверы волшебным образом читают не "страницами", а "записями", что-ли? Или я вас не понимаю.
Ну версии, и что. Включите в MS SQL версионность, и там будет то же самое. А в Оракле версионность по умолчанию. И?
ggreggory
И да, я пробовал планы подсовывать - не кушает.

как я понимаю, начитавшись статей по MS SQL вы хотите заставить Firebird, чтобы он работал так же. Ну, успехов...
7 ноя 21, 18:07    [22392832]     Ответить | Цитировать Сообщить модератору
 Re: Почему FB 2.5.8 не использует FK индекс в запросе  [new]
Dimitry Sibiryakov
Member

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

ggreggory
Если 3-ий Firebird, SuperServer и кэш большой, то это совсем не затратно (о чём
там Dimitry Sibiryakov ??? - непонятно).

Чтобы было понятно, таки надо прочитать статью на которую дали ссылку.

NATURAL это 2 фетча на запись.
INDEX это (при глубине индекса 3) 5 фетчей на запись.

Теперь считаем:
Nested loop(NATURAL + INDEX) = 7 фетчей на запись.
Merge (INDEX + INDEX) = 10 фетчей на запись.

Итого для двух миллионных таблиц это 7 миллионов фетчей против 10. Натуралы
выигрывают.

Откуда MS SQL при том же раскладе умудряется делать 30 миллионов фетчей - его
проблема.

Posted via ActualForum NNTP Server 1.5

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

Откуда:
Сообщений: 182
Проиллюстрирую примером. Сделать в Firebird-е запрос с нужным планом не могу, но могу написать процедуры, эмулирующие эту ситуацию:

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

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

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

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

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

create generator GEN_TBL_ID;

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

set term ^;

execute block as
declare variable i integer;
begin
  i = 0;
  while (i < 1000000) do
  begin
    insert into tbl(id,text) values (gen_id(GEN_TBL_ID, 1), null);
    i = i + 1;
  end
end^

insert into join_tbl select * from tbl^

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

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^

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^


у меня среднее время выполнения на FB3 Superserver с достаточным кэшем
select count(*) from test_index = 26 секунд
select count(*) from test_merge = 19 секунд
В обоих случаях всё в памяти, Reads from disk to cache = 0. Т.е. получается, что вариант MERGE быстрее варианта INDEX на 7 секунд.

Я понимаю, разница не большая, но там наибольшие потери в том, что это сделано в виде процедуры, а не обычного запроса.
Среднее время выполнения select count(*) from (select tbl.text, join_tbl.text as join_text from tbl join join_tbl on tbl.id = join_tbl.id) у меня 10 секунд. Таким образом если бы этот запрос делался не по индексу, а объединением, т.е. быстрее на 7 секунд, то это уже было бы 10-7=3 секунды. Сравните - 3 секунды и 10 секунд. Это в три с лишним раза быстрее!!!
7 ноя 21, 20:57    [22392878]     Ответить | Цитировать Сообщить модератору
 Re: Почему FB 2.5.8 не использует FK индекс в запросе  [new]
Dimitry Sibiryakov
Member

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

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

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

Posted via ActualForum NNTP Server 1.5

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

Откуда:
Сообщений: 11551
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

Откуда:
Сообщений: 182
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

Откуда:
Сообщений: 11551
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

Откуда:
Сообщений: 11551
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

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

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

Откуда:
Сообщений: 182
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

Откуда:
Сообщений: 182
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

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

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

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

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

Posted via ActualForum NNTP Server 1.5

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