iBase.ru Forum

Чем первичный ключ не индекс?

SELECT
   ORIGINAL_NUMBERS.ID_ON,
   ORIGINAL_NUMBERS.ON_CLEAR,
   ORIGINAL_NUMBERS.ON_NAME,
   ORIGINAL_NUMBERS.DET_LIST_ID,
   BOOK_ORIGINAL_FIRM_LIST.OF_NAME,
   SKLAD_NUMBERS.DIRTY_COD,
   BOOK_MAKERS.MAKER_NAME
FROM ORIGINAL_NUMBERS
   INNER JOIN BOOK_ORIGINAL_FIRM_LIST ON (ORIGINAL_NUMBERS.OFL_ID = BOOK_ORIGINAL_FIRM_LIST.ID_OFL)
   INNER JOIN SKLAD_NUMBERS ON (SKLAD_NUMBERS.ID_DET_LIST = ORIGINAL_NUMBERS.DET_LIST_ID)
   INNER JOIN BOOK_MAKERS  ON (BOOK_MAKERS.ID_MAKERS = SKLAD_NUMBERS.Maker_id)
where ORIGINAL_NUMBERS.On_clear like '7700%'

План
PLAN JOIN (BOOK_MAKERS NATURAL,SKLAD_NUMBERS INDEX (SKLAD_NUMBERS_IDX1),ORIGINAL_NUMBERS INDEX (ORIGINAL_NUMBERS_IDX1,DETLIST_IDX),BOOK_ORIGINAL_FIRM_LIST INDEX (PK_BOOK_ORIGINAL_FIRM_LIST))

Адаптированный план
PLAN JOIN (BOOK_MAKERS NATURAL,SKLAD_NUMBERS INDEX (SKLAD_NUMBERS_IDX1),ORIGINAL_NUMBERS INDEX (ORIGINAL_NUMBERS_IDX1,DETLIST_IDX),BOOK_ORIGINAL_FIRM_LIST INDEX (PK_BOOK_ORIGINAL_FIRM_LIST))

Ну очень долго выполняется.
По анализу производительности в ИБЭксперте вижу кучу ненужных чтений из SKLAD_NUMBERS

Код: Выделить всё

CREATE TABLE SKLAD_NUMBERS (
    ID_DET_LIST        INTEGER NOT NULL,
    CLEAR_COD          VARCHAR(30),
    DIRTY_COD          VARCHAR(30) NOT NULL,
    MAKER_ID           INTEGER,
    CATEGORY_ID        INTEGER,
    GROUP_ID           INTEGER,
***************
);
ALTER TABLE SKLAD_NUMBERS ADD CONSTRAINT PK_SKLAD_NUMBERS PRIMARY KEY (ID_DET_LIST);

CREATE INDEX SKLAD_NUMBERS_IDX1 ON SKLAD_NUMBERS (MAKER_ID, CLEAR_COD);

CREATE INDEX SN_NUM_MAK_IDX ON SKLAD_NUMBERS (CLEAR_COD, MAKER_ID);

CREATE TABLE BOOK_MAKERS (
    ID_MAKERS   INTEGER NOT NULL,
    MAKER_NAME  TFIRMA NOT NULL /* TFIRMA = VARCHAR(30) */
);

ALTER TABLE BOOK_MAKERS ADD CONSTRAINT PK_BOOK_MAKERS PRIMARY KEY (ID_MAKERS);

CREATE UNIQUE INDEX BOOK_MAKNAMES_IDX ON BOOK_MAKERS (MAKER_NAME);

я так понимаю потому что используется в плане SKLAD_NUMBERS_IDX1
пытаюсь прописать там первичный ключ PK_SKLAD_NUMBERS
на что получаю
index PK_SKLAD_NUMBERS cannot be used in the specified plan.
Что не так?
С планами никогда не работал раньше.
Помогите оптимизировать запрос

DSKalugin писал(а): Что не так?
С планами никогда не работал раньше.
Помогите оптимизировать запрос

В случае INNER JOIN оптимизатор имеет свободу выбора не только какие индексы применять, но и в каком порядке соединять таблицы, а не следовать твоему порядку записи объединений. Что не всегда идёт ему на пользу. Вот это

PLAN JOIN (BOOK_MAKERS NATURAL,SKLAD_NUMBERS INDEX (SKLAD_NUMBERS_IDX1),ORIGINAL_NUMBERS INDEX (ORIGINAL_NUMBERS_IDX1,DETLIST_IDX),BOOK_ORIGINAL_FIRM_LIST INDEX (PK_BOOK_ORIGINAL_FIRM_LIST))

