ReSQL.ru
     
powered by simpleCommunicator - 2.0.49     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Firebird, InterBase [игнор отключен] [закрыт для гостей] / Почему FB 2.5.8 не использует FK индекс в запросе
25 сообщений из 34, страница 1 из 2
  1  все
Почему FB 2.5.8 не использует FK индекс в запросе
    #40109171
hlopotun
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
hlopotun
Гость
Всем доброго дня,

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


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

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

 
Код: sql
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.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
/******************************************************************************/
/****              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                              ****/
/******************************************************************************/

...
Рейтинг: 0 / 0
04.11.2021, 11:31
| Ответить | Цитировать | Написать  
Почему FB 2.5.8 не использует FK индекс в запросе
    #40109173
hlopotun
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
hlopotun
Гость
hlopotun,

это был упрощённый пример, тут он тоже не используется:
Код: sql
1.
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)))
...
Рейтинг: 0 / 0
04.11.2021, 11:36
| Ответить | Цитировать | Написать  
Почему FB 2.5.8 не использует FK индекс в запросе
    #40109176
hlopotun
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
hlopotun
Гость
а если так то используется:
Код: sql
1.
select * from prouserroles pur where pur.proid like '{%'
...
Рейтинг: 0 / 0
04.11.2021, 11:38
| Ответить | Цитировать | Написать  
Почему FB 2.5.8 не использует FK индекс в запросе
    #40109180
a7exander
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
a7exander
Участник
hlopotunпочему не используется индекс FK_PROUSERROLES_PRO?


Здесь как минимум потому что индекс бесполезен) У вас поле "not null" и вы условие поставили is not null - зачем индекс??
...
Рейтинг: 0 / 0
04.11.2021, 11:43
| Ответить | Цитировать | Написать  
Почему FB 2.5.8 не использует FK индекс в запросе
    #40109182
hlopotun
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
hlopotun
Гость
a7exander,

ок, not null опустим
почему тут не используется?
Код: sql
1.
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)))
...
Рейтинг: 0 / 0
04.11.2021, 11:44
| Ответить | Цитировать | Написать  
Почему FB 2.5.8 не использует FK индекс в запросе
    #40109183
hlopotun
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
hlopotun
Гость
hlopotun,

сорри, просмотрел, там DISTINCT и на нём спотыкается а индекс используется
...
Рейтинг: 0 / 0
04.11.2021, 11:46
| Ответить | Цитировать | Написать  
Почему FB 2.5.8 не использует FK индекс в запросе
    #40109186
a7exander
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
a7exander
Участник
hlopotun
a7exander,

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




может кто то не умеет читать план?
...
Рейтинг: 0 / 0
04.11.2021, 11:50
| Ответить | Цитировать | Написать  
Почему FB 2.5.8 не использует FK индекс в запросе
    #40109187
hlopotun
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
hlopotun
Гость
hlopotun,

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


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

PLAN SORT ((PRO NATURAL))
 
Код: sql
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.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
121.
122.
123.
124.
125.
126.
127.
128.
129.
130.
131.
132.
133.
134.
135.
136.
137.
138.
139.
140.
141.
142.
143.
144.
145.
146.
147.
148.
149.
150.
151.
152.
153.
154.
155.
156.
157.
158.
159.
160.
161.
162.
163.
164.
165.
166.
167.
168.
169.
170.
171.
172.
173.
174.
175.
176.
177.
178.
179.
180.
181.
182.
183.
184.
185.
186.
187.
188.
189.
190.
191.
192.
193.
194.
195.
196.
197.
198.
199.
200.
201.
202.
203.
204.
205.
206.
207.
208.
209.
210.
211.
212.
213.
214.
215.
216.
217.
218.
219.
220.
221.
222.
223.
224.
/******************************************************************************/
/****              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                              ****/
/******************************************************************************/

