Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Почему оптимизатор отказывается использовать фильтрованый и covered индекс?  [new]
Yuri Abele
Member

Откуда: Латвия> Литва > Тольятти > Wiesbaden > Karlsruhe
Сообщений: 1661
Привет!

Есть огроменная таблица (30 GB где-то) под именем
    FileMetaData.File

В этой таблице есть, креме прочих, поля:
    ID INT, Priority SMALLINT, HashSum VARBINARY(MAX)

Есть фильтрованый индекс:
CREATE INDEX [FileMetaData.File.IX.Priority+Id WHERE HashSum IS NULL] ON [FileMetaData].[File]
(
	[Priority] ASC
)
INCLUDE ([Id]) 
WHERE ([HashSum] IS NULL);


И есть простой запрос:
SELECT COUNT(1) FROM FileMetaData.[File]
--WITH (INDEX([FileMetaData.File.IX.Priority+Id WHERE HashSum IS NULL]))
WHERE HashSum IS NULL;

И вот какого <CENSORED :-)> он выбирает стратегию "Clustered Index Scan"?
Причем, если я раскомментирую USE INDEX HINT, то всё еще хуже - он для каждой строки из индекса (делает "Index Scan" по нему), делает "Nested Loops"=>"Key Lookup" по кластерному индексу?

Почему просто не посчитать сколько листовых элементов в выше упомянутом индексе? Или вообще его статистику прочитать ...
2 июл 19, 10:47    [21918778]     Ответить | Цитировать Сообщить модератору
 Re: Почему оптимизатор отказывается использовать фильтрованый и covered индекс?  [new]
Yuri Abele
Member

Откуда: Латвия> Литва > Тольятти > Wiesbaden > Karlsruhe
Сообщений: 1661
План выполнения первого запроса

К сообщению приложен файл. Размер - 43Kb
2 июл 19, 10:53    [21918785]     Ответить | Цитировать Сообщить модератору
 Re: Почему оптимизатор отказывается использовать фильтрованый и covered индекс?  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
Yuri Abele,

COUNT(priority)?
2 июл 19, 10:54    [21918787]     Ответить | Цитировать Сообщить модератору
 Re: Почему оптимизатор отказывается использовать фильтрованый и covered индекс?  [new]
Yuri Abele
Member

Откуда: Латвия> Литва > Тольятти > Wiesbaden > Karlsruhe
Сообщений: 1661
План выполнения второго запроса

К сообщению приложен файл. Размер - 56Kb
2 июл 19, 10:54    [21918788]     Ответить | Цитировать Сообщить модератору
 Re: Почему оптимизатор отказывается использовать фильтрованый и covered индекс?  [new]
Yuri Abele
Member

Откуда: Латвия> Литва > Тольятти > Wiesbaden > Karlsruhe
Сообщений: 1661
Статистика (грубая) индекса

К сообщению приложен файл. Размер - 49Kb
2 июл 19, 10:54    [21918790]     Ответить | Цитировать Сообщить модератору
 Re: Почему оптимизатор отказывается использовать фильтрованый и covered индекс?  [new]
Yuri Abele
Member

Откуда: Латвия> Литва > Тольятти > Wiesbaden > Karlsruhe
Сообщений: 1661
TaPaK
Yuri Abele,

COUNT(priority)?

Неа - то же, что и в первом плане выполнения
2 июл 19, 10:55    [21918792]     Ответить | Цитировать Сообщить модератору
 Re: Почему оптимизатор отказывается использовать фильтрованый и covered индекс?  [new]
Sergey Syrovatchenko
Member

Откуда:
Сообщений: 134
Как вариант попробуйте на вычисляемом столбце фильтрованный индекс построить:

ALTER TABLE FileMetaData.[File] ADD IsEmptyHash AS IIF(HashSum IS NULL, 1, 0)
GO

CREATE INDEX ix2 ON FileMetaData.[File] ([Priority])
INCLUDE ([Id])
WHERE IsEmptyHash = 1
GO

Также если нужно чисто считать кол-во строк по индексу, то такой варик быстрее будет на порядок:

