Comments 33
Такие отличия наводят на мысль о некоем технологическом превосходстве данной СУБД
MS SQL Server действительно очень хорош. Настолько, что в какой-то мере расслабляет разработчиков. Ибо код, который в других СУБД до неприличия медленно работает, может вполне сносно работать в SQL Server. Потом было очень трудно переходить на другие БД. Или просто литературу по теме оптимизаций запросов читать, потому что внутренний голос шептал "ха, я так не делал, а все равно работало быстро, можно это и неважно вовсе". Так что приобретать первый опыт с ней не рекомендую 🙂
Плюсую коммент! MS SQL server!, a не просто SQL server, будто Postgres вовсе и не SQL. Странно слышать такое в стать от Postgres Pro.
Позвольте осторожно Вас поправить - SQL server - это официальное название этого продукта Microsoft. Под таким названием он проходит в документации и, например, настройках драйверов, где указывается тип подключаемой базы. MS в названии нет.
PostgreSQL ранее назывался Postgres, но после переименования в PostgreSQL (Postgres Query Language) начались разные толкования этого имени (как Postgre SQL, postgre), после чего автор переименования назвал это одной из своих больших ошибок в жизни. SQL - это только название. Постгрес - это сервер баз данных, а не SQL сервер. SQL у него - крошечное мгновение перед функцией Parse()
Ещё в mssql есть indexed views, которые могут использоваться как индексы. В postgres такое дорого поддерживать нас триггерах, ну и запросы все надо переписывать.
А covered index не подходят в таком случае?
https://www.postgresql.org/docs/current/indexes-index-only-scans.html
indexed views, которые могут использоваться как индексы.
indexed views не могут использоваться как обычные индексы никак. indexed views используются как материализованные подзапросы, ровно также как Materialized views в Oracle. Вся разница только в процессе обновления. Indexed views у MS включены в транзакцию обновления таблицы на которой они построены и автоматически обновляются вместе с ней.
Но также это накладывает и серьезные ограничения на функциональность этих indexed views, что очень сильно снижает их релаьную полезность
В mssql сервере indexed views реализованы через триггеры (которые не высвечиваются в списке триггеров) и, конечно, как вы написали, такое дорого поддерживать на триггерах. Кроме того, indexed views имеют ограничения по запросам на FULL и LEFT join, на использование столбцов - запросы надо переписывать.
В первом запросе ещё интересный факт: Group by делается только по ключам одной таблицы из Join. То есть если эта таблица используется как Probe side - то все многочисленные строки, полученные из одной строки в Probe side на выходе Join, попадут в одну группу. Хеширование и поиск группы можно делать один раз на строку Probe side, а не на каждую строку вышедшую из Join. В конкретном случае это ускорило бы в тысячи раз, а не жалкий десяток, который даёт параллелизм. Интересно, использует ли какая-либо база такую оптимизацию.
Спасибо за статью. Очень часто выясняется, что SQL server таки стоит своих денег.
Возможно. На самом деле SQL Server тоже частенько спотыкается на ровном месте, когда Postgres таки справляется. Просто у нас, разработчиков, профит зависит не от роста продаж Postgres-решений, а от количества выявленных багов и недостатков. Поэтому что мне важно, то и демонстрирую ;).
кто первый к оптимизации плана LLM прикрутит - тот и молодец!
Не вижу смысла. Он может дать новый алгоритм поиска планов в существующем пространстве поиска, а я как раз пытался показать, что само пространство поиска также нужно расширять. К тому же, доверия ему мало, поскольку построить корректный план - то ещё искусство. Мы же не хотим выдавать данные, лишь приблизительно соответствующие тому, что запрашивалось?
К тому же, пара предыдущих заходов на эту идею показали, что такие сложные методы не гарантируют нам план минимального качества или хотя бы предсказуемой стабильности выполнения одного и того же запроса.
Ну а изменяемость данных сводит эффект обучения к нулю. Разве что на аналитике такой оптимайзер будет иметь смысл?
Есть какие-то реальные примеры спотыкания на ровном месте у SQL Server? Честно говоря, последний раз такое помню на версии 6,5 в 1997 году.
Да, а почему в статье ничего нет про кластерные ключи/индексы? Их уже приделали в новых версиях (в 14 вроде не было)?
Про проблемы SQL Server лучше к сейлам/пресейлам - разработчику постгреса от этого один убыток ;). Хотя даже в этом посте есть пример с нодой Spool, где MS промахнулся и отработал хуже постгреса. Мне важна принципиальные возможности технологии, а не мелкие недочеты, поэтому и не заострял внимание.
А про индексы чего писать? Есть, работают, все хорошо - нашему брату никакого интереса.
Перечитал текст, не увидел места, где "Акела промахнулся", это непринципиально, под "спотыканием на ровном месте" я вспоминаю, например, многим неизвестную необходимость полуручного обновления статистики при обработке в БД сложных данных, запуском после каждых 1000 документов.
Зато я вижу, что вы сознательно ограничили параллелизм в запросах SQL Server. Не уверен, что это правильный подход, зная, что параллелизм в Постгресе, мягко говоря, находится на начальной стадии, хотя и появился в версии 12.
По поводу кластерных ключей/индексов, почему никакого интереса? Ускорение на CRUD операциях в 1,5-2 раза, использование в аналитике также даёт преимущества, хотя там уже можно строить read-only постгресовские аналоги
Действительно, вспомнил, что переписал пример, чтобы не было плохих планов SQL Server и не вызывало лишних вопросов :), мы же здесь наличие/отсутствие технологии обсуждаем, а не качество планирования в целом.
Параллелизм ограничил, чтобы продемонстрировать, что SQL Server может вставить Spool-ноду - это ключевая тема здесь. Это обычное дело в нашей работе - приходится много что отключать в модельных примерах, чтобы сохранить их простыми и наглядными.
С индексами в Postgres также полный порядок: тут тебе и кластерные индексы, и частичные, и всякие TID Scan, IndexOnlyScan, BitmapScan ... . Сейчас вот ScikScan прикручивают. Ни разу не пришлось наблюдать, чтобы SQL Server здесь как-то сильно выигрывал, поэтому и упоминать было нечего. Но если есть примеры, можно будет разобрать.
Я понимаю, что можно и отключить сильную сторону у "оппонента", но не уверен, что нужно это делать, тем более, что хинты в постгрес если и завезут, то очень не скоро по религиозным соображениям, а в SQL Server такое временное отключение можно прописать в запросе, не делая глобальных изменений на уровне всей БД, как это принято в постгресе.
Специально еще раз посмотрел на CLUSTER в постгресе. Это просто физическая сортировка таблицы, которую нужно принудительно повторять после каждой модификации данных. "Clustering is a one-time operation". То есть никаких кластерных индексов в постгресе до сих пор нет, и сравнивать с кластерными индексами в SQL Server просто некорректно.
Простейший CRUD-тест произвольной выборки по ключу из постгреса и SQL Server, думаю, вы и сами сможете соорудить минут за 15. Было бы желание. Всякие ORM только и заняты, что извлекают по ключу объекты из БД.
Хм, но я все-таки буду отключать, дабы не превращать примеры в простыни sql-кода. Иначе это никто читать не будет, а кто будет - не поймет :).
Будет интересно почитать про сравнение индексации в Postgres и SQL Server. Подозреваю, что если MS сделала имплементацию кластеризации на лету, то это будет очень больно для пишущей нагрузки. Но буду рад увидеть разбор, это всегда полезно!
Кластеризация индекса, насколько я вижу, дает хороший эффект на выборках по диапазону, значимых обьемов строк. Ну и в аналитике, которую я в основном рассматриваю, вклад сканирования во время выполнения сильно падает - там в основном сортировки и джойны грузят систему. Выигрыш от индекса может быть, но в моем опыте таких примеров просто не было
Моя цель - не спор и придирки по мелочам, наоборот, вам - благодарность за полезный обзор, который можно дополнить.
Кластерные индексы реализовала не MS, а Sybase, откуда они 30+ лет назад переехали в MSSQL. Эта опция стоит по умолчанию для всех первичных ключей. "Проблемы" с записью могут возникать при непоследовательных значениях ключей, но в реальности в 90 % будет автоинкрементный целочисленный тип. Для GUID в версии 2008 даже ввели newsequentialid. В других случаях может поможет fillfactor. Пишу "проблемы" в кавычках, потому что даже на строковых случайных значениях отставание от целочисленных порядка 30% (тест проводил для версии 2000)
Выигрыш в том, что данные лежат там же, где индекс, и нет дополнительных операций. Лет 10 назад в ФБ группе я уже постил результаты выборки строк таблицы по ключу, тогда они различались в 2 раза в пользу MS. Сейчас сходу найти не могу, повторять же нет смысла.
Скажу больше, MSSQL быстрее Redis на таких операциях: https://blog.arbinada.com/category/01707-sql-server-vs-redis.html
Еще кластерный индекс позволяет делать супер быстрый merge join без сортировок когда джоин по обеим кластерным ключам.
Так-то и в Postgres, если просто добавлять строки с инкрементальным счетчиком в качестве индексного поля, то кластеризация будет сохраняться.
Но весь вопрос в поддержании кластеризации. Мы же не можем всё время добавлять в конец хипа, как и в индекс, иначе будет блоатинг таблиц. Да и апдейт в кластеризованом индексе будет недешевый - даже при наличии UNDO-log, не все же типы постоянной длины.
Вот я бы и почитал, как в MS это решают - у них хорошие алгоритмы планирования/выполнения, должны быть и интересные технологии хранения.
кластеризация будет сохраняться.
будет, но заставить постгрес сделать merge join по таким кластеризованным таблицам, без сортировки невозможно. А значит бенефита от такой кластеризации минимум
Вот я бы и почитал, как в MS это решают
так и решают, рекомендуя PK кластерные делать последовательными, ну либо на крайняк fillfactor разреженный, чтобы место было.
Строк с автоинкрементным счетчиком недостаточно. Heap и cluster имеют физически разную организацию. В общем случае индекс на heap это дополнительный указатель, по которому нужно пройти, чтобы получить сами данные
В Постгрес добавлено ключевое слово INCLUDE в создание индекса, которое выполняет ровно те же функции, что и кластерный индекс SQLServer, только с дополнительной гибкостью по выбору полей и по использованию в других видах индексов, а не только BTREE. Но мы упорно не будет это замечать, так как там нет слова CLUSTER.
Опция INCLUDE существует в SQL Server очень давно. Я бы предложил вам разобраться, в чем отличие кластерных индексов от предлагаемой вами "замены".
У меня к SQLServer вопросов нет (есть у них INCLUDE или нет). Что такого не делает индекс btree PostgreSQL c INCLUDE, что делает кластерный индекс SQLServer? Я бы сказал, что при прочих равных у SQLServer есть оверхед из-за того, что он вынуждает для этой таблицы использовать не указатель строки, а ключ индексирования, что может быть длиннее указателя строки. Дополнительный минус при прочих равных - SQLServer хранит все столбцы (так надо, тут вопросов нет), но и поиск по дереву из-за этого требует большего ввода-вывода. Зато есть слово CLUSTER, а у другие его используют для других целей.
Индекс отделен от данных в куче, а кластерный "индекс" это и есть сами данные, отсортированные по колонке (как правило, ключу). Нет никакой ложки никаких указателей.
Если брать аналог с INCLUDE, то это будет индекс со включением в него всех колонок таблицы с соответствующим дублированием размера и данных таблицы.
Оптимизация запросов SQL Server V/S PostgreSQL: есть куда расти?