показывает и то и другое. То есть, он перебирает BOOK_MAKERS, присоединяет к нему SKLAD_NUMBERS по полю MAKER_ID и индексу SKLAD_NUMBERS_IDX1 ON SKLAD_NUMBERS (MAKER_ID, CLEAR_COD) и так далее. Поэтому просто впихнуть в этот план другой индекс не меняя порядка не получится, он не может его применить для этого порядка объединений. Вариантов 2:

1. Так называемый грязный хак, то есть применение явного плана. Суть - лишить оптимизатор свободы вообще, оставив ему функции только контроля приемлемости в принципе. В этом случае ты, глядя на свои условия в ON и WHERE, должен прописать план в желаемом порядке объединения таблиц, естественно, перечислив все таблицы и желаемые и годящиеся для этих условий индексы.

2. Так называемая подсказка оптимизатору. Более изящный способ, позволяющий отнять у него излишнюю свободу, но позволяя додумать оптимальный план самому. В данном случае первый шаг - изменить

INNER JOIN BOOK_MAKERS
ON (BOOK_MAKERS.ID_MAKERS = SKLAD_NUMBERS.Maker_id)

на

INNER JOIN BOOK_MAKERS
ON (BOOK_MAKERS.ID_MAKERS = SKLAD_NUMBERS.Maker_id+0)

что не даст ему использовать индекс по Maker_Id на таблице SKLAD_NUMBERS. Скорее всего он тогда обратит внимание на желаемый тобой PK и построит другую очерёдность объединения и будет использовать в некоторых местах другие индексы соответственно. Если ты прав в понимании заполненности твоих таблиц - более удачную. Глядя опять на план и время выполнения, принимаешь решение - устраивает это тебя или отсечь ему использование ещё какого-нибудь индекса, с целью дальнейшего изменения порядка объединения.

Код: Выделить всё

CREATE INDEX SKLAD_NUMBERS_IDX1 ON SKLAD_NUMBERS (MAKER_ID, CLEAR_COD);

CREATE INDEX SN_NUM_MAK_IDX ON SKLAD_NUMBERS (CLEAR_COD, MAKER_ID);

не надо такую бяку делать. у этих индексов селективность будет одинаковая, при том что реально уникальность столбцов clear_cod и maker_id разная. Но оптимизатор в силу специфики будет цеплять только последний из таких созданных индексов.

