#postgresql #amazon-rds
#postgresql #amazon-rds
Вопрос:
Я пытаюсь настроить сложный SQL-запрос (не могу опубликовать здесь из-за политики организации). При просмотре плана запроса я обнаружил, что огромное значение «Сканирования кучи» увеличивает время выполнения запроса и использует почти все ресурсы, доступные в RDS.
Я хотел бы знать, как я могу снизить его до приемлемого значения?
Aggregate (cost=19967.440..19967.450 rows=1 width=32) (actual time=356827.833..356827.834 rows=1 loops=1)
-> Hash Left Join (cost=7783.550..19774.270 rows=765 width=80) (actual time=1396.970..356657.528 rows=1357 loops=1)
Hash Cond: (alpha_quebec.juliet_victor = kilo_sierra.quebec_seven)
-> Hash Left Join (cost=7773.230..19760.700 rows=765 width=104) (actual time=1396.946..356647.666 rows=1357 loops=1)
Hash Cond: (alpha_two.quebec_seven = mike_lima.hotel)
-> Hash Left Join (cost=6407.130..18391.610 rows=765 width=128) (actual time=555.112..355801.185 rows=1357 loops=1)
Hash Cond: (alpha_quebec.quebec_seven = six_kilo.hotel)
-> Nested Loop Left Join (cost=5041.040..17022.640 rows=765 width=152) (actual time=90.547..355332.942 rows=1357 loops=1)
-> Nested Loop Left Join (cost=3672.570..13310.410 rows=765 width=168) (actual time=90.527..1692.694 rows=1357 loops=1)
-> Hash Left Join (cost=3672.290..9707.350 rows=765 width=160) (actual time=89.430..648.944 rows=1357 loops=1)
Hash Cond: (sierra(alpha_quebec.bravo) = three.quebec_seven)
-> Hash Left Join (cost=3671.250..9607.780 rows=765 width=184) (actual time=88.918..643.314 rows=1357 loops=1)
Hash Cond: (alpha_quebec.foxtrot_sierra = romeo_lima.quebec_seven)
-> Hash Left Join (cost=2666.800..8596.630 rows=765 width=208) (actual time=64.133..616.054 rows=1357 loops=1)
Hash Cond: (alpha_quebec.quebec_seven = yankee_tango_six.hotel)
-> Hash Left Join (cost=1561.140..7469.930 rows=765 width=208) (actual time=59.295..604.672 rows=1357 loops=1)
Hash Cond: (alpha_quebec.quebec_seven = zulu_four.hotel)
-> Hash Left Join (cost=454.600..6360.510 rows=765 width=208) (actual time=29.880..571.878 rows=1357 loops=1)
Hash Cond: (sierra(alpha_quebec.foxtrot_yankee) = delta_whiskey.quebec_seven)
-> Bitmap Heap Scan on zulu_charlie alpha_quebec (cost=191.900..5995.490 rows=765 width=232) (actual time=19.030..527.055 rows=1357 loops=1)
Recheck Cond: ((quebec_seven >= 'seven'::bytea) AND (quebec_seven <= 'mike_four'::bytea))
Filter: (NOT whiskey_yankee)
Heap Blocks: exact=804
-> Bitmap Index Scan on six_india (cost=0.000..191.710 rows=1529 width=0) (actual time=17.534..17.534 rows=1357 loops=1)
Index Cond: ((quebec_seven >= 'seven'::bytea) AND (quebec_seven <= 'mike_four'::bytea))
-> Hash (cost=249.350..249.350 rows=1068 width=40) (actual time=6.931..6.931 rows=2153 loops=1)
Buckets: 4096 (originally 2048) Batches: 1 (originally 1) Memory Usage: 152kB
-> Seq Scan on two_oscar delta_whiskey (cost=0.000..249.350 rows=1068 width=40) (actual time=0.844..5.628 rows=2153 loops=1)
Filter: (NOT whiskey_yankee)
-> Hash (cost=1104.370..1104.370 rows=173 width=32) (actual time=29.408..29.409 rows=281 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 22kB
-> Subquery Scan on zulu_four (cost=1100.050..1104.370 rows=173 width=32) (actual time=28.947..29.296 rows=281 loops=1)
-> GroupAggregate (cost=1100.050..1102.640 rows=173 width=96) (actual time=28.945..29.203 rows=281 loops=1)
Group Key: juliet_xray.hotel
-> Sort (cost=1100.050..1100.480 rows=173 width=32) (actual time=28.937..28.992 rows=281 loops=1)
Sort Key: juliet_xray.hotel
Sort Method: quicksort Memory: 46kB
-> Hash Join (cost=7.430..1093.620 rows=173 width=32) (actual time=9.474..28.581 rows=281 loops=1)
Hash Cond: (juliet_xray.romeo_zulu = romeo_three.quebec_seven)
-> Seq Scan on charlie juliet_xray (cost=0.000..1081.500 rows=788 width=64) (actual time=6.817..25.627 rows=281 loops=1)
Filter: ((NOT whiskey_yankee) AND zulu_uniform)
Rows Removed by Filter: 11474
-> Hash (cost=6.880..6.880 rows=44 width=32) (actual time=2.629..2.629 rows=88 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 13kB
-> Seq Scan on lima romeo_three (cost=0.000..6.880 rows=44 width=32) (actual time=0.840..2.569 rows=88 loops=1)
Filter: (NOT whiskey_yankee)
-> Hash (cost=1103.160..1103.160 rows=200 width=32) (actual time=4.822..4.822 rows=1305 loops=1)
Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 79kB
-> Subquery Scan on yankee_tango_six (cost=1099.160..1103.160 rows=200 width=32) (actual time=3.490..4.413 rows=1306 loops=1)
-> HashAggregate (cost=1099.160..1101.160 rows=200 width=96) (actual time=3.489..3.893 rows=1306 loops=1)
Group Key: india1.hotel
-> Hash Right Join (cost=7.430..1098.300 rows=346 width=32) (actual time=0.086..2.867 rows=1391 loops=1)
Hash Cond: (india1.romeo_zulu = six_lima1.quebec_seven)
-> Seq Scan on charlie quebec_oscar_zulu_seven (cost=0.000..1081.500 rows=1575 width=64) (actual time=0.003..2.086 rows=1306 loops=1)
Filter: (NOT whiskey_yankee)
Rows Removed by Filter: 10449
-> Hash (cost=6.880..6.880 rows=44 width=32) (actual time=0.067..0.067 rows=88 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 13kB
-> Seq Scan on lima victor_echo (cost=0.000..6.880 rows=44 width=32) (actual time=0.005..0.039 rows=88 loops=1)
Filter: (NOT whiskey_yankee)
-> Hash (cost=847.740..847.740 rows=12537 width=40) (actual time=24.720..24.720 rows=23917 loops=1)
Buckets: 32768 (originally 16384) Batches: 1 (originally 1) Memory Usage: 1588kB
-> Seq Scan on delta_juliet romeo_lima (cost=0.000..847.740 rows=12537 width=40) (actual time=1.960..15.639 rows=23917 loops=1)
Filter: (NOT whiskey_yankee)
-> Hash (cost=1.020..1.020 rows=1 width=40) (actual time=0.481..0.481 rows=1 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on whiskey_november three (cost=0.000..1.020 rows=1 width=40) (actual time=0.478..0.479 rows=1 loops=1)
Filter: ((NOT whiskey_yankee) AND ((two_charlie)::text = 'victor_lima'::text))
Rows Removed by Filter: 1
-> Index Scan using golf_sierra on quebec_oscar_zulu_papa alpha_two (cost=0.290..4.700 rows=1 width=40) (actual time=0.761..0.765 rows=1 loops=1357)
Index Cond: (quebec_seven = alpha_quebec.tango_yankee)
Filter: (NOT whiskey_yankee)
-> Hash Right Join (cost=1368.460..1371.520 rows=1 width=48) (actual time=218.485..260.014 rows=0 loops=1357)
Hash Cond: (yankee_zulu.hotel = victor_hotel.quebec_seven)
-> Unique (cost=1361.850..1363.010 rows=137 width=7905) (actual time=450.463..644.573 rows=64383 loops=491)
-> Sort (cost=1361.850..1362.430 rows=232 width=7905) (actual time=450.461..524.690 rows=64383 loops=491)
Sort Key: yankee_zulu.hotel
-> Bitmap Heap Scan on yankee_zulu (cost=39.950..1352.740 rows=232 width=7905) (actual time=25.050..203.538 rows=64383 loops=491)
Recheck Cond: ((kilo_two)::text = 'mike_papa'::text)
Filter: (NOT whiskey_yankee)
Heap Blocks: exact=1656634
-> Bitmap Index Scan on four (cost=0.000..39.890 rows=463 width=0) (actual time=23.773..23.773 rows=64383 loops=491)
Index Cond: ((kilo_two)::text = 'mike_papa'::text)
-> Hash (cost=6.600..6.600 rows=1 width=40) (actual time=1.350..1.350 rows=0 loops=1357)
Buckets: 1024 Batches: 1 Memory Usage: 8kB
-> Index Scan using november on xray victor_hotel (cost=0.660..6.600 rows=1 width=40) (actual time=1.340..1.341 rows=0 loops=1357)
Index Cond: (sierra(alpha_quebec.kilo_kilo) = quebec_seven)
Filter: (NOT whiskey_yankee)
Rows Removed by Filter: 0
-> Hash (cost=1364.380..1364.380 rows=137 width=40) (actual time=464.550..464.551 rows=31262 loops=1)
Buckets: 32768 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1997kB
-> Subquery Scan on six_kilo (cost=1361.850..1364.380 rows=137 width=40) (actual time=398.701..450.745 rows=31262 loops=1)
-> Unique (cost=1361.850..1363.010 rows=137 width=7905) (actual time=398.700..432.354 rows=31262 loops=1)
-> Sort (cost=1361.850..1362.430 rows=232 width=7905) (actual time=398.695..412.188 rows=31347 loops=1)
Sort Key: five1.hotel
Sort Method: quicksort Memory: 3217kB
-> Bitmap Heap Scan on yankee_zulu victor_zulu (cost=39.950..1352.740 rows=232 width=7905) (actual time=170.383..371.245 rows=31347 loops=1)
Recheck Cond: ((kilo_two)::text = 'kilo_six'::text)
Filter: (NOT whiskey_yankee)
Heap Blocks: exact=1272
-> Bitmap Index Scan on four (cost=0.000..39.890 rows=463 width=0) (actual time=169.794..169.794 rows=31347 loops=1)
Index Cond: ((kilo_two)::text = 'kilo_six'::text)
-> Hash (cost=1364.380..1364.380 rows=137 width=40) (actual time=841.806..841.806 rows=17638 loops=1)
Buckets: 32768 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1238kB
-> Subquery Scan on mike_lima (cost=1361.850..1364.380 rows=137 width=40) (actual time=786.540..830.724 rows=17638 loops=1)
-> Unique (cost=1361.850..1363.010 rows=137 width=7905) (actual time=786.538..815.928 rows=17638 loops=1)
-> Sort (cost=1361.850..1362.430 rows=232 width=7905) (actual time=786.536..797.997 rows=22005 loops=1)
Sort Key: five2.hotel
Sort Method: quicksort Memory: 2488kB
-> Bitmap Heap Scan on yankee_zulu golf_november (cost=39.950..1352.740 rows=232 width=7905) (actual time=75.120..759.345 rows=22005 loops=1)
Recheck Cond: ((kilo_two)::text = 'yankee_tango_papa'::text)
Filter: (NOT whiskey_yankee)
Heap Blocks: exact=1810
-> Bitmap Index Scan on four (cost=0.000..39.890 rows=463 width=0) (actual time=74.894..74.894 rows=22005 loops=1)
Index Cond: ((kilo_two)::text = 'yankee_tango_papa'::text)
-> Hash (cost=10.200..10.200 rows=10 width=40) (actual time=0.007..0.007 rows=0 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 8kB
-> Seq Scan on tango_delta kilo_sierra (cost=0.000..10.200 rows=10 width=40) (actual time=0.007..0.007 rows=0 loops=1)
Filter: (NOT whiskey_yankee)
Некоторые из вещей, которые я пробовал до сих пор:
Vacuum full analyzeSET default_statistics_target = 1000;SELECT * FROM pg_stat_all_tables order by n_tup_del;
Комментарии:
1. У нас мало что есть; возможно, запутанный план выполнения с использованием explain.depesz.com помогло бы. Есть неверная оценка (232 вместо 64383), но невозможно угадать, в чем проблема только с этим одним узлом. Странно, что PostgreSQL должен выполнять одно и то же сканирование индекса 491 раз.
2. Похоже, что ваша таблица диапазонов превышает join_collapse_limit и / или from_collapse_limit и / или geqo_threshold. В результате запускается geqo. Попробуйте переместить небольшие части запроса в CTE (что должно привести к небольшим наборам результатов) и обратитесь к ним
3. @wildplasser, допустим, это была проблема с настройками, тогда то же самое должно происходить везде, но это происходит только в одной базе данных из 1000 идентичных баз данных
4. Хорошо: выбросьте это, у вас все еще будет 999 других! [извините, невозможно ответить на этот вопрос без фактического запроса, DDL, настроек и некоторых соответствующих показателей]
5. Улучшается ли производительность, если вы
SET enable_nestloop = off?
Ответ №1:
Основной проблемой являются плохие неправильные оценки при трех проверках индекса на four с условиями ((kilo_two)::text = 'something'::text) .
Итак, если вы
ALTER TABLE four ALTER kilo_two SET STATISTICS 1000;
(или, может быть, даже более высокие значения) и ANALYZE таблица, план запроса должен улучшиться.
Мы надеемся, что это устранит оскорбительное соединение с хэшем справа, которое так дорого, вероятно, потому, что оно повторяется 491 раз.
Это также помогло бы использовать частичный индекс на four :
CREATE INDEX ON four ((kilo_two::text)) WHERE NOT whiskey_yankee;
Если все остальное не удается, вы можете принудительно выполнить этот запрос без объединения вложенных циклов:
BEGIN;
SET LOCAL enable_nestloop = off;
SELECT /* your query */;
COMMIT;
Комментарии:
1. Я попробовал ваши предложения. Как вы можете видеть, с тех пор планировщик запросов уже использует индекс для сканирования местоположений. Не уверен, почему он выбирает цикл так много раз и почему он выдает такие неточные оценки.
2. Неправильные оценки, вероятно, являются корнем проблемы. Даже с
SET STATISTICS 10000вы не получаете лучших оценок? Странно. Интересно, скрывает ли запутывание что-то. Являются ли значения в индексных сканированиях частыми или редкими значениями? Каков тип данных?3. Я увеличил свой default_statistics_target до 5000, но ничего не изменилось. Они частые, а тип данных — varchar
4. Просто чтобы быть уверенным: вы запускали
ANALYZEтаблицу после изменения, верно? Если да, у меня нет объяснений.5. Я запустил всю базу данных, используя подробный анализ вакуума