Оптимизация индексов для большой таблицы PostgreSQL
У меня есть большая таблица (location_history) с uid и geohash, и они оба являются первичными ключами и проиндексированы.
Indexes:
"location_history_pkey" PRIMARY KEY, btree (uid, geohash)
"ix_location_history_geohash" btree (geohash)
"ix_location_history_uid" btree (uid)
Но когда я запрашиваю данные со следующими ограничениями, они работают очень медленно (169 секунд!), Даже если два столбца уже проиндексированы:
EXPLAIN (analyze, verbose, buffers) SELECT * FROM location_history WHERE uid = 123 AND geohash >= 'hzzzzzzzzzz';
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using location_history_pkey on public.location_history (cost=0.71..84735.92 rows=22438 width=29) (actual time=1.434..114713.751 rows=189319 loops=1)
Output: created_at, updated_at, uid, geohash, is_from_zenly
Index Cond: ((location_history.uid = 123) AND ((location_history.geohash)::text >= 'hzzzzzzzzzz'::text))
Buffers: shared hit=5822 read=179864 dirtied=132 written=19338
I/O Timings: read=111433.601 write=2564.930
Query Identifier: -7646491345250917333
Planning Time: 0.103 ms
Execution Time: 114746.908 ms
(8 rows)
А вот и результат от pgstattuple():
SELECT * FROM pgstattuple(416708);
table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent
--------------+-------------+--------------+---------------+------------------+----------------+--------------------+------------+--------------
812873588736 | 15570800464 | 685124233392 | 84.28 | 1506 | 66264 | 0 | 394834592 | 0.05
(1 row)
bloat_ratio составляет около 34%:
SELECT 100-(pgstatindex('location_history_pkey')).avg_leaf_density;
?column?
--------------------
34.260000000000005
(1 row)
Не могли бы вы поделиться советами по ускорению запроса? Спасибо.
@kometen сейчас более 10 миллиардов строк, а объем оперативной памяти составляет 128 ГБ (экземпляр AWS db.m6i.8xlarge)
Если у вас было много обновлений/удалений, возможно, стоит переиндексировать + вакуумный анализ. Что такое «геохэш» (довольно необычно иметь >= в тексте)?
Не могли бы вы поделиться результатами объяснения (анализа, подробных сведений, буферов) для этого утверждения? Это дает вам подробную информацию о плане запроса, выполнении и почему все так быстро/медленно, как есть.
@Bruno В этой таблице есть только INSERT и SELECT. geohash — это строка из 7 цифр, например dr5rfk7, обозначающая местоположение (geohash.softeng.co/dr5rfk7)
Я бы попробовал пару вещей. (1) В достаточно современной версии PostgreSQL разделите таблицу, по крайней мере, по uid (например, по диапазону или хешу). (2) Интересно, эффективен ли >= для текста, поэтому, возможно, его стоит преобразовать в int. Если я не ошибаюсь, каждый буквенно-цифровой символ в geohash представляет 5 бит: все должно легко уместиться в 64-битное целое число (преобразование должно быть легким, если все они имеют длину 7 символов).
@FrankHeikens Конечно, я запущу EXPLAIN и обновлю исходный пост после того, как всплеск трафика закончится. Спасибо за вашу помощь!
@Bruno (1) Мы уже начали работать над секционированием, но перед этим мы хотели бы попробовать оптимизировать индекс. (2) Хорошо, попробую, спасибо!
@FrankHeikens Я обновил исходный пост, включив его в результат объяснения (анализ, подробный, буферный), спасибо!
IO довольно медленный, всего несколько МБ в секунду. Проверьте pgstattuple, чтобы узнать, есть ли у вас раздувание индекса: postgresql.org/docs/current/pgstattuple.html
Сколько существует различных uid? С меньшим количеством uid индекс также становится менее полезным, планировщик запросов может в некоторых случаях прибегать к сканированию таблицы.
@kometen Не уверен, что можно не иметь индекса (uid, geohash), так как это первичный ключ. Индекс только на geohash есть, но не используется.
@kometen uid около 10 миллионов
@FrankHeikens Я обновил пост, указав результат от pgstattuple(). Я новичок в раздувании индекса, можете ли вы сказать мне, указывают ли результаты на раздувание индекса? Если да, то следующий шаг, который мне нужно сделать, чтобы «REINDEX»? Спасибо.
@FrankHeikens Я также включил bloat_ratio, что составляет 34%
Ответы 1
Вы могли бы получить большое улучшение, если бы у вас было сканирование только индекса, которое вы могли бы включить в индекс все столбцы, необходимые для запроса, по-видимому (uid, geohash, is_from_zenly, created_at, updated_at). В этом случае нужно будет посещать только страницы индекса, а не (случайно разбросанные) страницы таблицы, если таблица хорошо очищена. Вы можете получить этот «покрывающий» индекс без частичного дублирования индекса первичного ключа, выполнив:
alter table location_history add constraint location_history_pkey primary key (uid, geohash) include (is_from_zenly, created_at, updated_at)
Конечно, вам нужно сначала удалить текущий первичный ключ, так что это повлечет за собой некоторое время простоя. Вместо этого вы можете просто создать новый индекс и жить с избыточными индексами.
Конечно, если ваш реальный набор запросов может содержать больше столбцов, чем те, которые вы показали в своем примере, это может быть не очень полное решение.
Сколько всего строк в таблице? Сколько оперативной памяти на сервере?