Почему разные данные в таблице могут обрабатываться с разной производительностью?

Question

У меня возникла странная проблема с производительностью базы данных Postgrtes 10.

У меня есть такая таблица:

CREATE TABLE articles
(
    article_id bigint NOT NULL,
    content jsonb NOT NULL,
    -- few other fields...
    CONSTRAINT articles_pkey PRIMARY KEY (article_id)
)

У нас есть библиотека Python (использующая драйвер psycopg2) для доступа к этой таблице. Он выполняет простые запросы к таблице, один из них используется для массовой загрузки контента и выглядит так:

select content from articles where id between $1 and $2

Я заметил, что с некоторыми идентификаторами он работает довольно быстро, в то время как с другими он в 4-5 раз медленнее. Следует отметить, что в этой таблице есть две основные категории идентификаторов: первая - диапазон 270000000-500000000, а вторая - диапазон 10000000000-100030000000. Для первого диапазона это "быстро", а для второго - "медленно". Помимо больших абсолютных чисел, снимающих их с int в bigintзначения во втором диапазоне являются более "разреженными", что означает, что в первом диапазоне значения являются почти последовательными (с очень небольшим количеством "дырок" пропущенных значений), тогда как во втором диапазоне гораздо больше "дырок" (среднее заполнение составляет 30%).

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

select count(*), sum(length(content::text))
from storage.articles where article_id between %s and %s

Я отрегулировал длину запрошенных патронов, чтобы выровнять итоговую длину JSONB на запрос в среднем.

Дополнительно я вычисляю скорость обработки как возвращенную count(*) делится на время выполнения. Для первого диапазона это 800-1050 строк / с, а для второго - 120-250 строк / с. Другой показатель - это длина обработанного JSON за раз. Поскольку основная сложность в запросе заключается в обработке данных JSONB, я предполагаю, что они должны быть в среднем одинаковыми в обоих случаях. Но нет: это 7-10 МБ / с против 1,5-2,5 МБ / с.

Так что проблема воспроизведена. Хотя в абсолютных значениях он стал быстрее (из-за отсутствия передачи по сети), существенная разница в производительности сохраняется. Я сравнил планы выполнения запросов в обоих диапазонах, они выглядят одинаково (EXPLAIN ANALYZE):

Aggregate  (cost=8046.71..8046.72 rows=1 width=8) (actual time=2924.410..2924.411 rows=1 loops=1)
  ->  Index Scan using articles_pkey on articles  (cost=0.57..8000.02 rows=4669 width=107) (actual time=11.482..61.258 rows=5000 loops=1)
        Index Cond: ((article_id >= 461995000) AND (article_id <= 461999999))
Planning time: 1.028 ms
Execution time: 2924.650 ms

Aggregate  (cost=6216.75..6216.76 rows=1 width=8) (actual time=17704.635..17704.636 rows=1 loops=1)
  ->  Index Scan using articles_pkey on articles  (cost=0.57..6180.69 rows=3606 width=107) (actual time=0.154..563.578 rows=3059 loops=1)
        Index Cond: ((article_id >= '100017010000'::bigint) AND (article_id <= '100017019999'::bigint))
Planning time: 0.404 ms
Execution time: 17704.746 ms

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

Я что-то пропустил? Как исследовать (и исправить) это?

Обновление 1: добавлено EXPLAIN с BUFFERS:

Aggregate  (cost=8635.91..8635.92 rows=1 width=16) (actual time=6625.993..6625.995 rows=1 loops=1)
  Buffers: shared hit=26847 read=3914
  ->  Index Scan using articles_pkey on articles  (cost=0.57..8573.35 rows=5005 width=107) (actual time=21.649..1128.004 rows=5000 loops=1)
        Index Cond: ((article_id >= 438000000) AND (article_id <= 438005000))
        Buffers: shared hit=4342 read=671
Planning time: 0.393 ms
Execution time: 6626.136 ms

Aggregate  (cost=5533.02..5533.03 rows=1 width=16) (actual time=33219.100..33219.102 rows=1 loops=1)
  Buffers: shared hit=6568 read=7104
  ->  Index Scan using articles_pkey on articles  (cost=0.57..5492.96 rows=3205 width=107) (actual time=22.167..12082.624 rows=2416 loops=1)
        Index Cond: ((article_id >= '100021000000'::bigint) AND (article_id <= '100021010000'::bigint))
        Buffers: shared hit=50 read=2378
Planning time: 0.517 ms
Execution time: 33219.218 ms

Обновление 2: больше экспериментов:

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

• Буфер хиты / чтения для таблицы.
• Буфер хиты / чтения для индекса.
• Количество рядов (от Index Scan...)
• Продолжительность исполнения

На основании вышеуказанных метрик я вычисляю унаследованные метрики:

• Скорость чтения с диска: (чтение индекса + чтение таблицы) * 8192 / длительность.
• Коэффициент чтения: (чтение индекса + чтение таблицы) / (чтение индекса + чтение таблицы + совпадения индекса + совпадения таблицы)
• Скорость передачи данных: (чтение индекса + чтение таблицы + совпадение индекса + совпадение таблицы) * 8192 / длительность

Так как "плотность" идентификаторов различается в "маленьких" и "больших" диапазонах, я отрегулировал размер кусков, чтобы в обоих случаях получить около 5000 строк на каждую итерацию, хотя мои эксперименты показывают, что размер кусков не имеет большого значения,

Опубликованный выше результат был подтвержден для всего первого и второго диапазонов (поэтому он был вызван не только случайно кэшированными данными).

Чтобы устранить влияние кеша, я перезапустил сервер Postgres с очищающими буферами:

postgresql stop; sync; echo 3 > /proc/sys/vm/drop_caches; postgresql start

После этого я повторил тест и получил почти такую ​​же картину.

Я рендерил серии из последнего теста в графики и выкладывал их здесь:

Это картина для "маленького" диапазона. Скорость чтения с диска составляет около 10-11 МБ / с равномерно по всему тесту.

Вопрос: почему частота обращений очень высока (коэффициент чтения низкий), хотя я очищал буферы памяти?

Это картина для "большого" диапазона. Это выглядит безумно для меня. В большинстве случаев скорость чтения с диска составляла около 2 МБ / с, но иногда она возрастала до 26–30 МБ / с. Коэффициент чтения также сильно различался.

Во время тестов я проверил скорость чтения с диска iotop и нашел его показания очень близкими к рассчитанным мной. Я не могу сказать, что отслеживал это все время, но я подтвердил это, когда оно было 2 МБ / с и 22 МБ / с во втором диапазоне и 10 МБ / с в первом диапазоне. Я также проверил с htop этот процессор не был узким местом и составлял около 3% во время тестов.

Эта проблема воспроизводима как на главном, так и на подчиненном серверах. Мои тесты проводились на ведомом устройстве, в то время как не было никакой другой нагрузки на СУБД или активности диска на хосте, не связанном с СУБД.

Мое единственное предположение состоит в том, что различные фрагменты данных читаются с разной скоростью из-за виртуализации или чего-то еще, но... почему это так строго связано с этими диапазонами? Почему это одинаково на двух разных машинах?

Есть идеи?