23.10.2024 06:16
danolivo 13 1300 Источник

В моей работе, когда приходится исследовать и нагружать СУБД нетипичной нагрузкой и синтетическими тестами, часто встречаются случаи загадочного поведения системы: ускорение/замедление времени выполнения запроса на пару порядков, отказ использовать тот или иной индекс и тд. Объяснение странного поведения оказывается в итоге почти всегда тривиальным и хорошо известным опытным DBA. Однако встретив его в реальной эксплуатации первый раз невольно теряешься и на разбор кейса уходит много времени. Вместе с тем, это достаточно интересное упражнение - навроде того, как прорешать задачник по аэродинамике после 10 лет проектирования планеров самолётов. Поэтому предлагаю здесь попробовать формат обсуждения/изучения PostgreSQL в виде задач. Вдруг зайдёт?

Итак, вводные для первой, самой простой задачи. Имеем таблицу и пару многоколоночных индексов, отличающихся друг от друга только порядком следования колонок:

CREATE TEMP TABLE shopping (
  CustomerId bigint, CategoryId numeric, WeekDay text, Total money
);
INSERT INTO shopping (CustomerId, CategoryId, WeekDay, Total)
  SELECT gs % 1E5, gs % 100, 'Day' || (gs % 7), random()*1000::money
  FROM generate_series(1,1E6) AS gs;
CREATE INDEX idx1 ON shopping (CustomerId, CategoryId, WeekDay);
CREATE INDEX idx2 ON shopping (WeekDay, CategoryId, CustomerId);
VACUUM ANALYZE shopping;

Если выполнить запрос на простое сканирование по индексу с заходом в Heap за полем 'Total', то время выполнения запроса может отличаться в разы, в зависимости от того, какой индекс выбран:

EXPLAIN (ANALYZE, TIMING OFF, COSTS ON)
SELECT customerid, categoryid, weekday, total FROM shopping
ORDER BY CustomerId, CategoryId, WeekDay;
/*
 Index Scan using idx1 on shopping  
 (cost=0.42..64093.93 rows=1000000 width=25) (actual rows=1000000 loops=1)
 Planning Time: 0.108 ms
 Execution Time: 377.762 ms
*/

EXPLAIN (ANALYZE, TIMING OFF, COSTS ON)
SELECT customerid, categoryid, weekday, total FROM shopping
ORDER BY WeekDay, CategoryId, CustomerId;
/*
 Index Scan using idx2 on shopping  
 (cost=0.42..63963.29 rows=1000000 width=25) (actual rows=1000000 loops=1)
 Planning Time: 0.127 ms
 Execution Time: 3700.215 ms
 */

Собственно задача: в чём причина такого замедления? В чём PostgreSQL ошибся? Ведь стоимость плана запроса оценивается планнером в примерно одинаковую величину для обоих случаев?

Если есть идеи/проекты/патчи в hackers mailing list, имеющие отношение к этому поведению, предлагаю обсудить в комментариях. Также крайне интересно узнать, наблюдается ли аналогичный кейс в Oracle и MS SQL Server? А если нет, то как у них это работает?

THE END.
Thailand, Chon Buri, South Pattaya.

