В таблице два фильтруемых столбца сильно коррелируют, статистика по каждому свежая, но план систематически недооценивает число строк. Какой механизм приводит к такой ошибке оценки?
Оптимизатор может ошибочно предположить, что условия по двум столбцам независимы, и перемножить их отдельные вероятности. При сильной корреляции это занижает или завышает селективность, поэтому выбирается невыгодный план: например, индексный доступ с большим числом обращений к таблице вместо последовательного чтения.
Оптимизатору нужно оценить количество строк до выполнения запроса, чтобы сравнить стоимость разных планов. Полные данные о совместном распределении всех столбцов дороги по объёму и времени построения, поэтому классические статистики обычно описывают столбцы по отдельности.
Такой подход хорошо работает при независимых или слабо связаных признаках. Он становится неточным, когда значения столбцов зависят друг от друга, например когда регион почти однозначно определяет валюту или категория товара связана с конкретным диапазоном цен.
Предположим, что условия по двум столбцам по отдельности достаточно редкие. Если оптимизатор считает их независимыми, он может ожидать почти полное перемножение их селективностей, хотя на практике второе условие часто встречается именно среди строк, уже отобранных первым.
Ошибка оценки количества строк влияет на стоимость соединений, выбор между индексным доступом и сканированием, порядок соединения таблиц, размер промежуточных результатов и необходимость сортировки. Свежая статистика по каждому столбцу не гарантирует точности, если в ней не отражена их совместная зависимость.
Оптимизатор строит оценку селективности условий на основе гистограмм, наиболее частых значений, кардинальности и других сведений. При отсутствии информации о зависимости он применяет модель, близкую к предположению независимости: вероятность совместного выполнения условий оценивается через произведение отдельных вероятностей.
При положительной корреляции условия не являются независимыми. Например, если почти все строки с одним значением первого столбца имеют одно и то же значение второго, совместное условие будет встречаться намного чаще, чем предсказывает произведение независимых вероятностей. При отрицательной корреляции возможна обратная ошибка — завышение количества строк.
Диагностический признак — большое устойчивое расхождение между оценочным и фактическим количеством строк в плане выполнения на узле фильтрации или соединения. Если статистика обновлена, а ошибка сохраняется на одних и тех же сочетаниях условий, следует проверить корреляцию столбцов, а не только давность статистики.
В СУБД, поддерживающих многоколоночную или расширенную статистику, можно собрать сведения о совместном распределении столбцов. В других системах помогает подходящий составной индекс, поскольку его статистика иногда даёт оптимизатору сведения о префиксе ключа, но это зависит от реализации и не заменяет универсально многоколоночную статистику.
Индекс следует выбирать не только для исправления оценки. Он должен соответствовать реальным условиям фильтрации, порядку сортировки и требуемому числу строк. Дополнительная статистика увеличивает затраты на сбор и хранение метаданных, а слишком большое количество объектов статистики усложняет сопровождение и не всегда улучшает план.
В таблице заказов поля «регион» и «склад» были сильно связаны: каждый склад обслуживал ограниченный набор регионов. Запрос фильтровал оба поля, но план ожидал несколько десятков строк вместо нескольких тысяч и выбирал индексный доступ с множественными обращениями к таблице.
Рассматривались три варианта. Полное сканирование могло убрать дорогие обращения к таблице, но увеличивало стоимость для действительно редких сочетаний. Перестройка индекса могла улучшить доступ к данным, однако не гарантировала точной оценки зависимости. Обновление обычной статистики не решало проблему, поскольку она уже была свежей.
Выбранным решением стала многоколоночная статистика для поддерживаемой СУБД с последующей проверкой плана на типичных и граничных сочетаниях значений. Оценка количества строк стала ближе к фактической, и оптимизатор начал выбирать план с меньшим числом случайных обращений; при этом необходимость в индексе оценивалась отдельно по профилю запросов.
Нет. Обновление повышает актуальность распределения каждого столбца, но не обязательно добавляет информацию об их совместной зависимости. Если ошибка вызвана предположением независимости, повторный сбор той же одномерной статистики может почти ничего не изменить.
Нет. Его эффективность зависит от СУБД, порядка ключей, вида условий и того, какие статистики оптимизатор строит по индексу. Даже если индекс помогает оценить ведущий префикс, он может не описывать совместное распределение всех нужных столбцов; кроме того, индекс, созданный только ради оценки, может оказаться неоправданно дорогим для вставок и обновлений.
Неверная кардинальность промежуточного результата влияет на весь последующий план. Оптимизатор может выбрать неподходящий алгоритм соединения или порядок обработки таблиц, а ошибка количества строк затем многократно усилится на следующих узлах. Поэтому проверять нужно не только итоговое время, но и места, где оценочное количество строк резко расходится с фактическим.