...
Рейтинг: 0 / 0
04.11.2021, 11:50
| Ответить | Цитировать | Написать  
Почему FB 2.5.8 не использует FK индекс в запросе
    #40109211
KreatorXXI
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
KreatorXXI
Участник
hlopotun,

ну какой primary key? Вы же условие никакое не ставите! Поставьте хотя order. Может будет перебирать по primary key. Опять же - какой смысл? Что Вы хотите? В Вашем случае по натуралу самый быстрый поиск. ФБ рулит!!! Я бы возмутился в обратном случае - зачем дёргать индексы, если надо проверить тупо все записи?
...
Рейтинг: 0 / 0
04.11.2021, 14:29
| Ответить | Цитировать | Написать  
Почему FB 2.5.8 не использует FK индекс в запросе
    #40109213
hvlad
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
hvlad
Участник
hlopotun,

какой смысл в distinct, если выбирается только PK ?
...
Рейтинг: 0 / 0
04.11.2021, 14:33
| Ответить | Цитировать | Написать  
Почему FB 2.5.8 не использует FK индекс в запросе
    #40109232
hlopotun
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
hlopotun
Гость
hvlad
hlopotun,

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


да, действительно. Извиняюсь. Неудачно обрезал пример, потерялся смысл, полный код выглядит так:
Код: sql
1.
2.
3.
4.
5.
6.
7.
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}')


и проблема в этой части
Код: sql
1.
...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))))
...
Рейтинг: 0 / 0
04.11.2021, 15:38
| Ответить | Цитировать | Написать  
Почему FB 2.5.8 не использует FK индекс в запросе
    #40109234
hvlad
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
hvlad
Участник
hlopotun
и проблема в этой части
Код: sql
1.
...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))

Тут нет проблемы, ибо невозможно использовать индексный доступ по условию джойна для обеих таблиц.
Если есть доп. условия, то могут быть использованы доп. индексы. Но здесь не этот случай.
...
Рейтинг: 0 / 0
04.11.2021, 15:46
| Ответить | Цитировать | Написать  
Почему FB 2.5.8 не использует FK индекс в запросе
    #40109238
hlopotun
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
hlopotun
Гость
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
...
Рейтинг: 0 / 0
04.11.2021, 15:52
| Ответить | Цитировать | Написать  
Почему FB 2.5.8 не использует FK индекс в запросе
    #40109239
hlopotun
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
hlopotun
Гость
hvlad,

ok, понятно. Спасибо
...
Рейтинг: 0 / 0
04.11.2021, 15:53
| Ответить | Цитировать | Написать  
Почему FB 2.5.8 не использует FK индекс в запросе
    #40109738
Фотография kdv
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
kdv
Участник
hlopotun JOIN (PRO NATURAL, PUR INDEX (FK_PROUSERROLES_PRO))))
в left/right join без дополнительных условий таблица со стороны left (или right) всегда читается натуралом.
Как вы себе представляете альтернативу? На пальцах попробуйте придумать (я не шучу, перебрать соответствие всех
пальцев одной руки и части пальцев на другой).
...
Рейтинг: 0 / 0
06.11.2021, 22:22
| Ответить | Цитировать | Написать  
Почему FB 2.5.8 не использует FK индекс в запросе
    #40109787
ggreggory
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ggreggory
Гость
kdv

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


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

Вопрос в том, почему Firebird не хочет использовать merge join в этом случае. Мне кажется, что join двух больших таблиц должен в этом случае работать значительно быстрее.
...
Рейтинг: 0 / 0
07.11.2021, 13:06
| Ответить | Цитировать | Написать  
Почему FB 2.5.8 не использует FK индекс в запросе
    #40109802
Фотография kdv
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
kdv
Участник
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
...
Рейтинг: 0 / 0
07.11.2021, 14:36
| Ответить | Цитировать | Написать  
Почему FB 2.5.8 не использует FK индекс в запросе
    #40109804
ggreggory
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ggreggory
Гость
kdv

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


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

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

