PostgreSQL brainteaser: медленный Index Scan

[a0fs]

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

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

[danolivo]

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

[sl_bug]

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

[ialexander]

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

[Sleuthhound]

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

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

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

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

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

[danolivo]

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

[fzfx]

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

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

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

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

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

[danolivo]

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

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

[fzfx]

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

[Kilor]

"Уж сколько раз твердили миру..." смотреть на 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

[danolivo]

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

[swa111]

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

[danolivo]

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

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

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

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