Комментарии (13)


  1. a0fs
    23.10.2024 06:48
    #27452922

    Селективность и недокрученные цены случайного чтения с диска?

    Вообще в EXPLAIN ANALYZE в последнее время сильно досыпали опций. Там и работа с памятью и с кешами и с буферами и вообще много чего. Интересно посмотреть на это самое много....


    1. danolivo Автор
      23.10.2024 06:48
      #27453350

      Задумка была в том, чтобы читатель сам погонял кейс и нашёл ответ. Иначе - это пассивное чтение, в чем соль?


  1. sl_bug
    23.10.2024 06:48
    #27452944

    в первом случае по факту последовательное чтение, во втором скакание рандомное по страницам потому что weekday меняется значительно чаще.


  1. ialexander
    23.10.2024 06:48
    #27453096

    Вы генерите данные так, что они упорядочены по первому индексу, по факту таблица оказывается кластеризованной по нему. Не удивительно, что запрос по первому индексу быстрее.


  1. Sleuthhound
    23.10.2024 06:48
    #27453180

    Правильно заданный вопрос содержит половину ответа, поэтому я бы для начала давал больше вводных, как то

    1) Фиксировать ос и версию пг, тк от версии к версии вывод того же EXPLAIN ANALYZE может улучшаться дополнительной информацией, что может вносить некоторые ясности. Про возможные оптимизации в самой пг я уже умолчу, там тоже могут быть существенные отличия от версии к версии.

    2) Фиксировать настройки пг с которыми Вы проводите эксперимент (написать хотя бы те настройки которые отличаются от дефолтных в Вашей инсталяции).

    2) Выводить полный EXPLAIN ANALYZE со всеми доступными опциями, а не тот огрызок что вывели Вы.

    Все это нужно для того, чтобы кто-то мог пройти через те же приседания, что и Вы.


    1. danolivo Автор
      23.10.2024 06:48
      #27453326

      Хм, здесь точно все необходимые сведения приведены, я проверил. Для того, чтобы воспроизвести кейс и увидеть проблему ровно ничего больше не нужно, по крайней мере с Постгресом. Это же как в задачнике по физике, зачем вам избыточные сведения?


  1. fzfx
    23.10.2024 06:48
    #27454196

    Особенности работы с temp-таблицами плюс различие в кардинальности значений полей.

    В целом это иллюстрация того, что индекс по нескольким полям при условии того, что в качестве первого поля выбирается поле с наибольшим количеством различных вариантов значений, а в качестве последнего поля - с наименьшим количеством различных вариантов значений, предпочтительнее в плане быстродействия при прочих равных.

    А также того, что от использования TEMP-таблиц при прочих равных лучше отказываться.

    А также того, что автору хочется, чтобы до решения проблемы все доходили одним ему нужным путём, "необходимым и достаточным", а не пытались в установку более адекватного shared_buffers, включение set track_io_timing = on, выполнение ANALYZE (BUFFERS) или избавление от TEMP в CREATE TABLE , а то ещё увидят, что время в основном тратится на чтение с диска, что на "обычной" (не временной) таблице различие менее радикальное за счёт использования shared buffers, а то и вообще придут к тем же выводам насчёт кардинальности/селективности полей другим путём. =)

    Формат постов в основе своей - всецело поддерживаю. Ограничение вариантов решения одним правильным - порицаю.


    1. danolivo Автор
      23.10.2024 06:48
      #27455702

      Хм, я в целом не декларировал найти решение проблемы - только найти объяснение + узнать, имеют ли такой же нюанс mssql & oracle - или они как-то хранят еще и статистику по распределению данных по страницам.

      Но в целом, на ваших комментах я лучше понимаю, как оно должно было быть. Давайте вместе делать формат лучше, раз он продуктивен.


      1. fzfx
        23.10.2024 06:48
        #27456112

        значит, показалось, сорри. =)


  1. Kilor
    23.10.2024 06:48
    #27454410

    "Уж сколько раз твердили миру..." смотреть на buffers при анализе быстродействия:

    EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT customerid, categoryid, weekday, total FROM shopping
ORDER BY CustomerId, CategoryId, WeekDay;
    Index Scan using idx1 on shopping (actual time=0.065..318.714 rows=1000000 loops=1)
  Buffers: local hit=935926 read=12272
Planning Time: 0.096 ms
Execution Time: 356.156 ms
    EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT customerid, categoryid, weekday, total FROM shopping
ORDER BY WeekDay, CategoryId, CustomerId;
    Index Scan using idx2 on shopping (actual time=0.069..9072.660 rows=1000000 loops=1)
  Buffers: local hit=50 read=1004865
Planning Time: 0.102 ms
Execution Time: 9170.632 ms

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

    CREATE TEMP TABLE shopping2 AS
  TABLE shopping ORDER BY WeekDay, CategoryId, CustomerId;
CREATE INDEX idx21 ON shopping2(CustomerId, CategoryId, WeekDay);
CREATE INDEX idx22 ON shopping2(WeekDay, CategoryId, CustomerId);

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT customerid, categoryid, weekday, total FROM shopping2
ORDER BY WeekDay, CategoryId, CustomerId;
    Index Scan using idx22 on shopping2 (actual time=0.073..202.190 rows=1000000 loops=1)
  Buffers: local read=12262
Planning Time: 0.094 ms
Execution Time: 239.664 ms


    1. danolivo Автор
      23.10.2024 06:48
      #27455484

      Верно, хотя было бы интереснее посмотреть обьяснение чуть глубже - ведь таблица небольшая, и все ее страницы уже есть в shared buffers


  1. swa111
    23.10.2024 06:48
    #27455704

    Мое понимание такое - В первом варианте сначала читается индекс и затем по каждой записи идет в основную таблицу. так как порядок записей совпадает, то страница не вытесняется из кеша и просто читается повторно из оперативной памяти.
    Во втором случае все начинается так же, но так как сортировка индекса не совпадает с сортировкой в таблице, то происходит постоянный промах, а старые страницы вытесняются из кеша. В худшем случае приходится прочитать страницу данных для каждой записи + полный индекс.


  1. danolivo Автор
    23.10.2024 06:48
    #27458796

    Большое спасибо всем за ответы! Весьма ценный фидбэк.

    Касательно задачи: за счет временных таблиц удалось в простой форме эмулировать жизненную ситуацию, когда одновременно и не используются background workers, и страницы таблицы не помещаются в память (дефолтный размер temp_buffers всего 8 МБ). Таким образом, постоянная подкачка с диска новых страниц создает оверхед, выражающийся в настолько большой разнице времени выполнения.

    Важный момент здесь - одинаковые оценки стоимости использования обоих индексов. Вероятно, здесь есть пространство для совершенствования оптимизатора.

    Открытым остался вопрос, а не продвинулись ли Oracle и SQL Server дальше в своих стоимостных моделях доступа к диску.