#postgresql
#postgresql
Вопрос:
У меня есть вызываемое представление vProductScoreDetail , которое вычисляет индивидуальную оценку трех показателей, которые были применены к каждому продукту. Он содержит множество case и другие операторы для вычисления каждой оценки, но он достаточно быстр, возвращая запросы менее чем за секунду.
Теперь я хочу суммировать эти три подробные оценки в «общую» оценку, и теперь у меня возникают проблемы с производительностью (15 секунд для возврата общих оценок). Для этого я использовал вложенное представление. Мой vProductScoreOverall взгляд выглядит так:
CREATE OR REPLACE VIEW public."vProductScoreOverall"
AS
SELECT vpsd."userId",
vpsd.type,
vpsd."productId",
sum(vpsd."scoreWeighted")::integer AS "scoreWeighted",
sum(vpsd."excludeCount")::integer AS "excludeCount",
CASE
WHEN count(DISTINCT vpsd."refScoreMetricCode") >= 3 THEN true
ELSE false
END AS "isDataComplete"
FROM "vProductScoreDetail" vpsd
WHERE 1 = 1
GROUP BY vpsd."userId", vpsd.type, vpsd."productId";
Я попытался вставить вложенный подзапрос вместо вложенных представлений, чтобы планировщик имел видимость, но время запроса было таким же. Я также не могу реализовать какие-либо представления, поскольку показатели могут быть динамическими для каждого пользователя, поэтому их необходимо генерировать динамически. Любые советы о том, что я мог бы сделать, чтобы оптимизировать эту итоговую общую сумму баллов из подробных оценок?
Вот результат explain analyse, который поможет мне оптимизировать работу… explain analyze select * from "vProductScoreOverall" vpsd limit 1000
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=965124.03..968461.23 rows=1000 width=77) (actual time=27617.730..27642.267 rows=1000 loops=1)
-> GroupAggregate (cost=965124.03..1657936.27 rows=208227 width=77) (actual time=26130.662..26152.508 rows=1000 loops=1)
Group Key: up."userId", (CASE WHEN (up."userId" = 'default'::text) THEN 'default'::text ELSE 'personal'::text END), p.id
-> Sort (cost=965124.03..971745.65 rows=2648645 width=164) (actual time=26130.577..26135.090 rows=1233 loops=1)
Sort Key: up."userId", (CASE WHEN (up."userId" = 'default'::text) THEN 'default'::text ELSE 'personal'::text END), p.id
Sort Method: external merge Disk: 278520kB
-> Nested Loop (cost=34865.56..248009.68 rows=2648645 width=164) (actual time=1215.785..19755.806 rows=882456 loops=1)
-> Seq Scan on "userPref" up (cost=0.00..1.04 rows=1 width=64) (actual time=0.032..0.052 rows=3 loops=1)
Filter: ("refUserPrefTypeCode" = 'scoring_metric'::text)
-> Hash Right Join (cost=34865.56..214900.58 rows=2648645 width=68) (actual time=1063.990..5626.202 rows=294152 loops=3)
Hash Cond: (vpsm."productId" = p.id)
-> Subquery Scan on vpsm (cost=24780.45..134970.64 rows=2648645 width=68) (actual time=148.073..3238.083 rows=281032 loops=3)
-> Append (cost=24780.45..108484.19 rows=2648645 width=260) (actual time=148.069..2468.913 rows=281032 loops=3)
CTE parents
-> Recursive Union (cost=0.00..6278.35 rows=37699 width=170) (actual time=0.028..17.852 rows=714 loops=1)
-> Seq Scan on company (cost=0.00..72.15 rows=589 width=141) (actual time=0.019..3.643 rows=589 loops=1)
Filter: ("parentCompanyId" IS NULL)
Rows Removed by Filter: 126
-> Hash Join (cost=81.09..545.22 rows=3711 width=170) (actual time=3.015..5.612 rows=62 loops=2)
Hash Cond: (p_2.id = c."parentCompanyId")
-> WorkTable Scan on parents p_2 (cost=0.00..117.80 rows=5890 width=40) (actual time=0.003..0.543 rows=357 loops=2)
-> Hash (cost=72.15..72.15 rows=715 width=141) (actual time=5.564..5.566 rows=126 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 19kB
-> Seq Scan on company c (cost=0.00..72.15 rows=715 width=141) (actual time=0.019..1.397 rows=715 loops=1)
-> Hash Join (cost=18502.10..59678.16 rows=2449682 width=265) (actual time=148.065..1371.192 rows=82069 loops=3)
Hash Cond: (prod."brandId" = b.id)
-> Seq Scan on product prod (cost=0.00..6668.27 rows=208227 width=8) (actual time=0.017..413.030 rows=207027 loops=3)
-> Hash (cost=11597.11..11597.11 rows=173279 width=193) (actual time=135.930..135.930 rows=2027 loops=3)
Buckets: 32768 Batches: 16 Memory Usage: 276kB
-> Merge Join (cost=8830.03..11597.11 rows=173279 width=193) (actual time=46.101..71.929 rows=2027 loops=3)
Merge Cond: (b."companyId" = p_1.id)
-> Sort (cost=1602.01..1638.83 rows=14729 width=8) (actual time=37.011..41.318 rows=2028 loops=3)
Sort Key: b."companyId"
Sort Method: quicksort Memory: 1075kB
-> Seq Scan on brand b (cost=0.00..582.29 rows=14729 width=8) (actual time=0.017..56.425 rows=14729 loops=1)
-> Materialize (cost=7228.03..7416.52 rows=37699 width=193) (actual time=9.072..14.685 rows=2298 loops=3)
-> Sort (cost=7228.03..7322.27 rows=37699 width=193) (actual time=27.203..28.287 rows=712 loops=1)
Sort Key: p_1.id
Sort Method: quicksort Memory: 80kB
-> CTE Scan on parents p_1 (cost=0.00..753.98 rows=37699 width=193) (actual time=0.037..25.772 rows=714 loops=1)
-> Seq Scan on "productScoreMetric" (cost=0.00..4787.63 rows=198963 width=196) (actual time=0.023..375.273 rows=198963 loops=3)
-> Hash (cost=6668.27..6668.27 rows=208227 width=4) (actual time=913.104..913.104 rows=207027 loops=3)
Buckets: 131072 Batches: 4 Memory Usage: 2840kB
-> Seq Scan on product p (cost=0.00..6668.27 rows=208227 width=4) (actual time=0.017..444.154 rows=207027 loops=3)
Planning Time: 1.257 ms
JIT:
Functions: 55
Options: Inlining true, Optimization true, Expressions true, Deforming true
Timing: Generation 10.312 ms, Inlining 20.160 ms, Optimization 903.558 ms, Emission 562.204 ms, Total 1496.233 ms
Execution Time: 33422.479 ms
РЕДАКТИРОВАТЬ: Спасибо за комментарии, я попробую их все, начиная с увеличения объема памяти, и посмотрю, имеет ли это значение. Для полной картины, вот vProductScoreDetail представление / запрос
CREATE OR REPLACE VIEW public."vProductScoreDetail"
AS SELECT up."userId",
CASE
WHEN up."userId" = 'default'::text THEN 'default'::text
ELSE 'personal'::text
END AS type,
up."refUserPrefTypeCode",
p.id AS "productId",
up.data,
vpsm."refScoreMetricCode",
vpsm.country,
vpsm.percentage,
vpsm."companyId",
vpsm."desc",
vpsm."statusModeration",
CASE
WHEN vpsm."refScoreMetricCode" = 'made'::text THEN
CASE
WHEN (((up.data -> 'made'::text) ->> 'target'::text)::jsonb) @> concat('["', vpsm.country, '"]')::jsonb THEN (100::numeric * (((up.data -> 'made'::text) ->> 'weight'::text)::numeric))::integer
WHEN (((up.data -> 'made'::text) ->> 'exclude'::text)::jsonb) @> concat('["', vpsm.country, '"]')::jsonb THEN 0
ELSE 0
END
WHEN vpsm."refScoreMetricCode" = 'own'::text THEN
CASE
WHEN (((up.data -> 'own'::text) ->> 'target'::text)::jsonb) @> concat('["', vpsm.country, '"]')::jsonb THEN (100::numeric * (((up.data -> 'own'::text) ->> 'weight'::text)::numeric))::integer
WHEN (((up.data -> 'own'::text) ->> 'exclude'::text)::jsonb) @> concat('["', vpsm.country, '"]')::jsonb THEN 0
ELSE 0
END
WHEN vpsm."refScoreMetricCode" = 'ing'::text THEN
CASE
WHEN (((up.data -> 'ing'::text) ->> 'target'::text)::jsonb) @> concat('["', vpsm.country, '"]')::jsonb THEN (vpsm.percentage::numeric * (((up.data -> 'ing'::text) ->> 'weight'::text)::numeric))::integer
WHEN (((up.data -> 'ing'::text) ->> 'exclude'::text)::jsonb) @> concat('["', vpsm.country, '"]')::jsonb THEN 0
ELSE 0
END
ELSE NULL::integer
END AS "scoreWeighted",
CASE
WHEN vpsm."refScoreMetricCode" = 'made'::text THEN
CASE
WHEN (((up.data -> 'made'::text) ->> 'exclude'::text)::jsonb) @> concat('["', vpsm.country, '"]')::jsonb THEN 1
ELSE 0
END
WHEN vpsm."refScoreMetricCode" = 'own'::text THEN
CASE
WHEN (((up.data -> 'own'::text) ->> 'exclude'::text)::jsonb) @> concat('["', vpsm.country, '"]')::jsonb THEN 1
ELSE 0
END
WHEN vpsm."refScoreMetricCode" = 'ing'::text THEN
CASE
WHEN (((up.data -> 'ing'::text) ->> 'exclude'::text)::jsonb) @> concat('["', vpsm.country, '"]')::jsonb THEN 1
ELSE 0
END
ELSE NULL::integer
END AS "excludeCount",
CASE
WHEN vpsm."refScoreMetricCode" = 'made'::text THEN
CASE
WHEN (((up.data -> 'made'::text) ->> 'target'::text)::jsonb) @> concat('["', vpsm.country, '"]')::jsonb THEN 'pass'::text
WHEN (((up.data -> 'made'::text) ->> 'exclude'::text)::jsonb) @> concat('["', vpsm.country, '"]')::jsonb THEN 'fail'::text
ELSE 'neutral'::text
END
WHEN vpsm."refScoreMetricCode" = 'own'::text THEN
CASE
WHEN (((up.data -> 'own'::text) ->> 'target'::text)::jsonb) @> concat('["', vpsm.country, '"]')::jsonb THEN 'pass'::text
WHEN (((up.data -> 'own'::text) ->> 'exclude'::text)::jsonb) @> concat('["', vpsm.country, '"]')::jsonb THEN 'fail'::text
ELSE 'neutral'::text
END
WHEN vpsm."refScoreMetricCode" = 'ing'::text THEN
CASE
WHEN (((up.data -> 'ing'::text) ->> 'target'::text)::jsonb) @> concat('["', vpsm.country, '"]')::jsonb THEN 'pass'::text
WHEN (((up.data -> 'ing'::text) ->> 'exclude'::text)::jsonb) @> concat('["', vpsm.country, '"]')::jsonb THEN 'fail'::text
ELSE 'neutral'::text
END
ELSE NULL::text
END AS status
FROM "userPref" up
CROSS JOIN product p
LEFT JOIN "vProductScoreMetric" vpsm ON p.id = vpsm."productId"
WHERE 1 = 1 AND up."refUserPrefTypeCode" = 'scoring_metric'::text;
и explain analyze select * from "vProductScoreDetail" vpsd limit 1000
QUERY PLAN |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
Limit (cost=34865.56..35331.66 rows=1000 width=303) (actual time=1341.507..1520.844 rows=1000 loops=1) |
-> Nested Loop (cost=34865.56..1269394.33 rows=2648645 width=303) (actual time=1341.501..1518.261 rows=1000 loops=1) |
-> Seq Scan on "userPref" up (cost=0.00..1.04 rows=1 width=96) (actual time=0.027..0.028 rows=1 loops=1) |
Filter: ("refUserPrefTypeCode" = 'scoring_metric'::text) |
-> Hash Right Join (cost=34865.56..256286.58 rows=2648645 width=135) (actual time=1341.298..1505.238 rows=1000 loops=1) |
Hash Cond: (vpsm."productId" = p.id) |
-> Subquery Scan on vpsm (cost=24780.45..134970.64 rows=2648645 width=135) (actual time=163.108..316.151 rows=3922 loops=1) |
-> Append (cost=24780.45..108484.19 rows=2648645 width=260) (actual time=163.103..305.665 rows=3922 loops=1) |
CTE parents |
-> Recursive Union (cost=0.00..6278.35 rows=37699 width=170) (actual time=0.051..10.874 rows=714 loops=1) |
-> Seq Scan on company (cost=0.00..72.15 rows=589 width=141) (actual time=0.024..1.758 rows=589 loops=1) |
Filter: ("parentCompanyId" IS NULL) |
Rows Removed by Filter: 126 |
-> Hash Join (cost=81.09..545.22 rows=3711 width=170) (actual time=1.720..2.892 rows=62 loops=2) |
Hash Cond: (p_2.id = c."parentCompanyId") |
-> WorkTable Scan on parents p_2 (cost=0.00..117.80 rows=5890 width=40) (actual time=0.003..0.581 rows=357 loops=2) |
-> Hash (cost=72.15..72.15 rows=715 width=141) (actual time=2.892..2.893 rows=126 loops=1) |
Buckets: 1024 Batches: 1 Memory Usage: 19kB |
-> Seq Scan on company c (cost=0.00..72.15 rows=715 width=141) (actual time=0.011..1.658 rows=715 loops=1) |
-> Hash Join (cost=18502.10..59678.16 rows=2449682 width=265) (actual time=163.099..295.986 rows=3922 loops=1) |
Hash Cond: (prod."brandId" = b.id) |
-> Seq Scan on product prod (cost=0.00..6668.27 rows=208227 width=8) (actual time=0.144..67.242 rows=30065 loops=1) |
-> Hash (cost=11597.11..11597.11 rows=173279 width=193) (actual time=149.571..149.572 rows=2027 loops=1) |
Buckets: 32768 Batches: 16 Memory Usage: 276kB |
-> Merge Join (cost=8830.03..11597.11 rows=173279 width=193) (actual time=112.062..132.766 rows=2027 loops=1) |
Merge Cond: (b."companyId" = p_1.id) |
-> Sort (cost=1602.01..1638.83 rows=14729 width=8) (actual time=95.741..98.830 rows=2028 loops=1) |
Sort Key: b."companyId" |
Sort Method: quicksort Memory: 1075kB |
-> Seq Scan on brand b (cost=0.00..582.29 rows=14729 width=8) (actual time=0.025..49.058 rows=14729 loops=1) |
-> Materialize (cost=7228.03..7416.52 rows=37699 width=193) (actual time=16.308..23.359 rows=2298 loops=1) |
-> Sort (cost=7228.03..7322.27 rows=37699 width=193) (actual time=16.300..17.372 rows=712 loops=1) |
Sort Key: p_1.id |
Sort Method: quicksort Memory: 80kB |
-> CTE Scan on parents p_1 (cost=0.00..753.98 rows=37699 width=193) (actual time=0.058..14.438 rows=714 loops=1)|
-> Seq Scan on "productScoreMetric" (cost=0.00..4787.63 rows=198963 width=196) (never executed) |
-> Hash (cost=6668.27..6668.27 rows=208227 width=4) (actual time=1174.682..1174.683 rows=207027 loops=1) |
Buckets: 131072 Batches: 4 Memory Usage: 2840kB |
-> Seq Scan on product p (cost=0.00..6668.27 rows=208227 width=4) (actual time=0.471..446.470 rows=207027 loops=1) |
Planning Time: 32.678 ms |
Execution Time: 1706.232 ms |
EDIT2: у меня есть
- изменил значение
left joinнаjoininvProductScoreDetail, и это значительно ускорило переход к этому представлению, это сделалоvProductScoreOverallпросмотр быстрее, но не намного - увеличено
work_memдо 400 МБ — я не видел никакого ускорения и, глядя наexplain analyzeто, что он все еще переключается на диск для сортировки, которая занимает 20 секунд. Я проверил, что work_mem составляет 400 МБselect name, setting, unit, source from pg_settings where name = 'work_mem';. Любые другие подсказки для принудительной сортировки в памяти?
Комментарии:
1.
Sort Method: external merge Disk: 278520kBэто то, что вас убивает. Сортировка 278 МБ данных на диске, а не в памяти. Попробуйте увеличить вашиwork_memдополнительные 300 МБ или около того?2. На самом деле я не вижу никакого очевидного места, где индекс мог бы помочь здесь, потому что в вашем запросе нет
WHEREHAVINGпредложений or, и он не используетMAXorMIN.3. Не имеет отношения к вашей проблеме, но: вам действительно следует избегать этих страшных идентификаторов в кавычках. Они доставляют гораздо больше хлопот, чем того стоят. wiki.postgresql.org/wiki /…
4. Это не окажет никакого влияния на производительность, но ваше выражение CASE можно упростить до:
count(DISTINCT vpsd."refScoreMetricCode") >= 3 as "isDataComplete"5. Вы можете попытаться отключить jit:
set jit=offперед его запуском, но, похоже, это не имеет большого значения