причем в данном запросе второй индекс не используется, а sklad_numbers_idx1 используется только по первому сегменту (ON (BOOK_MAKERS.ID_MAKERS = SKLAD_NUMBERS.Maker_id)

я кстати, не понял. почему не построен FK с SKLAD_NUMBERS.Maker_id на BOOK_MAKERS.ID_MAKERS ?

что не даст ему использовать индекс по Maker_Id на таблице SKLAD_NUMBERS.

дык. см. мое письмо выше - этот композит возможно лучше совсем прибить. Если с одиночным индексом по maker_id будет такая же бяка, тогда да, надо просто посмотреть на селективность всех используемых индексов, и для конкретного условия добавить +0.

kdv писал(а): дык. см. мое письмо выше - этот композит возможно лучше совсем прибить. Если с одиночным индексом по maker_id будет такая же бяка, тогда да, надо просто посмотреть на селективность всех используемых индексов, и для конкретного условия добавить +0.

Ну, ты ж меня знаешь, я ленивый чужое слишком уж внимательно анализировать и окончательные решения давать. Понятие о плане дал, на свободу иннера указал, рычажки показал, пусть дальше растёт над собой, оно так и полезнее будет

А насчёт таких композитов - про этот конкретный случай не скажу, но бывает, что они таки полезны. Когда многие запросы используют оба сегмента, а селективность их по отдельности так себе. И с чередованием сегментов бывает полезно. Например, эта таблица часто оказывается связывающей другие в запросах, по типу m:n, причём то с одного конца, то с другого. Если селективность сегментов плохая, то запросы-то быстрее по сравнению с одиночными индексами не станут, но время рестора базы будет меньше. Но в дальнейшем приходится за такими индексами приглядывать, чтоб не хватал где не надо.

Merlin писал(а): В случае INNER JOIN оптимизатор имеет свободу выбора не только какие индексы применять, но и в каком порядке соединять таблицы, а не следовать твоему порядку записи объединений. Что не всегда идёт ему на пользу.

Спасибо за лекцию, полезно было почерпнуть.
Но такая фича как +0 осталась для меня загадкой

Merlin писал(а): Понятие о плане дал, на свободу иннера указал, рычажки показал, пусть дальше растёт над собой, оно так и полезнее будет

Очень любезно

буду искать документацию по этому вопросу.
Что порекомендушь почитать?

kdv писал(а):
Код: Выделить всё
CREATE INDEX SKLAD_NUMBERS_IDX1 ON SKLAD_NUMBERS (MAKER_ID, CLEAR_COD);

CREATE INDEX SN_NUM_MAK_IDX ON SKLAD_NUMBERS (CLEAR_COD, MAKER_ID);
не надо такую бяку делать. у этих индексов селективность будет одинаковая, при том что реально уникальность столбцов clear_cod и maker_id разная. Но оптимизатор в силу специфики будет цеплять только последний из таких созданных индексов.

причем в данном запросе второй индекс не используется, а sklad_numbers_idx1 используется только по первому сегменту (ON (BOOK_MAKERS.ID_MAKERS = SKLAD_NUMBERS.Maker_id)

ОООООО!!!! Как раз в десяточку! Убил SKLAD_NUMBERS_IDX1 и все аж летать стало! Вот план

Код: Выделить всё

Адаптированный план
PLAN JOIN (ORIGINAL_NUMBERS INDEX (ORIGINAL_NUMBERS_IDX1),SKLAD_NUMBERS INDEX (PK_SKLAD_NUMBERS),BOOK_MAKERS INDEX (PK_BOOK_MAKERS),BOOK_ORIGINAL_FIRM_LIST INDEX (PK_BOOK_ORIGINAL_FIRM_LIST))

------ Performance info ------
Prepare time = 10ms
Execute time = 101ms

Супер

То что и требовалось

kdv писал(а): я кстати, не понял. почему не построен FK с SKLAD_NUMBERS.Maker_id на BOOK_MAKERS.ID_MAKERS ?

Бытует в народе мнение, что FK снижают производительность Firebird. Поэтому я ссылочную целостность решаю на уровне приложения, а FK вообще не использую нигде. Или это заблуждение?

Огромное спасибо kdv и Merlin

Здравствуйте, уважаемые,

DSKalugin писал(а):Как раз в десяточку! Убил SKLAD_NUMBERS_IDX1 и все аж летать стало!

Обрати внимание на оба плана - в первом твоем письме и после убиения индекса. У тебя изменился порядок "обхода" таблиц. Это и стало причиной использования индекса от пк таблицы SKLAD_NUMBERS, а не сам факт уничтожения индекса, то есть ты и в первоначальном варианте мог, например, "ручками" указать план с PK_SKLAD_NUMBERS, но при условии другого порядка таблиц. А с индексом SKLAD_NUMBERS_IDX1 оптимизатор был запутан и выдал не самый оптимальный вариант.

DSKalugin писал(а):Бытует в народе мнение, что FK снижают производительность Firebird.

Ну это мнение из рода мнений подобным слухам о вреде OUTER-а

С точки зрения оптимальности индекс от FK на справочник из 2-х значений, может и навредить, так же как и неуместный OUTER.

DSKalugin писал(а):Но такая фича как +0 осталась для меня загадкой

А ты попробуй ее. К полям таблиц при "связывании" поприбавляй нолики, посмотри на план, как он меняется.
Как-то я решил подрасти над собой, подумав, что привычное мне ручное прибивание планов - не есть хорошо, начал пользоваться этим трюком. Потом долго в монитор пялился - чего это мне такого запрос выдал. И нашел, что в одном из мест where-раздела многостраничного запроса вместо +0 забабахал +1. Так и не закрепилась у меня эта метода.

DSKalugin писал(а):Что порекомендушь почитать?

возможно, полезно будет почитать http://www.krista.ru/ib

Бытует в народе мнение, что FK снижают производительность Firebird. Поэтому я ссылочную целостность решаю на уровне приложения, а FK вообще не использую нигде. Или это заблуждение?

зависит от данных. то есть, от степени неуникальности индекса по FK.

Но такая фича как +0 осталась для меня загадкой

батенька, ну элементарно же!

select * from table
where id = 5

смотрим план - юзается индекс.
пишем where id+0 = 5
смотрим план - индекс НЕ юзается. Почему? просто потому, что id+0 это ВЫРАЖЕНИЕ. А индексы по выражениям IB/FB/YA использовать не умеет.
То есть, мы насильно загнули оптимизатор от использования конкретного индекса.

p.s. про индексы по выражениям я знаю.

Спасибо, господа!
Информации для размышления вы мне дали предостаточно

iBase.ru Forum

index PK_SKLAD_NUMBERS cannot be used in the specified plan.

index PK_SKLAD_NUMBERS cannot be used in the specified plan.

Re: index PK_SKLAD_NUMBERS cannot be used in the specified p

УРРААА!!! ПОМОГЛО!!!

Re: УРРААА!!! ПОМОГЛО!!!