Ведь элементарная логика. Если в таблице А 1000000 (миллион) записей, а таблице В миллион записей, то чтобы присоединить их по вашему алгоритму, надо считать миллион записей из первой таблицы и для каждой логарифм от миллиона записей второй таблицы, т.е. 30 миллионов. А алгоритм слияния считывает обе по индексу и в итоге считывается 2 миллиона записей, т.е. он будет в 15 раз быстрее работать. Грубая оценка, но всё же...
...
Рейтинг: 0 / 0
07.11.2021, 14:56
| Ответить | Цитировать | Написать  
Почему FB 2.5.8 не использует FK индекс в запросе
    #40109807
Dimitry Sibiryakov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Dimitry Sibiryakov
Участник
ggreggoryГрубая оценка, но всё же...

Не только грубая, но и просто неверная. При "считывает обе по индексу"
получается тот же самый логарифм, только с обеих сторон.
Posted via ActualForum NNTP Server 1.5
...
Рейтинг: 0 / 0
07.11.2021, 15:09
| Ответить | Цитировать | Написать  
Почему FB 2.5.8 не использует FK индекс в запросе
    #40109810
Фотография kdv
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
kdv
Участник
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, а в ФБ не так и в половине случаев быстрее).
...
Рейтинг: 0 / 0
07.11.2021, 15:18
| Ответить | Цитировать | Написать  
Почему FB 2.5.8 не использует FK индекс в запросе
    #40109828
ggreggory
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ggreggory
Гость
kdv

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


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

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

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

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


В Firebird-е на чтение накладывает свой отпечаток наличие версий и необходимости считывать страницы данных. Но эта же проблема будет и при поиске по индексу. Так что вопрос остается открытым.
...
Рейтинг: 0 / 0
07.11.2021, 17:41
| Ответить | Цитировать | Написать  
Почему FB 2.5.8 не использует FK индекс в запросе
    #40109832
Фотография kdv
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
kdv
Участник
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, чтобы он работал так же. Ну, успехов...
...
Рейтинг: 0 / 0
07.11.2021, 18:07
| Ответить | Цитировать | Написать  
Почему FB 2.5.8 не использует FK индекс в запросе
    #40109835
Dimitry Sibiryakov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Dimitry Sibiryakov
Участник
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
...
Рейтинг: 0 / 0
07.11.2021, 18:34
| Ответить | Цитировать | Написать  
Почему FB 2.5.8 не использует FK индекс в запросе
    #40109869
ggreggory
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ggreggory
Гость
Проиллюстрирую примером. Сделать в Firebird-е запрос с нужным планом не могу, но могу написать процедуры, эмулирующие эту ситуацию:

 
Код: sql
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.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
-- Создание таблиц

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 секунд. Это в три с лишним раза быстрее!!!
...
Рейтинг: 0 / 0
07.11.2021, 20:57
| Ответить | Цитировать | Написать  
Почему FB 2.5.8 не использует FK индекс в запросе
    #40109873
Dimitry Sibiryakov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Dimitry Sibiryakov
Участник
ggreggoryно могу написать процедуры, эмулирующие эту ситуацию:

Походу, не смог. У тебя join внутренний, а в топике всё время речь про внешний.
Posted via ActualForum NNTP Server 1.5
...
Рейтинг: 0 / 0
07.11.2021, 21:14
| Ответить | Цитировать | Написать  
25 сообщений из 34, страница 1 из 2
  1  все
Форумы / Firebird, InterBase [игнор отключен] [закрыт для гостей] / Почему FB 2.5.8 не использует FK индекс в запросе
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru             Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=40&fpage=3&tid=1559895]:
 0ms
get settings:
 10ms
get forum list:
 14ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 26ms
get topic data:
 11ms
get forum data:
 3ms
get page messages:
 69ms
get tp. blocked users:
 2ms
others: 269ms
total:  412ms
созд. / загр.: 412ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]