SELECT SUM(p.[rows])
FROM sys.partitions p
JOIN sys.indexes i ON p.[object_id] = i.[object_id]
WHERE p.[object_id] = OBJECT_ID('FileMetaData.[File]')
    AND i.[name] = 'ix2'
2 июл 19, 11:06    [21918803]     Ответить | Цитировать Сообщить модератору
 Re: Почему оптимизатор отказывается использовать фильтрованый и covered индекс?  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
Yuri Abele,

скан выбирает скорее всего из за количества, какое соотношение NULL/NOT NULL
2 июл 19, 11:06    [21918806]     Ответить | Цитировать Сообщить модератору
 Re: Почему оптимизатор отказывается использовать фильтрованый и covered индекс?  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36905
Потому что functionality gap
Надо добавлять HashSum в include.

UPD: ну или через вычисляемое поле, как советовали.

Сообщение было отредактировано: 2 июл 19, 11:11
2 июл 19, 11:08    [21918807]     Ответить | Цитировать Сообщить модератору
 Re: Почему оптимизатор отказывается использовать фильтрованый и covered индекс?  [new]
Yuri Abele
Member

Откуда: Латвия> Литва > Тольятти > Wiesbaden > Karlsruhe
Сообщений: 1661
Такое расширение добавит, как минимум, 4 байта на строку. А когда сотня миллионов, это уже чуствуется.
А NULL или NOT NULL - это только один бит в уже существующей маске
2 июл 19, 11:08    [21918808]     Ответить | Цитировать Сообщить модератору
 Re: Почему оптимизатор отказывается использовать фильтрованый и covered индекс?  [new]
Yuri Abele
Member

Откуда: Латвия> Литва > Тольятти > Wiesbaden > Karlsruhe
Сообщений: 1661
Гавриленко Сергей Алексеевич
Потому что functionality gap

Мда ..., нежиданно ...
2 июл 19, 11:10    [21918810]     Ответить | Цитировать Сообщить модератору
 Re: Почему оптимизатор отказывается использовать фильтрованый и covered индекс?  [new]
Sergey Syrovatchenko
Member

Откуда:
Сообщений: 134
Yuri Abele
Такое расширение добавит, как минимум, 4 байта на строку.

Кастуем в BIT получаем вместо 4 байтов - 1. Как вариант работать должно, опять же если нужно просто кол-во то лучше создать индекс но смотреть в метаданные.
2 июл 19, 11:11    [21918811]     Ответить | Цитировать Сообщить модератору
 Re: Почему оптимизатор отказывается использовать фильтрованый и covered индекс?  [new]
Yuri Abele
Member

Откуда: Латвия> Литва > Тольятти > Wiesbaden > Karlsruhe
Сообщений: 1661
Sergey Syrovatchenko
SELECT SUM(p.[rows])
FROM sys.partitions p
JOIN sys.indexes i ON p.[object_id] = i.[object_id]
WHERE p.[object_id] = OBJECT_ID('FileMetaData.[File]')
    AND i.[name] = 'ix2'

Ну да, это кусок из того, как тот же "Disk Usage By Table" работает. Это понятно, да и костыли я прикручу какие-нибудь. Хотелось просто понять, что за "№;%"№:", но коллега выше объяснил.
Еще бы понять, чего они это никак не зафиксят?...
2 июл 19, 11:13    [21918816]     Ответить | Цитировать Сообщить модератору
 Re: Почему оптимизатор отказывается использовать фильтрованый и covered индекс?  [new]
Yuri Abele
Member

Откуда: Латвия> Литва > Тольятти > Wiesbaden > Karlsruhe
Сообщений: 1661
Sergey Syrovatchenko
Кастуем в BIT получаем вместо 4 байтов - 1. Как вариант работать должно, опять же если нужно просто кол-во то лучше создать индекс но смотреть в метаданные.

И поле NOT NULL ...
хм, я подумаю, спасибо за идею!
2 июл 19, 11:14    [21918819]     Ответить | Цитировать Сообщить модератору
 Re: Почему оптимизатор отказывается использовать фильтрованый и covered индекс?  [new]
