FB2 Оптимизация сортировки

[Sikambr]

Имеется таблица:

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

create TABLE Docs (
  Id        INTEGER  not NULL PRIMARY KEY,
  Doc_Kind  SMALLINT not NULL REFERENCES Doc_Kinds(Id),
  Client_Id INTEGER  not NULL REFERENCES Clients(Id),
  Doc_Year  SMALLINT not NULL,
  Doc_No    INTEGER  not NULL,
  ...

Для получания списка документов, использую такой запрос:

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

select * from Docs
  where (Doc_Kind = :Doc_Kind) and (Client_Id = :Client_Id)
  order by Doc_Year desc, Doc_No desc

PLAN SORT ((DOCS INDEX (RDB$FOREIGN79, RDB$FOREIGN78)))

Т.е. используется два форин-индекса.
После создания индекса:

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

create desc index Idx_Docs_No on Docs(Doc_Year, Doc_No)

план уже такой

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

PLAN (DOCS ORDER IDX_DOCS_NO INDEX (RDB$FOREIGN79, RDB$FOREIGN78))

Хотелось спросить общественность, как оптимальней использовать индексы при сортировки и использования условий?
Насколько я понимаю, использование одного составного индекса лучше, чем трех одиночек.
Что скажите?

[Dimitry Sibiryakov]

Для сортировки индекс лучше вообще не использовать если только у тебя не мега-выборки.
У составного индекса практически нет преимуществ перед одиночными.

[Sikambr]

У составного индекса практически нет преимуществ перед одиночными.

Для FB не знаю, а для Interbase 7, если оптимизатор использует два одиночных индекса, то время выполения запроса заметно больше, чем использование (после создания) одного составного индекса.
Это для было замечено на таблице с количеством записей более 1 млн.

[dimitr]

1) индекс для сортировки (т.е. ORDER-план) стоит использовать *только* для максимально быстрого получения первых записей. Например, отобразить первую страницу грида. Если же требуется максимально быстро отфетчить все записи, то ORDER практически всегда проигрывает SORT-плану.

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

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

[Sikambr]

1) индекс для сортировки (т.е. ORDER-план) стоит использовать *только* для максимально быстрого получения первых записей. Например, отобразить первую страницу грида. Если же требуется максимально быстро отфетчить все записи, то ORDER практически всегда проигрывает SORT-плану.

Вот этого я и хочу.
После дропанья индекса Idx_Doc_No и создания другого

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

create desc index Idx_Docs_No_2 on Docs(Doc_Kind, Client_Id, Doc_Year, Doc_No)

Получаем следущее:
a)

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

select * from Docs
where (Doc_Kind = :Doc_Kind) and (Client_Id = :Client_Id)
order by Doc_Year desc, Doc_No desc

PLAN SORT ((DOCS INDEX (IDX_DOCS_NO_2)))

b)

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

select * from Docs
where (Doc_Kind = :Doc_Kind) and (Client_Id = :Client_Id)
order by Doc_Kind desc, Client_Id desc, Doc_Year desc, Doc_No desc

PLAN (DOCS ORDER IDX_DOCS_NO_2 INDEX (IDX_DOCS_NO_2))

Для варианта (1) план (b) оптимальный?

[kdv]

дропать исходный индекс необязательно.

Для варианта (1) план (b) оптимальный?

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

[Sikambr]

дропать исходный индекс необязательно.

Если Idx_Doc_No не дропать, то он будет участвовать в запросе (a).
Вообщем, вопрос не в этом.

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

Не спорю, как устроен индекс и что такое б-деревья я знаю только по наслышке.
Но меня интересует, что оптимальнее, запрос (а) или запрос (b). Т.е. я не совсем догоняю, если план:

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

PLAN (DOCS ORDER IDX_DOCS_NO_2 INDEX (IDX_DOCS_NO_2))

Сколько проходов будет:
1. в одном проходе будет и сортировка и отсеивание;
2. вначале сортируется, а затем отсеивается (или наоборот), т.е. два прохода.
Вот в этом хочу разобраться.

PS В запросе (a) я ошибся с планом. Прошу прощения, исправил.

[Sikambr]

После ряда экспериментов, решил, что оптимальней (по времени) и гибче будет следущее:

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

create desc index Idx_Docs_No on Docs(Doc_Year, Doc_No);

select * from Docs 
  where (Doc_Kind = :Doc_Kind) and (Client_Id = :Client_Id) 
  order by Doc_Year desc, Doc_No desc;

PLAN (DOCS ORDER IDX_DOCS_NO INDEX (RDB$FOREIGN79, RDB$FOREIGN78));

Даже использование 3-х индексов, по времени дает выигрыш в 3 раза, чем отказ от построения индекса Idx_Docs_No, т.е. безиндексная сортировка.
Построение индекса по 4-м полям

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

create desc index Idx_Docs_No_2 on Docs(Doc_Kind, Client_Id, Doc_Year, Doc_No) ;

select * from Docs
  where (Doc_Kind = :Doc_Kind) and (Client_Id = :Client_Id) 
  order by Doc_Kind desc, Client_Id desc, Doc_Year desc, Doc_No desc;

PLAN (DOCS ORDER IDX_DOCS_NO_2 INDEX (IDX_DOCS_NO_2))

не дает выигрыша по времени.

Предполагаю, возможно ошибочно, что последний план говорит о том, что делается два прохода по одному и тому же индексу.
Может кто-нибудь прояснит, сколько делается проходов?

[kdv]

Может кто-нибудь прояснит, сколько делается проходов?

www.ibase.ru/devinfo/dataaccesspaths.htm

[Sikambr]

Может кто-нибудь прояснит, сколько делается проходов?

www.ibase.ru/devinfo/dataaccesspaths.htm

Спасибо за поучительную статью.