ReSQL.ru
     
powered by simpleCommunicator - 2.0.49     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Firebird, InterBase [игнор отключен] [закрыт для гостей] / Почему FB 2.5.8 не использует FK индекс в запросе
34 сообщений из 34, показаны все 2 страниц
  все  
Почему 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
| Ответить | Цитировать | Написать  
Почему FB 2.5.8 не использует FK индекс в запросе
    #40109877
hvlad
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
hvlad
Участник
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.
...
Рейтинг: 0 / 0
07.11.2021, 21:33
| Ответить | Цитировать | Написать  
Почему FB 2.5.8 не использует FK индекс в запросе
    #40109878
ggreggory
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ggreggory
Гость
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, всё одно.
...
Рейтинг: 0 / 0
07.11.2021, 21:34
| Ответить | Цитировать | Написать  
Почему FB 2.5.8 не использует FK индекс в запросе
    #40109879
hvlad
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
hvlad
Участник
ggreggory
Код: sql
1.
2.
3.
4.
CREATE TABLE TBL (
    ID    INTEGER NOT NULL,
    TEXT  VARCHAR(4096)
);

А чего не VARCHAR(100500) ? Зачем скромничать ?

ggreggory
Код: sql
1.
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 запросов переносится один-в-один на гипотетический "запрос с объединеним" ?
...
Рейтинг: 0 / 0
07.11.2021, 21:41
| Ответить | Цитировать | Написать  
Почему FB 2.5.8 не использует FK индекс в запросе
    #40109880
hvlad
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
hvlad
Участник
ggreggory
Вопрос в том, почему Firebird не хочет использовать merge join в этом случае
Потому что random IO от индекса к таблице убьёт весь потенциальный выигрыш.
Попробуй этот запрос в MSSQL не для кластерного и не для покрывающего индекса.
...
Рейтинг: 0 / 0
07.11.2021, 21:44
| Ответить | Цитировать | Написать  
Почему FB 2.5.8 не использует FK индекс в запросе
    #40109881
Dimitry Sibiryakov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Dimitry Sibiryakov
Участник
ggreggoryбез разницы

Ты не врубился: у тебя в процедурах идёт исключительно inner join для
примитивного соединения 1:1 по первичному ключу. Вот когда ты их напишешь так,
чтобы они выдавали правильный результат для M:N, тогда их и можно будет
сравнивать с запросом.
Posted via ActualForum NNTP Server 1.5
...
Рейтинг: 0 / 0
07.11.2021, 21:51
| Ответить | Цитировать | Написать  
Почему FB 2.5.8 не использует FK индекс в запросе
    #40109897
ggreggory
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ggreggory
Гость
hvlad
А чего не VARCHAR(100500) ? Зачем скромничать ?


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

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


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

 
Код: plsql
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 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 секунды. Вы можете попробовать у себя запустить этот тест. Возможно, у вас будут другие соотношения.
...
Рейтинг: 0 / 0
07.11.2021, 23:32
| Ответить | Цитировать | Написать  
Почему FB 2.5.8 не использует FK индекс в запросе
    #40109901
ggreggory
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ggreggory
Гость
hvlad
ggreggory
Вопрос в том, почему Firebird не хочет использовать merge join в этом случае
Потому что random IO от индекса к таблице убьёт весь потенциальный выигрыш..


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

Dimitry Sibiryakov

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


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

По TEST_MERGE - согласен, написал на скорую руку, но для этого конкретного примера она выдает корректный результат.
...
Рейтинг: 0 / 0
07.11.2021, 23:38
| Ответить | Цитировать | Написать  
Почему FB 2.5.8 не использует FK индекс в запросе
    #40109909
Dimitry Sibiryakov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Dimitry Sibiryakov
Участник
ggreggoryПо TEST_INDEX не согласен, там четко пробегается по всем записям первой таблицы
и ищется по PK запись в связанной таблице.

Добавь в JOIN_TBL только каждую вторую запись из TBL, но дважды. "Вот тогда-то
мы и повеселимся..." (с)
Posted via ActualForum NNTP Server 1.5
...
Рейтинг: 0 / 0
08.11.2021, 00:47
| Ответить | Цитировать | Написать  
Почему FB 2.5.8 не использует FK индекс в запросе
    #40109913
Dimitry Sibiryakov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Dimitry Sibiryakov
Участник
И вообще не заморачивайся пока с миллионами и производительностью. Для начала
просто заставь свои процедуры выдавать корректный результат на простых данных:
https://dbfiddle.uk/?rdbms=firebird_3.0&fiddle=580347a8e7361360ec67fda82e3fa78c
Posted via ActualForum NNTP Server 1.5
...
Рейтинг: 0 / 0
08.11.2021, 00:59
| Ответить | Цитировать | Написать  
34 сообщений из 34, показаны все 2 страниц
  все  
Форумы / Firebird, InterBase [игнор отключен] [закрыт для гостей] / Почему FB 2.5.8 не использует FK индекс в запросе
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?all=1&fid=40&tid=1559895]:
 0ms
get settings:
 10ms
get forum list:
 15ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 62ms
get topic data:
 13ms
get forum data:
 3ms
get page messages:
 66ms
get tp. blocked users:
 2ms
others: 236ms
total:  415ms
созд. / загр.: 415ms
x
x
Закрыть


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