Yuri Abele
Member

Откуда: Латвия> Литва > Тольятти > Wiesbaden > Karlsruhe
Сообщений: 1661
Yuri Abele
Sergey Syrovatchenko
Кастуем в BIT получаем вместо 4 байтов - 1. Как вариант работать должно, опять же если нужно просто кол-во то лучше создать индекс но смотреть в метаданные.

И поле NOT NULL ...
хм, я подумаю, спасибо за идею!

Тогда уж наверное TINY INT и пихать туда до 256 комбинаций битов.
Дело в том, что у меня несколько подобных полей - MetaInfo например, там то, что через Windows API о файле вытащить можно (типа разрешения картинок или авторов документов)
2 июл 19, 11:18    [21918827]     Ответить | Цитировать Сообщить модератору
 Re: Почему оптимизатор отказывается использовать фильтрованый и covered индекс?  [new]
Sergey Syrovatchenko
Member

Откуда:
Сообщений: 134
Если что у сиквела есть внутренняя оптимизация по хранению типа BIT - 8 колонок такого типа хранится в таблице как 1 байт.
2 июл 19, 11:22    [21918831]     Ответить | Цитировать Сообщить модератору
 Re: Почему оптимизатор отказывается использовать фильтрованый и covered индекс?  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31331
Yuri Abele
Гавриленко Сергей Алексеевич
Потому что functionality gap

Мда ..., нежиданно ...
Самое возмутительное, МС имеет наглость заявлять, что это какой то "gap", а не баг.
И эту бажищу не пофиксили до сих пор, с того обсуждения прошло уже 6 лет
2 июл 19, 11:24    [21918834]     Ответить | Цитировать Сообщить модератору
 Re: Почему оптимизатор отказывается использовать фильтрованый и covered индекс?  [new]
Yuri Abele
Member

Откуда: Латвия> Литва > Тольятти > Wiesbaden > Karlsruhe
Сообщений: 1661
Sergey Syrovatchenko
Если что у сиквела есть внутренняя оптимизация по хранению типа BIT - 8 колонок такого типа хранится в таблице как 1 байт.

Так и я об этом! Этим-то и хотел пользоваться, когда фильтр по IS NULL в индекс запихал.
А так теперь дополнительное COMPUTED поле, которое будет материализовано наложенным индексом
2 июл 19, 11:32    [21918847]     Ответить | Цитировать Сообщить модератору
 Re: Почему оптимизатор отказывается использовать фильтрованый и covered индекс?  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
Yuri Abele,

автор
А так теперь дополнительное COMPUTED поле, которое будет материализовано наложенным индексом

А что даст без PERSIST в фильтр засунуть? Так что дважды материализовано
2 июл 19, 11:42    [21918858]     Ответить | Цитировать Сообщить модератору
 Re: Почему оптимизатор отказывается использовать фильтрованый и covered индекс?  [new]
Yuri Abele
Member

Откуда: Латвия> Литва > Тольятти > Wiesbaden > Karlsruhe
Сообщений: 1661
TaPaK
Yuri Abele,

автор
А так теперь дополнительное COMPUTED поле, которое будет материализовано наложенным индексом

А что даст без PERSIST в фильтр засунуть? Так что дважды материализовано

Вы, похоже, не с начала читали. Хотелось сэкономить, а не новые байты в строчках отгрызать
2 июл 19, 12:41    [21918910]     Ответить | Цитировать Сообщить модератору
 Re: Почему оптимизатор отказывается использовать фильтрованый и covered индекс?  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
Yuri Abele
TaPaK
Yuri Abele,

пропущено...

А что даст без PERSIST в фильтр засунуть? Так что дважды материализовано

Вы, похоже, не с начала читали. Хотелось сэкономить, а не новые байты в строчках отгрызать

так я и говорю что с фильтрованным индексом сэкономить не получится, можно посмотреть в сторону индекисрованного представления но везде свои минусы
2 июл 19, 12:43    [21918912]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить