#postgresql
#postgresql
Вопрос:
В PostgreSQL 11 на AWS есть запрос mview в этой базе данных OLAP, задействованные таблицы огромны — в среднем 50-100 миллионов записей в большинстве случаев содержат сотни столбцов. Запрос выполняется некоторое время, а затем выдает ошибку «На устройстве не осталось свободного места». Я мог видеть, что он генерирует около 500 ГБ данных временного файла. Иногда это выполняется, а иногда завершается сбоем — вероятно, из-за других запросов, выполняемых одновременно и вызывающих сбой.
Таблицы разбиты на разделы и проиндексированы в PKs и FKS. Использование параллелизма (4) с увеличенной work_mem (4 ГБ).
Соединения происходят примерно в 10 таблицах, и все они объединяются в столбцах PK и FK. Я вижу, что происходит сокращение раздела, но хэш-соединения прерывают запрос.
Есть ли какой-либо способ избежать хэш-соединений? Если мы создадим хэш-индексы для объединяемых столбцов, будет ли PostgreSQL избегать операции хэширования и вместо этого использовать хэш-индексы для таблиц и объединять их. Таким образом, я чувствую, что можно избежать ресурсоемкого хэширования и не будет необходимости во временных файлах. Я пытался, но, похоже, не работает, когда я запрашиваю таблицу с определенными значениями, тогда она использует хэш-индекс, но когда я соединяю таблицы, кажется, что он выполняет свое собственное хэш-соединение.
Мой вопрос заключается в том, как оптимизировать массовые объединения таблиц в PostgreSQL для устранения сбоев в пространстве и ускорить его выполнение — сейчас на это уходит пара часов. Любые рекомендации или предложения.
SELECT
...
250 cols from various below tables
...
FROM
x.table1 yankee_charlie
INNER JOIN x.juliet_juliet juliet_alpha ON
yankee_charlie.bravo_tango = juliet_alpha.kilo_four
LEFT OUTER JOIN y.sierra_delta kilo_foxtrot ON
yankee_charlie.sierra_six = kilo_foxtrot.november_juliet
LEFT OUTER JOIN z.xray_bravo uniform_delta ON
yankee_charlie.alpha_four = uniform_delta.papa_mike
LEFT OUTER JOIN x.papa_whiskey india_five ON
yankee_charlie.golf = india_five.tango_mike
LEFT OUTER JOIN x.lima_romeo hotel ON
yankee_charlie.zulu_oscar = hotel.bravo_hotel
LEFT OUTER JOIN x.romeo_golf foxtrot_whiskey_two ON
yankee_charlie.xray_alpha = foxtrot_whiskey_two.tango_quebec
LEFT OUTER JOIN a.seven_yankee zulu_four ON
yankee_charlie.uniform_india = zulu_four.seven_bravo
LEFT OUTER JOIN a.quebec_november kilo_lima ON
zulu_four.mike_four = kilo_lima.lima_uniform
LEFT OUTER JOIN a.tango_romeo romeo_xray_echo ON
kilo_lima.lima_uniform = romeo_xray_echo.lima_uniform
LEFT OUTER JOIN b.seven_three four_hotel ON
kilo_lima.zulu_three = four_hotel.whiskey_victor_bravo
LEFT OUTER JOIN x.juliet_yankee five_quebec ON
yankee_charlie.oscar_india = five_quebec.six_xray
LEFT OUTER JOIN z.romeo_two delta_mike ON
yankee_charlie.four_zulu = delta_mike.whiskey_victor_seven
LEFT OUTER JOIN z.delta_lima six_alpha ON
yankee_charlie.xray_three = six_alpha.kilo_whiskey
LEFT OUTER JOIN x.five_hotel xray_quebec ON
yankee_charlie.bravo_tango = xray_quebec.kilo_four
LEFT OUTER JOIN y.sierra_delta mike_foxtrot ON
yankee_charlie.two = mike_foxtrot.november_juliet
LEFT OUTER JOIN y.sierra_delta india_three ON
yankee_charlie.victor = india_three.november_juliet
WHERE
yankee_charlie.romeo_xray_two >= (CURRENT_DATE - INTERVAL '5 years')
AND yankee_charlie.romeo_xray_two < papa_five('year',(CURRENT_DATE INTERVAL '1 year')) - INTERVAL '1 day';
Gather (cost=33464846.41..475412138.09 rows=97965031 width=7161)
Workers Planned: 2
-> Parallel Hash Left Join (cost=33463846.41..465614634.99 rows=40818763 width=7161)
Hash Cond: (yankee_charlie.victor = india_three.november_juliet)
-> Parallel Hash Left Join (cost=33330811.86..392519286.24 rows=40818763 width=7109)
Hash Cond: (yankee_charlie.two = mike_foxtrot.november_juliet)
-> Hash Left Join (cost=33197777.31..321716804.91 rows=40818763 width=7056)
Hash Cond: (yankee_charlie.xray_three = six_alpha.kilo_whiskey)
-> Hash Left Join (cost=33197713.71..321608781.15 rows=40818763 width=7003)
Hash Cond: (yankee_charlie.four_zulu = delta_mike.whiskey_victor_seven)
-> Hash Left Join (cost=33197035.05..321500899.07 rows=40818763 width=6863)
Hash Cond: (yankee_charlie.oscar_india = five_quebec.six_xray)
-> Parallel Hash Left Join (cost=33196883.64..321393345.56 rows=40818763 width=6813)
Hash Cond: (yankee_charlie.bravo_tango = xray_quebec.kilo_four)
-> Parallel Hash Left Join (cost=29850433.79..255866124.43 rows=40818763 width=6125)
Hash Cond: (zulu_four.mike_four = kilo_lima.lima_uniform)
-> Hash Left Join (cost=27572665.00..192250116.73 rows=40818763 width=6070)
Hash Cond: (yankee_charlie.zulu_oscar = hotel.bravo_hotel)
-> Parallel Hash Left Join (cost=27571519.35..192141780.40 rows=40818763 width=6042)
Hash Cond: (yankee_charlie.alpha_four = uniform_delta.papa_mike)
-> Parallel Hash Join (cost=27569303.10..192032398.49 rows=40818763 width=5775)
Hash Cond: (yankee_charlie.bravo_tango = foxtrot_whiskey_bravo2.kilo_four)
-> Parallel Hash Left Join (cost=3696445.91..128666530.60 rows=40818763 width=2497)
Hash Cond: (yankee_charlie.sierra_six = kilo_foxtrot.november_juliet)
-> Parallel Hash Left Join (cost=3550202.36..106708533.27 rows=40818763 width=2147)
Hash Cond: (yankee_charlie.xray_alpha = foxtrot_whiskey_two.tango_quebec)
-> Parallel Hash Left Join (cost=1366012.90..84660500.52 rows=40818763 width=1926)
Hash Cond: (yankee_charlie.uniform_india = zulu_four.seven_bravo)
-> Parallel Hash Left Join (cost=3031.30..65010702.64 rows=40818763 width=1781)
Hash Cond: (yankee_charlie.golf = india_five.tango_mike)
-> Parallel Append (cost=0.12..64900513.56 rows=40818780 width=1835)
Subplans Removed: 25
-> Parallel Index Scan using november_mike on quebec_victor yankee_charlie (cost=0.12..8.15 rows=1 width=10798)
Filter: ((romeo_xray_two >= (CURRENT_DATE - 'uniform_tango'::interval)) AND (romeo_xray_two < (papa_five('quebec_four'::text, (CURRENT_DATE 'foxtrot_juliet'::interval)) - 'seven_oscar'::interval)))
-> Parallel Index Scan using alpha_six on xray_foxtrot romeo_oscar (cost=0.12..8.15 rows=1 width=10798)
Filter: ((romeo_xray_two >= (CURRENT_DATE - 'uniform_tango'::interval)) AND (romeo_xray_two < (papa_five('quebec_four'::text, (CURRENT_DATE 'foxtrot_juliet'::interval)) - 'seven_oscar'::interval)))
-> Parallel Index Scan using whiskey_three on yankee_bravo charlie (cost=0.12..8.15 rows=1 width=10798)
Filter: ((romeo_xray_two >= (CURRENT_DATE - 'uniform_tango'::interval)) AND (romeo_xray_two < (papa_five('quebec_four'::text, (CURRENT_DATE 'foxtrot_juliet'::interval)) - 'seven_oscar'::interval)))
-> Parallel Index Scan using quebec_india on five_yankee quebec_foxtrot (cost=0.12..8.15 rows=1 width=10798)
Filter: ((romeo_xray_two >= (CURRENT_DATE - 'uniform_tango'::interval)) AND (romeo_xray_two < (papa_five('quebec_four'::text, (CURRENT_DATE 'foxtrot_juliet'::interval)) - 'seven_oscar'::interval)))
-> Parallel Seq Scan on romeo_zulu oscar_seven (cost=0.00..48358699.80 rows=30351009 width=1975)
Filter: ((romeo_xray_two >= (CURRENT_DATE - 'uniform_tango'::interval)) AND (romeo_xray_two < (papa_five('quebec_four'::text, (CURRENT_DATE 'foxtrot_juliet'::interval)) - 'seven_oscar'::interval)))
-> Parallel Seq Scan on xray_seven five_golf (cost=0.00..16337483.57 rows=10467742 width=1428)
Filter: ((romeo_xray_two >= (CURRENT_DATE - 'uniform_tango'::interval)) AND (romeo_xray_two < (papa_five('quebec_four'::text, (CURRENT_DATE 'foxtrot_juliet'::interval)) - 'seven_oscar'::interval)))
-> Parallel Hash (cost=2643.30..2643.30 rows=31030 width=76)
-> Parallel Seq Scan on papa_whiskey india_five (cost=0.00..2643.30 rows=31030 width=76)
-> Parallel Hash (cost=1131848.93..1131848.93 rows=5529893 width=210)
-> Parallel Seq Scan on seven_yankee zulu_four (cost=0.00..1131848.93 rows=5529893 width=210)
-> Parallel Hash (cost=1797395.54..1797395.54 rows=6623354 width=351)
-> Parallel Seq Scan on romeo_golf foxtrot_whiskey_two (cost=0.00..1797395.54 rows=6623354 width=351)
-> Parallel Hash (cost=122037.80..122037.80 rows=365580 width=415)
-> Parallel Seq Scan on sierra_delta kilo_foxtrot (cost=0.00..122037.80 rows=365580 width=415)
-> Parallel Hash (cost=10995540.28..10995540.28 rows=30397353 width=3343)
-> Parallel Append (cost=0.00..10995540.28 rows=30397353 width=3343)
-> Parallel Seq Scan on kilo_november lima_five (cost=0.00..8362872.25 rows=22396025 width=3681)
-> Parallel Seq Scan on five_hotel foxtrot_oscar (cost=0.00..2480681.27 rows=8001327 width=2398)
-> Parallel Seq Scan on juliet_juliet juliet_alpha (cost=0.00..0.00 rows=1 width=7566)
-> Parallel Hash (cost=2012.78..2012.78 rows=16278 width=332)
-> Parallel Seq Scan on xray_bravo uniform_delta (cost=0.00..2012.78 rows=16278 width=332)
-> Hash (cost=1008.62..1008.62 rows=10962 width=158)
-> Seq Scan on lima_romeo hotel (cost=0.00..1008.62 rows=10962 width=158)
-> Parallel Hash (cost=2112348.26..2112348.26 rows=4256042 width=185)
-> Hash Left Join (cost=671798.94..2112348.26 rows=4256042 width=185)
Hash Cond: (kilo_lima.zulu_three = four_hotel.whiskey_victor_bravo)
-> Parallel Hash Left Join (cost=670990.36..2100358.89 rows=4256042 width=188)
Hash Cond: (kilo_lima.lima_uniform = romeo_xray_echo.lima_uniform)
-> Parallel Seq Scan on quebec_november kilo_lima (cost=0.00..1156375.42 rows=4256042 width=176)
-> Parallel Hash (cost=563811.27..563811.27 rows=4253927 width=77)
-> Parallel Seq Scan on tango_romeo romeo_xray_echo (cost=0.00..563811.27 rows=4253927 width=77)
-> Hash (cost=740.48..740.48 rows=5448 width=127)
-> Seq Scan on seven_three four_hotel (cost=0.00..740.48 rows=5448 width=127)
-> Parallel Hash (cost=2480681.27..2480681.27 rows=8001327 width=753)
-> Parallel Seq Scan on five_hotel xray_quebec (cost=0.00..2480681.27 rows=8001327 width=753)
-> Hash (cost=128.96..128.96 rows=1796 width=180)
-> Seq Scan on juliet_yankee five_quebec (cost=0.00..128.96 rows=1796 width=180)
-> Hash (cost=573.85..573.85 rows=8385 width=270)
-> Seq Scan on romeo_two delta_mike (cost=0.00..573.85 rows=8385 width=270)
-> Hash (cost=56.60..56.60 rows=560 width=183)
-> Seq Scan on delta_lima six_alpha (cost=0.00..56.60 rows=560 width=183)
-> Parallel Hash (cost=122037.80..122037.80 rows=365580 width=118)
-> Parallel Seq Scan on sierra_delta mike_foxtrot (cost=0.00..122037.80 rows=365580 width=118)
-> Parallel Hash (cost=122037.80..122037.80 rows=365580 width=118)
-> Parallel Seq Scan on sierra_delta india_three (cost=0.00..122037.80 rows=365580 width=118)
Комментарии:
1. «Я мог видеть, как он генерирует около 500 ГБ данных временного файла» Вы имеете в виду, что вы действительно можете наблюдать, как он генерирует столько, или что вы можете представить, как он это делает?
2. Вы можете запретить хэш-соединения, выполнив
set enable_hashjoin = offно все, что он делает вместо хэш-соединения, вероятно, будет еще хуже.3. Вы выбираете сотни столбцов из каждой таблицы в десятиходовом соединении? Или большинство из них остаются невыбранными в любом данном соединении?
4. @jjanes — Я видел, что он сгенерировал временный файл объемом 500 ГБ, да, как вы сказали, запрет хэша усугубил ситуацию — вложенный цикл и объединение слиянием были слишком дорогими, чем хэш. Что касается количества столбцов, да, извлекается довольно много столбцов для каждой таблицы.
5. @a_horse_with_no_name добавил запрос и план выполнения.
Ответ №1:
Запрос заработал, выделив большее пространство work_mem (16 ГБ) и parallel = 4, на сервере достаточно места (400 ГБ) для поддержки сеанса с большим объемом памяти. Из-за этого запрос не передавался во временные файлы и выполнялся намного быстрее.