Комментарии 7
А зачем вы использовали индекс по паре знаний, а не два индекса, если у вас значения не связанные?
BitmapAnd обычно стоит избегать, он почти всегда проигрывает составному индексу за редкими исключениями.
https://explain.tensor.ru/archive/explain/f0d4564d9536a996688fe0b96a4a87b2:0:2024-03-15
Странное говорите... У вас-то он выиграл относительно составного индекса, хоть и немного. И по моему опыту он всегда выигрывает, что очевидно. Сравнивать только время поиска, то выиграл он очень хорошо.
И по количеству прочитанных страниц он вас тоже впереди составного индекса (в полтора раза, однако!), причем, по мере роста объема базы отрыв будет нарастать. А уж если увеличить количество условий и/или индексов, то там и в разы разница будет. Нельзя так индексы использовать, это по количеству страниц при поиске видео.
И более того, по количеству страниц он на уровне примера с gist!
И теперь возникли вопросы к gist и чистоте эксперимента - количество страниц одинаковое, а время исполнения запросов отличается на порядок. Тут что-то не так. Тут совершенно точно дело не во времени поиска. Кэширование, разное хранение данных, что-то ещё разное.
Относительно составного по времени он тут выиграл 5.03 против 5.30, и 410 против 461 по страницам - то есть "копейку". Но выиграл ровно потому, что "двихдиапазонный" поиск по btree заведомо неэффективен.
А вот gist как раз выигрывает за счет более оптимизированного хранения самого дерева индекса - из heap страницы-то читались все разы одни и те же 338 за примерно 0.28-0.30мс.
Я про первый вариант gist. Количество страниц индекса совпадает с их количеством в случае двух индексов, но время чтения этих страниц в 15 раз меньше. Вот это странно. Что-то тут с временами не так, а если ориентироваться только на количество страниц, то картина получается немного иной, хотя sp-gist всё равно окажется впереди. Но зато обычный gist сравняется с правильными индексами, что логично, так как чудес не бывает и никаких супер-волшебных алгоритмов там под капотом нет.
Кроме того, у вас эксперимент все равно не чистый. Тогда нужно было и с обычными (двумя!) индексами date в целое/плавающее преобразовать!
Самая страшная тайна заключается в том, что внутри себя gist использует ровно те же b-trees ровно таким же образом! По индексу на каждое изменение - всё так, как и должно быть в нормальной базе. Там нет никакой магии. И при поиске не должно быть большой разницы с обычными индексами плавающих или целых чисел. А вы почему-то исходите из того, что у gist под капотом какая-то волшебная магия.
Какая уж тут магия? Все вполне объяснимо: gist достаточно быстро "схлапывается" до нужного прямоугольника, где находятся только искомые точки, а вот btree, фактически, для каждого подходящего значения dt делает вложенный поиск интервала по sum, что явно не быстро, поскольку линейно растет с количеством уникальных подходящих значений dt в интервале.
То есть нашли мы, например, значение dt = '2023-12-15', полезли искать внутри по sum - там ничего подходящего, а время уже потрачено.
Ради интереса полез в gist. Да, там должен быть заметный прирост скорости, так как поиск осуществляется сразу по двум индексам, а деревья сбалансированы.
Тем не менее результаты странные, так как при равном количестве страниц индекса скорость поиска отличается на порядок.
SQL HowTo: один индекс на два диапазона