Составные индексы с "разной" сортировкой

← →
YuRock © (2007-03-20 13:44) [0]

Добрый день.

Вопрос такой: есть ли смысл использовать указание направления сортировки в узлах индексов, если они (направления) - разные? Как они работают, я пока-что не заметил. Вот пример:

CREATE INDEX i_DocLimits_LoHi ON DocLimits( LowLimit ASC, HighLimit DESC )
оба поля - unsigned int

Так вот, использовать такую сортировку не получается. Запрос типа
select * from DocLimits where LowLimit <= 1000000 AND HighLimit >= 1000000
Не работает по индексу. ( Если вручную указать план - то индекс использоваться будет, но отбираться по нему будет только "половина" таблицы, а по второму полю (HighLimit) будет sequentially.

Никто не сталкивался с подобной проблемой? Если эта фича не работает - посоветуете что-нибудь другое для решения этой проблемы?

Сервер ASA 9.0.2
В таблице - 16 миллионов записей. Результаты запросов, как в примере - 2-4 записи. Данные в полях LowLimit и HighLimit не уникальные, но и сильно не повторяющиеся.

min/мах значение этих полей:
select
( select min(LowLimit) from doclimits ) as minlo,
( select max(LowLimit) from doclimits ) as maxlo,
( select min(HighLimit) from doclimits ) as minhi,
( select max(HighLimit) from doclimits ) as maxhi
from dummy

minlo,maxlo,minhi,maxhi
1048577,231735297,1048577,231740120

SELECT AVG(HighLimit) FROM DocLimits

46283869

Спасибо большое за любые ответы.

← →
Sergey13 © (2007-03-20 13:58) [1]

> Так вот, использовать такую сортировку не получается. Запрос типа
> select * from DocLimits where LowLimit <= 1000000 AND HighLimit >= 1000000
> Не работает по индексу.
И не получится наверное. Ибо в условии не четкое равенство. Другими словами записей, удовлетворяющих условию, может быть и миллион и два и больше. Индекс при этом будет только мешать из-за дополнительного чтения. Поэтому оптимизатор видимо (справедливо) посчитал, что фул скан будет производительнее.

> select
> ( select min(LowLimit) from doclimits ) as minlo,
> ( select max(LowLimit) from doclimits ) as maxlo,
> ( select min(HighLimit) from doclimits ) as minhi,
> ( select max(HighLimit) from doclimits ) as maxhi
> from dummy

Почему не просто
select min(LowLimit),max(LowLimit),min(HighLimit),max(HighLimit) from doclimits

? Все выберется за один проход вместо 4-х.

ЗЫ: Все исключительно ИМХО, с указанным сервером не встречался

← →
YuRock © (2007-03-20 14:07) [2]

> Почему не просто select min(LowLimit),max(LowLimit),min(HighLimit),
> max(HighLimit) from doclimits

Да, так проще :) Но медленнее. В моем варианте отрабатывает моментально каждый подзапрос по индексу, а тут - полный перебор будет идти. Но это не столь важно. Это так, вспомогательные данные. мож кому поможет дать дельный совет :)

> И не получится наверное. Ибо в условии не четкое равенство.
> Другими словами записей, удовлетворяющих условию, может
> быть и миллион и два и больше. Индекс при этом будет только
> мешать из-за дополнительного чтения. Поэтому оптимизатор
> видимо (справедливо) посчитал, что фул скан будет производительнее.
>

Может, и справедливо, но неправильно в моем случае. В результате такого запроса у меня ВСЕГДА получается от 0 до 10-15 записей из 16 миллионов.

Но дело даже не в оптимизаторе. Когда я пишу

select * from DocLimits WITH( INDEX( i_DocLimits_LoHi ) ) where LowLimit <= 1000000 AND HighLimit >= 1000000

ничего в лучшую сторону почти не меняется. Т.е. запрос в среднем в 2 раза быстрее начинает работать. Ну, не 4 минуты, а 2 :(
А должно быть моментально по моему разумению, коль уж в индексе поля сортировать возможно в разных направлениях...

← →
Sergey13 © (2007-03-20 14:12) [3]

> [2] YuRock © (20.03.07 14:07)

А если, кстати сделать просто
CREATE INDEX i_DocLimits_LoHi ON DocLimits( LowLimit, HighLimit)
это что то поменяет?

← →
YuRock © (2007-03-20 14:16) [4]

В том то и дело, что НИЧЕГО. Я отчасти потому и вопрос задал, работает ли эта фича -указание для разных полей разное напровление сортировки.

← →
Val © (2007-03-20 14:20) [5]

а что при двух отдельных индексах?

← →
YuRock © (2007-03-20 14:27) [6]

> Val © (20.03.07 14:20) [5]
> а что при двух отдельных индексах?

То же, что и

> Когда я пишуselect * from DocLimits WITH( INDEX( i_DocLimits_LoHi
> ) ) where LowLimit <= 1000000 AND HighLimit >= 1000000ничего
> в лучшую сторону почти не меняется. Т.е. запрос в среднем
> в 2 раза быстрее начинает работать. Ну, не 4 минуты, а 2
> :(

Кстати, такая особенность: если я пишу такое:

SELECT *
FROM DocLimits
ORDER BY LowLimit ASC, HighLimit DESC

То оптимизатор сам выбирает правильный план для сортировки, зараза, а для фильтра - не хочет не то что выбирать, но и юзать то, во что его носом тыкают :(

← →
Jan (2007-03-20 14:33) [7]

> CREATE INDEX i_DocLimits_LoHi ON DocLimits( LowLimit ASC,
> HighLimit DESC )

а если два индекса?
CREATE INDEX i_DocLimits_Lo ON DocLimits( LowLimit ASC) CREATE INDEX i_DocLimits_LoHi ON DocLimits( HighLimit DESC )
в Сервере ASA 9.0.2 можно так написать?
select * from ( select * from DocLimits where LowLimit <= 1000000) t where HighLimit >= 1000000

← →
Jan (2007-03-20 14:35) [8]

> select * from (
> select * from DocLimits
> where LowLimit <= 1000000) t
> where HighLimit >= 1000000

или так:
select t1.* from DocLimits t1 join DocLimits t2 on t1.id = t2.id and t2.LowLimit <= 1000000 where t1.HighLimit >= 1000000

← →
YuRock © (2007-03-20 14:35) [9]

> Jan (20.03.07 14:33) [7]

Я так уже пробовал :)

Да, написать так можно. Но сервер будет использовать только один индекс (во вложенном запросе). Остальное - перебором, т.е. то же самое.

← →
Jan (2007-03-20 14:38) [10]

> select t1.* from DocLimits t1
> join DocLimits t2 on t1.id = t2.id and t2.LowLimit <= 1000000
> where t1.HighLimit >= 1000000

или так:
select t1.* from DocLimits t1 where t1.HighLimit >= 1000000 and exists(select * from DocLimits t2 where t1.id = t2.id and t2.LowLimit <= 1000000)

← →
YuRock © (2007-03-20 14:40) [11]

> select t1.* from DocLimits t1join DocLimits t2 on t1.id
> = t2.id and t2.LowLimit <= 1000000where t1.HighLimit >=
> 1000000

Хм. Отличная идея! Большое спасибо, буду думать...

← →
YuRock © (2007-03-20 14:41) [12]

> Jan (20.03.07 14:38) [10]

Спасибо за советы!

← →
YuRock © (2007-03-20 15:10) [13]

> Jan

Но, к сожалению, толку, пока-что, естественно :) нет :(

Т.к. все-равно в среднем по половине таблицы сканом проходит...

← →
Jan (2007-03-20 15:23) [14]

> Т.к. все-равно в среднем по половине таблицы сканом проходит.
> ..

индекс по каждому полю создал?

← →
YuRock © (2007-03-20 15:30) [15]

есть индексы отдельно на LowLimit и HighLimit, есть на (LowLimit, HighLimit), есть на (Id,LowLimit). Уже на все, что можно.

скопом и пробуешь? может, по очереди?

← →
Jan (2007-03-20 16:06) [18]

вохзможно серверному оптимизатору виднее - когда ему индекс нужен а когда и нет...

> Jan (20.03.07 16:06) [18]
> вохзможно серверному оптимизатору виднее - когда ему индекс
> нужен а когда и нет...

Да эт. понятно, но мне то задачу решить (оптимизировать) надо, а как - я уже представить себе не могу... Ладно, спасибо всем.

на скл.ру сходите, к специалистам по данному серверу.

я там давно сижу :)

Составные индексы с "разной" сортировкой Найти похожие ветки