Sync vulnerability_statistics info to security_inventory_filters table
What does this MR do and why?
Adds services that sync vulnerability_statistics table with security_inventory_filters table.
Changelog: added
EE: true
Query plans
VulnerabilityStatisticsSyncService
The security_inventory_filters doesn't have any values yet, so the update will not affect any records.
Raw SQL
UPDATE
security_inventory_filters
SET
total = COALESCE(vs.total, 0),
critical = COALESCE(vs.critical, 0),
high = COALESCE(vs.high, 0),
medium = COALESCE(vs.medium, 0),
low = COALESCE(vs.low, 0),
unknown = COALESCE(vs.unknown, 0),
info = COALESCE(vs.info, 0)
FROM (
SELECT
project_ids.project_id,
vs.total,
vs.critical,
vs.high,
vs.medium,
vs.low,
vs.unknown,
vs.info
FROM
unnest(ARRAY[71329491, 68794259, 69782606, 68571417]) AS project_ids (project_id)
LEFT JOIN vulnerability_statistics vs ON vs.project_id = project_ids.project_id) vs
WHERE
security_inventory_filters.project_id = vs.project_idPlan
See full plan here.
ModifyTable on public.security_inventory_filters (cost=0.57..21.98 rows=0 width=0) (actual time=0.033..0.034 rows=0 loops=1)
Buffers: shared hit=11
I/O Timings: read=0.000 write=0.000
-> Nested Loop Left Join (cost=0.57..21.98 rows=4 width=68) (actual time=0.032..0.033 rows=0 loops=1)
Buffers: shared hit=11
I/O Timings: read=0.000 write=0.000
-> Nested Loop (cost=0.15..8.21 rows=4 width=38) (actual time=0.031..0.032 rows=0 loops=1)
Buffers: shared hit=11
I/O Timings: read=0.000 write=0.000
-> Function Scan on unnest project_ids (cost=0.00..0.04 rows=4 width=32) (actual time=0.015..0.016 rows=4 loops=1)
I/O Timings: read=0.000 write=0.000
-> Index Scan using index_security_inventory_filters_on_project_id on public.security_inventory_filters (cost=0.15..2.04 rows=1 width=14) (actual time=0.003..0.003 rows=0 loops=4)
Index Cond: (security_inventory_filters.project_id = project_ids.project_id)
Buffers: shared hit=11
I/O Timings: read=0.000 write=0.000
-> Index Scan using index_vulnerability_statistics_on_unique_project_id on public.vulnerability_statistics vs (cost=0.42..3.44 rows=1 width=42) (actual time=0.000..0.000 rows=0 loops=0)
Index Cond: (vs.project_id = project_ids.project_id)
I/O Timings: read=0.000 write=0.000
Settings: effective_cache_size = '338688MB', jit = 'off', seq_page_cost = '4', work_mem = '100MB', random_page_cost = '1.5'VulnerabilityStatisticsUpdateService
Raw SQL
INSERT INTO security_inventory_filters AS target (project_id, project_name, archived, traversal_ids, "total", "info", "unknown", "low", "medium", "high", "critical")
VALUES (71329491, 'Project One', FALSE, '{9970,7}', 3, 0, 0, 0, 0, 1, 2),
(68794259, 'Project Two', FALSE, '{9970,8}', 5, 1, 1, 1, 1, 2, 3),
(69782606, 'Project Three', TRUE, '{9970,9}', 2, 0, 1, 1, 0, 0, 1),
(68571417, 'Project Four', FALSE, '{9970,10}', 7, 2, 1, 1, 2, 3, 4)
ON CONFLICT (project_id)
DO UPDATE SET
project_name = EXCLUDED.project_name,
traversal_ids = EXCLUDED.traversal_ids,
archived = EXCLUDED.archived,
"total" = GREATEST (TARGET."total" + EXCLUDED."total", 0),
"info" = GREATEST (TARGET."info" + EXCLUDED."info", 0),
"unknown" = GREATEST (TARGET."unknown" + EXCLUDED."unknown", 0),
"low" = GREATEST (TARGET."low" + EXCLUDED."low", 0),
"medium" = GREATEST (TARGET."medium" + EXCLUDED."medium", 0),
"high" = GREATEST (TARGET."high" + EXCLUDED."high", 0),
"critical" = GREATEST (TARGET."critical" + EXCLUDED."critical", 0);
Plan
See full plan here.
ModifyTable on public.security_inventory_filters target (cost=0.00..0.06 rows=0 width=0) (actual time=0.837..0.837 rows=0 loops=1)
Buffers: shared hit=49 read=6 dirtied=6 written=3
WAL: records=19 fpi=0 bytes=1722
I/O Timings: read=0.197 write=0.087
-> Values Scan on "*VALUES*" (cost=0.00..0.06 rows=4 width=137) (actual time=0.282..0.294 rows=4 loops=1)
Buffers: shared hit=13 read=5 dirtied=1
WAL: records=1 fpi=0 bytes=99
I/O Timings: read=0.179 write=0.000
Settings: effective_cache_size = '338688MB', jit = 'off', seq_page_cost = '4', work_mem = '100MB', random_page_cost = '1.5'Statistics::AdjustmentService
Raw SQL
WITH new_values AS (
SELECT
project_ids.project_id AS project_id,
project_attributes.archived AS archived,
project_attributes.traversal_ids AS traversal_ids,
COALESCE(severity_counts.total, 0) AS total,
COALESCE(severity_counts.info, 0) AS info,
COALESCE(severity_counts.unknown, 0) AS unknown,
COALESCE(severity_counts.low, 0) AS low,
COALESCE(severity_counts.medium, 0) AS medium,
COALESCE(severity_counts.high, 0) AS high,
COALESCE(severity_counts.critical, 0) AS critical,
(
CASE WHEN severity_counts.critical > 0 THEN
4
WHEN severity_counts.high > 0
OR severity_counts.unknown > 0 THEN
3
WHEN severity_counts.medium > 0 THEN
2
WHEN severity_counts.low > 0 THEN
1
ELSE
0
END) AS letter_grade,
now() AS created_at,
now() AS updated_at
FROM
unnest(ARRAY[71329491, 68794259, 69782606, 68571417, 66101996]) project_ids (project_id)
JOIN (
VALUES (71329491, FALSE, ARRAY[9970, 96981785, 97058478, 98867842, 100569068]::bigint[]),
(68794259, FALSE, ARRAY[9970, 96981785, 97058478, 98867842, 100569068]::bigint[]),
(69782606, FALSE, ARRAY[9970, 96981785, 97058478, 98867842, 100569068]::bigint[]),
(68571417, FALSE, ARRAY[9970, 96981785, 97058478, 98867842, 100569068]::bigint[]),
(66101996, FALSE, ARRAY[9970, 96981785, 97058478, 98867842, 100569068]::bigint[])) project_attributes (project_id, archived, traversal_ids) ON project_attributes.project_id = project_ids.project_id
LEFT OUTER JOIN (
SELECT
vulnerability_reads.project_id AS project_id, COUNT(*) AS total,
COUNT(*) FILTER (WHERE severity = 1) AS info,
COUNT(*) FILTER (WHERE severity = 2) AS unknown,
COUNT(*) FILTER (WHERE severity = 4) AS low,
COUNT(*) FILTER (WHERE severity = 5) AS medium,
COUNT(*) FILTER (WHERE severity = 6) AS high,
COUNT(*) FILTER (WHERE severity = 7) AS critical
FROM
vulnerability_reads
WHERE
vulnerability_reads.project_id IN (71329491, 68794259, 69782606, 68571417, 66101996)
AND vulnerability_reads.state IN (1, 4)
GROUP BY
vulnerability_reads.project_id) AS severity_counts ON severity_counts.project_id = project_ids.project_id
),
old_values AS (
SELECT
project_id,
traversal_ids,
total,
critical,
high,
medium,
low,
unknown,
info
FROM
vulnerability_statistics
WHERE
project_id IN (
SELECT
project_id
FROM
new_values)
),
upserted AS (
INSERT INTO vulnerability_statistics (project_id, archived, traversal_ids, total, info, unknown, low, medium, high, critical, letter_grade, created_at, updated_at) (
SELECT
project_id,
archived,
traversal_ids,
total,
info,
unknown,
low,
medium,
high,
critical,
letter_grade,
created_at,
updated_at
FROM
new_values)
ON CONFLICT (project_id)
DO UPDATE SET
total = EXCLUDED.total,
info = EXCLUDED.info,
unknown = EXCLUDED.unknown,
low = EXCLUDED.low,
medium = EXCLUDED.medium,
high = EXCLUDED.high,
critical = EXCLUDED.critical,
letter_grade = EXCLUDED.letter_grade,
updated_at = EXCLUDED.updated_at
RETURNING
project_id
),
diff_values AS (
SELECT
new_values.traversal_ids[array_length(new_values.traversal_ids, 1)] AS namespace_id,
new_values.traversal_ids,
ARRAY_AGG(DISTINCT new_values.project_id ORDER BY new_values.project_id) AS affected_project_ids,
SUM(new_values.total - COALESCE(old_values.total, 0)) AS total,
SUM(new_values.info - COALESCE(old_values.info, 0)) AS info,
SUM(new_values.unknown - COALESCE(old_values.unknown, 0)) AS unknown,
SUM(new_values.low - COALESCE(old_values.low, 0)) AS low,
SUM(new_values.medium - COALESCE(old_values.medium, 0)) AS medium,
SUM(new_values.high - COALESCE(old_values.high, 0)) AS high,
SUM(new_values.critical - COALESCE(old_values.critical, 0)) AS critical
FROM
new_values
LEFT JOIN old_values ON new_values.project_id = old_values.project_id
WHERE
EXISTS (
SELECT
1
FROM
upserted
WHERE
upserted.project_id = new_values.project_id)
AND (new_values.total != COALESCE(old_values.total, 0)
OR new_values.info != COALESCE(old_values.info, 0)
OR new_values.unknown != COALESCE(old_values.unknown, 0)
OR new_values.low != COALESCE(old_values.low, 0)
OR new_values.medium != COALESCE(old_values.medium, 0)
OR new_values.high != COALESCE(old_values.high, 0)
OR new_values.critical != COALESCE(old_values.critical, 0))
GROUP BY
namespace_id,
new_values.traversal_ids
)
SELECT
*
FROM
diff_values
WHERE
total != 0
OR info != 0
OR unknown != 0
OR low != 0
OR medium != 0
OR high != 0
OR critical != 0
Plan
See full plan here.
Aggregate (cost=520.54..534.41 rows=146 width=296) (actual time=43.923..43.931 rows=0 loops=1)
Group Key: (new_values.traversal_ids[array_length(new_values.traversal_ids, 1)]), new_values.traversal_ids
Filter: ((sum((new_values.total - COALESCE(vulnerability_statistics.total, 0))) <> '0'::numeric) OR (sum((new_values.info - COALESCE(vulnerability_statistics.info, 0))) <> '0'::numeric) OR (sum((new_values.unknown - COALESCE(vulnerability_statistics.unknown, 0))) <> '0'::numeric) OR (sum((new_values.low - COALESCE(vulnerability_statistics.low, 0))) <> '0'::numeric) OR (sum((new_values.medium - COALESCE(vulnerability_statistics.medium, 0))) <> '0'::numeric) OR (sum((new_values.high - COALESCE(vulnerability_statistics.high, 0))) <> '0'::numeric) OR (sum((new_values.critical - COALESCE(vulnerability_statistics.critical, 0))) <> '0'::numeric))
Rows Removed by Filter: 0
Buffers: shared hit=127 read=61 dirtied=17
WAL: records=26 fpi=15 bytes=69785
I/O Timings: read=41.740 write=0.000
CTE new_values
-> Hash Right Join (cost=464.68..607.56 rows=146 width=113) (actual time=19.247..19.299 rows=5 loops=1)
Hash Cond: (vulnerability_reads.project_id = project_ids.project_id)
Buffers: shared hit=37 read=23 dirtied=1
WAL: records=1 fpi=1 bytes=7901
I/O Timings: read=18.423 write=0.000
-> HashAggregate (cost=464.37..522.84 rows=5847 width=64) (actual time=19.199..19.234 rows=4 loops=1)
Group Key: vulnerability_reads.project_id
Buffers: shared hit=37 read=23 dirtied=1
WAL: records=1 fpi=1 bytes=7901
I/O Timings: read=18.423 write=0.000
-> Index Only Scan using index_vuln_reads_on_project_id_state_severity_and_vuln_id on public.vulnerability_reads (cost=0.57..246.98 rows=6211 width=10) (actual time=4.948..19.169 rows=42 loops=1)
Index Cond: ((vulnerability_reads.project_id = ANY ('{71329491,68794259,69782606,68571417,66101996}'::bigint[])) AND (vulnerability_reads.state = ANY ('{1,4}'::integer[])))
Heap Fetches: 1
Buffers: shared hit=37 read=23 dirtied=1
WAL: records=1 fpi=1 bytes=7901
I/O Timings: read=18.423 write=0.000
-> Hash (cost=0.25..0.25 rows=5 width=37) (actual time=0.030..0.034 rows=5 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
I/O Timings: read=0.000 write=0.000
-> Hash Join (cost=0.12..0.25 rows=5 width=37) (actual time=0.025..0.029 rows=5 loops=1)
Hash Cond: ("*VALUES*".column1 = project_ids.project_id)
I/O Timings: read=0.000 write=0.000
-> Values Scan on "*VALUES*" (cost=0.00..0.06 rows=5 width=37) (actual time=0.001..0.003 rows=5 loops=1)
I/O Timings: read=0.000 write=0.000
-> Hash (cost=0.05..0.05 rows=5 width=4) (actual time=0.010..0.011 rows=5 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
I/O Timings: read=0.000 write=0.000
-> Function Scan on unnest project_ids (cost=0.00..0.05 rows=5 width=4) (actual time=0.007..0.007 rows=5 loops=1)
I/O Timings: read=0.000 write=0.000
CTE upserted
-> ModifyTable on public.vulnerability_statistics vulnerability_statistics_1 (cost=0.00..6.57 rows=146 width=103) (actual time=24.698..43.689 rows=5 loops=1)
Buffers: shared hit=93 read=61 dirtied=17
WAL: records=25 fpi=15 bytes=69724
I/O Timings: read=41.740 write=0.000
-> CTE Scan on new_values new_values_2 (cost=0.00..6.57 rows=146 width=103) (actual time=20.512..20.582 rows=5 loops=1)
Buffers: shared hit=53 read=26 dirtied=2
WAL: records=2 fpi=1 bytes=8000
I/O Timings: read=19.577 write=0.000
-> Sort (cost=520.54..520.90 rows=146 width=128) (actual time=43.922..43.924 rows=0 loops=1)
Sort Key: (new_values.traversal_ids[array_length(new_values.traversal_ids, 1)]), new_values.traversal_ids, new_values.project_id
Sort Method: quicksort Memory: 25kB
Buffers: shared hit=127 read=61 dirtied=17
WAL: records=26 fpi=15 bytes=69785
I/O Timings: read=41.740 write=0.000
-> Hash Semi Join (cost=13.20..515.29 rows=146 width=128) (actual time=43.890..43.891 rows=0 loops=1)
Hash Cond: (new_values.project_id = upserted.project_id)
Buffers: shared hit=116 read=61 dirtied=17
WAL: records=26 fpi=15 bytes=69785
I/O Timings: read=41.740 write=0.000
-> Hash Right Join (cost=8.46..508.17 rows=146 width=120) (actual time=0.172..0.173 rows=0 loops=1)
Hash Cond: (vulnerability_statistics.project_id = new_values.project_id)
Filter: ((new_values.total <> COALESCE(vulnerability_statistics.total, 0)) OR (new_values.info <> COALESCE(vulnerability_statistics.info, 0)) OR (new_values.unknown <> COALESCE(vulnerability_statistics.unknown, 0)) OR (new_values.low <> COALESCE(vulnerability_statistics.low, 0)) OR (new_values.medium <> COALESCE(vulnerability_statistics.medium, 0)) OR (new_values.high <> COALESCE(vulnerability_statistics.high, 0)) OR (new_values.critical <> COALESCE(vulnerability_statistics.critical, 0)))
Rows Removed by Filter: 5
Buffers: shared hit=23
WAL: records=1 fpi=0 bytes=61
I/O Timings: read=0.000 write=0.000
-> Nested Loop (cost=3.71..502.85 rows=146 width=36) (actual time=0.051..0.141 rows=4 loops=1)
Buffers: shared hit=23
WAL: records=1 fpi=0 bytes=61
I/O Timings: read=0.000 write=0.000
-> HashAggregate (cost=3.29..4.75 rows=146 width=4) (actual time=0.007..0.009 rows=5 loops=1)
Group Key: new_values_1.project_id
I/O Timings: read=0.000 write=0.000
-> CTE Scan on new_values new_values_1 (cost=0.00..2.92 rows=146 width=4) (actual time=0.000..0.001 rows=5 loops=1)
I/O Timings: read=0.000 write=0.000
-> Index Scan using index_vulnerability_statistics_on_unique_project_id on public.vulnerability_statistics (cost=0.42..3.41 rows=1 width=36) (actual time=0.025..0.025 rows=1 loops=5)
Index Cond: (vulnerability_statistics.project_id = new_values_1.project_id)
Buffers: shared hit=23
WAL: records=1 fpi=0 bytes=61
I/O Timings: read=0.000 write=0.000
-> Hash (cost=2.92..2.92 rows=146 width=92) (actual time=0.007..0.007 rows=5 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
I/O Timings: read=0.000 write=0.000
-> CTE Scan on new_values (cost=0.00..2.92 rows=146 width=92) (actual time=0.001..0.003 rows=5 loops=1)
I/O Timings: read=0.000 write=0.000
-> Hash (cost=2.92..2.92 rows=146 width=8) (actual time=43.707..43.707 rows=5 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
Buffers: shared hit=93 read=61 dirtied=17
WAL: records=25 fpi=15 bytes=69724
I/O Timings: read=41.740 write=0.000
-> CTE Scan on upserted (cost=0.00..2.92 rows=146 width=8) (actual time=24.700..43.696 rows=5 loops=1)
Buffers: shared hit=93 read=61 dirtied=17
WAL: records=25 fpi=15 bytes=69724
I/O Timings: read=41.740 write=0.000
Settings: effective_cache_size = '338688MB', jit = 'off', seq_page_cost = '4', work_mem = '100MB', random_page_cost = '1.5'Another Statistics::AdjustmentService for project with 24k associated vulnerability_reads records
Raw SQL
WITH new_values AS (
SELECT
project_ids.project_id AS project_id,
project_attributes.archived AS archived,
project_attributes.traversal_ids AS traversal_ids,
COALESCE(severity_counts.total, 0) AS total,
COALESCE(severity_counts.info, 0) AS info,
COALESCE(severity_counts.unknown, 0) AS unknown,
COALESCE(severity_counts.low, 0) AS low,
COALESCE(severity_counts.medium, 0) AS medium,
COALESCE(severity_counts.high, 0) AS high,
COALESCE(severity_counts.critical, 0) AS critical,
(
CASE WHEN severity_counts.critical > 0 THEN
4
WHEN severity_counts.high > 0
OR severity_counts.unknown > 0 THEN
3
WHEN severity_counts.medium > 0 THEN
2
WHEN severity_counts.low > 0 THEN
1
ELSE
0
END) AS letter_grade,
now() AS created_at,
now() AS updated_at
FROM
unnest(ARRAY[71329491, 68794259, 69782606, 68571417, 66101996, 62548178]) project_ids (project_id)
JOIN (
VALUES (71329491, FALSE, ARRAY[9970, 96981785, 97058478, 98867842, 100569068]::bigint[]),
(68794259, FALSE, ARRAY[9970, 96981785, 97058478, 98867842, 100569068]::bigint[]),
(69782606, FALSE, ARRAY[9970, 96981785, 97058478, 98867842, 100569068]::bigint[]),
(68571417, FALSE, ARRAY[9970, 96981785, 97058478, 98867842, 100569068]::bigint[]),
(66101996, FALSE, ARRAY[9970, 96981785, 97058478, 98867842, 100569068]::bigint[]),
(62548178, FALSE, ARRAY[9970,3455548,12496989,79226984,94862429,62548178]::bigint[])) project_attributes (project_id, archived, traversal_ids) ON project_attributes.project_id = project_ids.project_id
LEFT OUTER JOIN (
SELECT
vulnerability_reads.project_id AS project_id, COUNT(*) AS total,
COUNT(*) FILTER (WHERE severity = 1) AS info,
COUNT(*) FILTER (WHERE severity = 2) AS unknown,
COUNT(*) FILTER (WHERE severity = 4) AS low,
COUNT(*) FILTER (WHERE severity = 5) AS medium,
COUNT(*) FILTER (WHERE severity = 6) AS high,
COUNT(*) FILTER (WHERE severity = 7) AS critical
FROM
vulnerability_reads
WHERE
vulnerability_reads.project_id IN (71329491, 68794259, 69782606, 68571417, 66101996, 62548178)
AND vulnerability_reads.state IN (1, 4)
GROUP BY
vulnerability_reads.project_id) AS severity_counts ON severity_counts.project_id = project_ids.project_id
),
old_values AS (
SELECT
project_id,
traversal_ids,
total,
critical,
high,
medium,
low,
unknown,
info
FROM
vulnerability_statistics
WHERE
project_id IN (
SELECT
project_id
FROM
new_values)
),
upserted AS (
INSERT INTO vulnerability_statistics (project_id, archived, traversal_ids, total, info, unknown, low, medium, high, critical, letter_grade, created_at, updated_at) (
SELECT
project_id,
archived,
traversal_ids,
total,
info,
unknown,
low,
medium,
high,
critical,
letter_grade,
created_at,
updated_at
FROM
new_values)
ON CONFLICT (project_id)
DO UPDATE SET
total = EXCLUDED.total,
info = EXCLUDED.info,
unknown = EXCLUDED.unknown,
low = EXCLUDED.low,
medium = EXCLUDED.medium,
high = EXCLUDED.high,
critical = EXCLUDED.critical,
letter_grade = EXCLUDED.letter_grade,
updated_at = EXCLUDED.updated_at
RETURNING
project_id
),
diff_values AS (
SELECT
new_values.traversal_ids[array_length(new_values.traversal_ids, 1)] AS namespace_id,
new_values.traversal_ids,
ARRAY_AGG(DISTINCT new_values.project_id ORDER BY new_values.project_id) AS affected_project_ids,
SUM(new_values.total - COALESCE(old_values.total, 0)) AS total,
SUM(new_values.info - COALESCE(old_values.info, 0)) AS info,
SUM(new_values.unknown - COALESCE(old_values.unknown, 0)) AS unknown,
SUM(new_values.low - COALESCE(old_values.low, 0)) AS low,
SUM(new_values.medium - COALESCE(old_values.medium, 0)) AS medium,
SUM(new_values.high - COALESCE(old_values.high, 0)) AS high,
SUM(new_values.critical - COALESCE(old_values.critical, 0)) AS critical
FROM
new_values
LEFT JOIN old_values ON new_values.project_id = old_values.project_id
WHERE
EXISTS (
SELECT
1
FROM
upserted
WHERE
upserted.project_id = new_values.project_id)
AND (new_values.total != COALESCE(old_values.total, 0)
OR new_values.info != COALESCE(old_values.info, 0)
OR new_values.unknown != COALESCE(old_values.unknown, 0)
OR new_values.low != COALESCE(old_values.low, 0)
OR new_values.medium != COALESCE(old_values.medium, 0)
OR new_values.high != COALESCE(old_values.high, 0)
OR new_values.critical != COALESCE(old_values.critical, 0))
GROUP BY
namespace_id,
new_values.traversal_ids
)
SELECT
*
FROM
diff_values
WHERE
total != 0
OR info != 0
OR unknown != 0
OR low != 0
OR medium != 0
OR high != 0
OR critical != 0Plan
See full plan here.
Aggregate (cost=748.23..771.46 rows=200 width=296) (actual time=353.086..353.092 rows=0 loops=1)
Group Key: (new_values.traversal_ids[array_length(new_values.traversal_ids, 1)]), new_values.traversal_ids
Filter: ((sum((new_values.total - COALESCE(vulnerability_statistics.total, 0))) <> '0'::numeric) OR (sum((new_values.info - COALESCE(vulnerability_statistics.info, 0))) <> '0'::numeric) OR (sum((new_values.unknown - COALESCE(vulnerability_statistics.unknown, 0))) <> '0'::numeric) OR (sum((new_values.low - COALESCE(vulnerability_statistics.low, 0))) <> '0'::numeric) OR (sum((new_values.medium - COALESCE(vulnerability_statistics.medium, 0))) <> '0'::numeric) OR (sum((new_values.high - COALESCE(vulnerability_statistics.high, 0))) <> '0'::numeric) OR (sum((new_values.critical - COALESCE(vulnerability_statistics.critical, 0))) <> '0'::numeric))
Rows Removed by Filter: 0
Buffers: shared hit=5124 read=701 dirtied=113
WAL: records=141 fpi=113 bytes=882551
I/O Timings: read=340.577 write=0.000
CTE new_values
-> Hash Right Join (cost=4029.15..4509.99 rows=587 width=113) (actual time=352.093..352.203 rows=6 loops=1)
Hash Cond: (vulnerability_reads.project_id = project_ids.project_id)
Buffers: shared hit=4966 read=698 dirtied=110
WAL: records=111 fpi=110 bytes=861143
I/O Timings: read=340.323 write=0.000
-> HashAggregate (cost=4028.78..4224.44 rows=19566 width=64) (actual time=352.044..352.143 rows=5 loops=1)
Group Key: vulnerability_reads.project_id
Buffers: shared hit=4966 read=698 dirtied=110
WAL: records=111 fpi=110 bytes=861143
I/O Timings: read=340.323 write=0.000
-> Index Only Scan using idx_vuln_reads_for_filtering on public.vulnerability_reads (cost=0.57..3166.80 rows=24628 width=10) (actual time=4.094..347.936 rows=23896 loops=1)
Index Cond: ((vulnerability_reads.project_id = ANY ('{71329491,68794259,69782606,68571417,66101996,62548178}'::bigint[])) AND (vulnerability_reads.state = ANY ('{1,4}'::integer[])))
Heap Fetches: 672
Buffers: shared hit=4966 read=698 dirtied=110
WAL: records=111 fpi=110 bytes=861143
I/O Timings: read=340.323 write=0.000
-> Hash (cost=0.30..0.30 rows=6 width=37) (actual time=0.033..0.035 rows=6 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
I/O Timings: read=0.000 write=0.000
-> Hash Join (cost=0.14..0.30 rows=6 width=37) (actual time=0.026..0.031 rows=6 loops=1)
Hash Cond: ("*VALUES*".column1 = project_ids.project_id)
I/O Timings: read=0.000 write=0.000
-> Values Scan on "*VALUES*" (cost=0.00..0.08 rows=6 width=37) (actual time=0.002..0.005 rows=6 loops=1)
I/O Timings: read=0.000 write=0.000
-> Hash (cost=0.06..0.06 rows=6 width=4) (actual time=0.011..0.012 rows=6 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
I/O Timings: read=0.000 write=0.000
-> Function Scan on unnest project_ids (cost=0.00..0.06 rows=6 width=4) (actual time=0.006..0.007 rows=6 loops=1)
I/O Timings: read=0.000 write=0.000
CTE upserted
-> ModifyTable on public.vulnerability_statistics vulnerability_statistics_1 (cost=0.00..26.41 rows=587 width=103) (actual time=0.574..0.826 rows=6 loops=1)
Buffers: shared hit=118 read=3 dirtied=3
WAL: records=30 fpi=3 bytes=21408
I/O Timings: read=0.254 write=0.000
-> CTE Scan on new_values new_values_2 (cost=0.00..26.41 rows=587 width=103) (actual time=0.115..0.232 rows=6 loops=1)
Buffers: shared hit=20
I/O Timings: read=0.000 write=0.000
-> Sort (cost=748.23..748.96 rows=294 width=128) (actual time=353.085..353.087 rows=0 loops=1)
Sort Key: (new_values.traversal_ids[array_length(new_values.traversal_ids, 1)]), new_values.traversal_ids, new_values.project_id
Sort Method: quicksort Memory: 25kB
Buffers: shared hit=5124 read=701 dirtied=113
WAL: records=141 fpi=113 bytes=882551
I/O Timings: read=340.577 write=0.000
-> Hash Left Join (cost=717.76..736.18 rows=294 width=128) (actual time=353.048..353.050 rows=0 loops=1)
Hash Cond: (new_values.project_id = vulnerability_statistics.project_id)
Filter: ((new_values.total <> COALESCE(vulnerability_statistics.total, 0)) OR (new_values.info <> COALESCE(vulnerability_statistics.info, 0)) OR (new_values.unknown <> COALESCE(vulnerability_statistics.unknown, 0)) OR (new_values.low <> COALESCE(vulnerability_statistics.low, 0)) OR (new_values.medium <> COALESCE(vulnerability_statistics.medium, 0)) OR (new_values.high <> COALESCE(vulnerability_statistics.high, 0)) OR (new_values.critical <> COALESCE(vulnerability_statistics.critical, 0)))
Rows Removed by Filter: 6
Buffers: shared hit=5113 read=701 dirtied=113
WAL: records=141 fpi=113 bytes=882551
I/O Timings: read=340.577 write=0.000
-> Hash Join (cost=17.71..34.26 rows=294 width=92) (actual time=352.958..352.961 rows=6 loops=1)
Hash Cond: (new_values.project_id = upserted.project_id)
Buffers: shared hit=5084 read=701 dirtied=113
WAL: records=141 fpi=113 bytes=882551
I/O Timings: read=340.577 write=0.000
-> CTE Scan on new_values (cost=0.00..11.74 rows=587 width=92) (actual time=352.097..352.098 rows=6 loops=1)
Buffers: shared hit=4966 read=698 dirtied=110
WAL: records=111 fpi=110 bytes=861143
I/O Timings: read=340.323 write=0.000
-> Hash (cost=15.21..15.21 rows=200 width=8) (actual time=0.842..0.843 rows=6 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
Buffers: shared hit=118 read=3 dirtied=3
WAL: records=30 fpi=3 bytes=21408
I/O Timings: read=0.254 write=0.000
-> HashAggregate (cost=13.21..15.21 rows=200 width=8) (actual time=0.836..0.838 rows=6 loops=1)
Group Key: upserted.project_id
Buffers: shared hit=118 read=3 dirtied=3
WAL: records=30 fpi=3 bytes=21408
I/O Timings: read=0.254 write=0.000
-> CTE Scan on upserted (cost=0.00..11.74 rows=587 width=8) (actual time=0.576..0.830 rows=6 loops=1)
Buffers: shared hit=118 read=3 dirtied=3
WAL: records=30 fpi=3 bytes=21408
I/O Timings: read=0.254 write=0.000
-> Hash (cost=692.71..692.71 rows=587 width=36) (actual time=0.064..0.065 rows=5 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
Buffers: shared hit=29
I/O Timings: read=0.000 write=0.000
-> Nested Loop (cost=13.63..692.71 rows=587 width=36) (actual time=0.044..0.062 rows=5 loops=1)
Buffers: shared hit=29
I/O Timings: read=0.000 write=0.000
-> HashAggregate (cost=13.21..15.21 rows=200 width=4) (actual time=0.006..0.008 rows=6 loops=1)
Group Key: new_values_1.project_id
I/O Timings: read=0.000 write=0.000
-> CTE Scan on new_values new_values_1 (cost=0.00..11.74 rows=587 width=4) (actual time=0.000..0.001 rows=6 loops=1)
I/O Timings: read=0.000 write=0.000
-> Index Scan using index_vulnerability_statistics_on_unique_project_id on public.vulnerability_statistics (cost=0.42..3.39 rows=1 width=36) (actual time=0.008..0.008 rows=1 loops=6)
Index Cond: (vulnerability_statistics.project_id = new_values_1.project_id)
Buffers: shared hit=29
I/O Timings: read=0.000 write=0.000
Settings: effective_cache_size = '338688MB', random_page_cost = '1.5', jit = 'off', seq_page_cost = '4', work_mem = '100MB'Another Statistics::AdjustmentService for project with 110k+ associated vulnerability_reads records
Raw SQL
WITH new_values AS (
SELECT
project_ids.project_id AS project_id,
project_attributes.archived AS archived,
project_attributes.traversal_ids AS traversal_ids,
COALESCE(severity_counts.total, 0) AS total,
COALESCE(severity_counts.info, 0) AS info,
COALESCE(severity_counts.unknown, 0) AS unknown,
COALESCE(severity_counts.low, 0) AS low,
COALESCE(severity_counts.medium, 0) AS medium,
COALESCE(severity_counts.high, 0) AS high,
COALESCE(severity_counts.critical, 0) AS critical,
(
CASE WHEN severity_counts.critical > 0 THEN
4
WHEN severity_counts.high > 0
OR severity_counts.unknown > 0 THEN
3
WHEN severity_counts.medium > 0 THEN
2
WHEN severity_counts.low > 0 THEN
1
ELSE
0
END) AS letter_grade,
now() AS created_at,
now() AS updated_at
FROM
unnest(ARRAY[13728947, 21574229, 14485459, 50760550, 44002359]) project_ids (project_id)
JOIN (
VALUES (13728947, FALSE, ARRAY[9970, 96981785, 97058478, 98867842, 100569068]::bigint[]),
(21574229, FALSE, ARRAY[9970, 96981785, 97058478, 98867842, 100569068]::bigint[]),
(14485459, FALSE, ARRAY[9970, 96981785, 97058478, 98867842, 100569068]::bigint[]),
(50760550, FALSE, ARRAY[9970, 96981785, 97058478, 98867842, 100569068]::bigint[]),
(44002359, FALSE, ARRAY[9970, 96981785, 97058478, 98867842, 100569068]::bigint[])
) project_attributes (project_id, archived, traversal_ids) ON project_attributes.project_id = project_ids.project_id
LEFT OUTER JOIN (
SELECT
vulnerability_reads.project_id AS project_id, COUNT(*) AS total,
COUNT(*) FILTER (WHERE severity = 1) AS info,
COUNT(*) FILTER (WHERE severity = 2) AS unknown,
COUNT(*) FILTER (WHERE severity = 4) AS low,
COUNT(*) FILTER (WHERE severity = 5) AS medium,
COUNT(*) FILTER (WHERE severity = 6) AS high,
COUNT(*) FILTER (WHERE severity = 7) AS critical
FROM
vulnerability_reads
WHERE
vulnerability_reads.project_id IN (13728947, 21574229, 14485459, 50760550, 44002359)
AND vulnerability_reads.state IN (1, 4)
GROUP BY
vulnerability_reads.project_id) AS severity_counts ON severity_counts.project_id = project_ids.project_id
),
old_values AS (
SELECT
project_id,
traversal_ids,
total,
critical,
high,
medium,
low,
unknown,
info
FROM
vulnerability_statistics
WHERE
project_id IN (
SELECT
project_id
FROM
new_values)
),
upserted AS (
INSERT INTO vulnerability_statistics (project_id, archived, traversal_ids, total, info, unknown, low, medium, high, critical, letter_grade, created_at, updated_at) (
SELECT
project_id,
archived,
traversal_ids,
total,
info,
unknown,
low,
medium,
high,
critical,
letter_grade,
created_at,
updated_at
FROM
new_values)
ON CONFLICT (project_id)
DO UPDATE SET
total = EXCLUDED.total,
info = EXCLUDED.info,
unknown = EXCLUDED.unknown,
low = EXCLUDED.low,
medium = EXCLUDED.medium,
high = EXCLUDED.high,
critical = EXCLUDED.critical,
letter_grade = EXCLUDED.letter_grade,
updated_at = EXCLUDED.updated_at
RETURNING
project_id
),
diff_values AS (
SELECT
new_values.traversal_ids[array_length(new_values.traversal_ids, 1)] AS namespace_id,
new_values.traversal_ids,
ARRAY_AGG(DISTINCT new_values.project_id ORDER BY new_values.project_id) AS affected_project_ids,
SUM(new_values.total - COALESCE(old_values.total, 0)) AS total,
SUM(new_values.info - COALESCE(old_values.info, 0)) AS info,
SUM(new_values.unknown - COALESCE(old_values.unknown, 0)) AS unknown,
SUM(new_values.low - COALESCE(old_values.low, 0)) AS low,
SUM(new_values.medium - COALESCE(old_values.medium, 0)) AS medium,
SUM(new_values.high - COALESCE(old_values.high, 0)) AS high,
SUM(new_values.critical - COALESCE(old_values.critical, 0)) AS critical
FROM
new_values
LEFT JOIN old_values ON new_values.project_id = old_values.project_id
WHERE
EXISTS (
SELECT
1
FROM
upserted
WHERE
upserted.project_id = new_values.project_id)
AND (new_values.total != COALESCE(old_values.total, 0)
OR new_values.info != COALESCE(old_values.info, 0)
OR new_values.unknown != COALESCE(old_values.unknown, 0)
OR new_values.low != COALESCE(old_values.low, 0)
OR new_values.medium != COALESCE(old_values.medium, 0)
OR new_values.high != COALESCE(old_values.high, 0)
OR new_values.critical != COALESCE(old_values.critical, 0))
GROUP BY
namespace_id,
new_values.traversal_ids
)
SELECT
*
FROM
diff_values
WHERE
total != 0
OR info != 0
OR unknown != 0
OR low != 0
OR medium != 0
OR high != 0
OR critical != 0Plan
See full plan here.
Aggregate (cost=828.04..866.93 rows=200 width=296) (actual time=38662.915..38662.941 rows=0 loops=1)
Group Key: (new_values.traversal_ids[array_length(new_values.traversal_ids, 1)]), new_values.traversal_ids
Filter: ((sum((new_values.total - COALESCE(vulnerability_statistics.total, 0))) <> '0'::numeric) OR (sum((new_values.info - COALESCE(vulnerability_statistics.info, 0))) <> '0'::numeric) OR (sum((new_values.unknown - COALESCE(vulnerability_statistics.unknown, 0))) <> '0'::numeric) OR (sum((new_values.low - COALESCE(vulnerability_statistics.low, 0))) <> '0'::numeric) OR (sum((new_values.medium - COALESCE(vulnerability_statistics.medium, 0))) <> '0'::numeric) OR (sum((new_values.high - COALESCE(vulnerability_statistics.high, 0))) <> '0'::numeric) OR (sum((new_values.critical - COALESCE(vulnerability_statistics.critical, 0))) <> '0'::numeric))
Rows Removed by Filter: 0
Buffers: shared hit=808796 read=94282 dirtied=19796
WAL: records=20684 fpi=19794 bytes=155105486
I/O Timings: read=36551.800 write=0.000
CTE new_values
-> Hash Right Join (cost=582675.62..583930.92 rows=1284 width=113) (actual time=38652.767..38654.047 rows=5 loops=1)
Hash Cond: (vulnerability_reads.project_id = project_ids.project_id)
Buffers: shared hit=808716 read=94269 dirtied=19789
WAL: records=20672 fpi=19789 bytes=155082929
I/O Timings: read=36543.868 write=0.000
-> HashAggregate (cost=582675.31..583188.99 rows=51368 width=64) (actual time=38652.682..38653.930 rows=5 loops=1)
Group Key: vulnerability_reads.project_id
Buffers: shared hit=808716 read=94269 dirtied=19789
WAL: records=20672 fpi=19789 bytes=155082929
I/O Timings: read=36543.868 write=0.000
-> Index Only Scan using index_vuln_reads_on_project_id_state_severity_and_vuln_id on public.vulnerability_reads (cost=0.57..431594.00 rows=4316609 width=10) (actual time=3.906..37705.020 rows=6271600 loops=1)
Index Cond: ((vulnerability_reads.project_id = ANY ('{13728947,21574229,14485459,50760550,44002359}'::bigint[])) AND (vulnerability_reads.state = ANY ('{1,4}'::integer[])))
Heap Fetches: 155443
Buffers: shared hit=808716 read=94269 dirtied=19789
WAL: records=20672 fpi=19789 bytes=155082929
I/O Timings: read=36543.868 write=0.000
-> Hash (cost=0.25..0.25 rows=5 width=37) (actual time=0.032..0.046 rows=5 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
I/O Timings: read=0.000 write=0.000
-> Hash Join (cost=0.12..0.25 rows=5 width=37) (actual time=0.026..0.040 rows=5 loops=1)
Hash Cond: ("*VALUES*".column1 = project_ids.project_id)
I/O Timings: read=0.000 write=0.000
-> Values Scan on "*VALUES*" (cost=0.00..0.06 rows=5 width=37) (actual time=0.003..0.009 rows=5 loops=1)
I/O Timings: read=0.000 write=0.000
-> Hash (cost=0.05..0.05 rows=5 width=4) (actual time=0.009..0.010 rows=5 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
I/O Timings: read=0.000 write=0.000
-> Function Scan on unnest project_ids (cost=0.00..0.05 rows=5 width=4) (actual time=0.005..0.005 rows=5 loops=1)
I/O Timings: read=0.000 write=0.000
CTE upserted
-> ModifyTable on public.vulnerability_statistics vulnerability_statistics_1 (cost=0.00..57.78 rows=1284 width=103) (actual time=2.586..9.762 rows=5 loops=1)
Buffers: shared hit=46 read=13 dirtied=7
WAL: records=12 fpi=5 bytes=22557
I/O Timings: read=7.932 write=0.000
-> CTE Scan on new_values new_values_2 (cost=0.00..57.78 rows=1284 width=103) (actual time=0.281..1.560 rows=5 loops=1)
Buffers: shared hit=19 dirtied=1
WAL: records=1 fpi=0 bytes=99
I/O Timings: read=0.000 write=0.000
-> Sort (cost=828.04..829.65 rows=642 width=128) (actual time=38662.914..38662.915 rows=0 loops=1)
Sort Key: (new_values.traversal_ids[array_length(new_values.traversal_ids, 1)]), new_values.traversal_ids, new_values.project_id
Sort Method: quicksort Memory: 25kB
Buffers: shared hit=808796 read=94282 dirtied=19796
WAL: records=20684 fpi=19794 bytes=155105486
I/O Timings: read=36551.800 write=0.000
-> Hash Left Join (cost=757.84..798.10 rows=642 width=128) (actual time=38662.883..38662.884 rows=0 loops=1)
Hash Cond: (new_values.project_id = vulnerability_statistics.project_id)
Filter: ((new_values.total <> COALESCE(vulnerability_statistics.total, 0)) OR (new_values.info <> COALESCE(vulnerability_statistics.info, 0)) OR (new_values.unknown <> COALESCE(vulnerability_statistics.unknown, 0)) OR (new_values.low <> COALESCE(vulnerability_statistics.low, 0)) OR (new_values.medium <> COALESCE(vulnerability_statistics.medium, 0)) OR (new_values.high <> COALESCE(vulnerability_statistics.high, 0)) OR (new_values.critical <> COALESCE(vulnerability_statistics.critical, 0)))
Rows Removed by Filter: 5
Buffers: shared hit=808785 read=94282 dirtied=19796
WAL: records=20684 fpi=19794 bytes=155105486
I/O Timings: read=36551.800 write=0.000
-> Hash Join (cost=33.39..69.58 rows=642 width=92) (actual time=38662.711..38662.716 rows=5 loops=1)
Hash Cond: (new_values.project_id = upserted.project_id)
Buffers: shared hit=808762 read=94282 dirtied=19796
WAL: records=20684 fpi=19794 bytes=155105486
I/O Timings: read=36551.800 write=0.000
-> CTE Scan on new_values (cost=0.00..25.68 rows=1284 width=92) (actual time=38652.785..38652.788 rows=5 loops=1)
Buffers: shared hit=808716 read=94269 dirtied=19789
WAL: records=20672 fpi=19789 bytes=155082929
I/O Timings: read=36543.868 write=0.000
-> Hash (cost=30.89..30.89 rows=200 width=8) (actual time=9.834..9.834 rows=5 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
Buffers: shared hit=46 read=13 dirtied=7
WAL: records=12 fpi=5 bytes=22557
I/O Timings: read=7.932 write=0.000
-> HashAggregate (cost=28.89..30.89 rows=200 width=8) (actual time=9.821..9.822 rows=5 loops=1)
Group Key: upserted.project_id
Buffers: shared hit=46 read=13 dirtied=7
WAL: records=12 fpi=5 bytes=22557
I/O Timings: read=7.932 write=0.000
-> CTE Scan on upserted (cost=0.00..25.68 rows=1284 width=8) (actual time=2.597..9.775 rows=5 loops=1)
Buffers: shared hit=46 read=13 dirtied=7
WAL: records=12 fpi=5 bytes=22557
I/O Timings: read=7.932 write=0.000
-> Hash (cost=708.40..708.40 rows=1284 width=36) (actual time=0.132..0.133 rows=5 loops=1)
Buckets: 2048 Batches: 1 Memory Usage: 17kB
Buffers: shared hit=23
I/O Timings: read=0.000 write=0.000
-> Nested Loop (cost=29.31..708.40 rows=1284 width=36) (actual time=0.109..0.124 rows=5 loops=1)
Buffers: shared hit=23
I/O Timings: read=0.000 write=0.000
-> HashAggregate (cost=28.89..30.89 rows=200 width=4) (actual time=0.031..0.032 rows=5 loops=1)
Group Key: new_values_1.project_id
I/O Timings: read=0.000 write=0.000
-> CTE Scan on new_values new_values_1 (cost=0.00..25.68 rows=1284 width=4) (actual time=0.003..0.004 rows=5 loops=1)
I/O Timings: read=0.000 write=0.000
-> Index Scan using index_vulnerability_statistics_on_unique_project_id on public.vulnerability_statistics (cost=0.42..3.39 rows=1 width=36) (actual time=0.014..0.014 rows=1 loops=5)
Index Cond: (vulnerability_statistics.project_id = new_values_1.project_id)
Buffers: shared hit=23
I/O Timings: read=0.000 write=0.000
Settings: jit = 'off', seq_page_cost = '4', work_mem = '100MB', effective_cache_size = '338688MB', random_page_cost = '1.5'The original Statistics::AdjustmentService for project with 110k+ associated vulnerability_reads records (from master)
Raw SQL
WITH new_values AS (
SELECT
project_ids.project_id AS project_id,
project_attributes.archived AS archived,
project_attributes.traversal_ids AS traversal_ids,
COALESCE(severity_counts.total, 0) AS total,
COALESCE(severity_counts.info, 0) AS info,
COALESCE(severity_counts.unknown, 0) AS unknown,
COALESCE(severity_counts.low, 0) AS low,
COALESCE(severity_counts.medium, 0) AS medium,
COALESCE(severity_counts.high, 0) AS high,
COALESCE(severity_counts.critical, 0) AS critical,
(
CASE WHEN severity_counts.critical > 0 THEN
4
WHEN severity_counts.high > 0
OR severity_counts.unknown > 0 THEN
3
WHEN severity_counts.medium > 0 THEN
2
WHEN severity_counts.low > 0 THEN
1
ELSE
0
END) AS letter_grade,
now() AS created_at,
now() AS updated_at
FROM
unnest(ARRAY[13728947, 21574229, 14485459, 50760550, 44002359]) project_ids (project_id)
JOIN (
VALUES (13728947, FALSE, ARRAY[9970, 96981785, 97058478, 98867842, 100569068]::bigint[]),
(21574229, FALSE, ARRAY[9970, 96981785, 97058478, 98867842, 100569068]::bigint[]),
(14485459, FALSE, ARRAY[9970, 96981785, 97058478, 98867842, 100569068]::bigint[]),
(50760550, FALSE, ARRAY[9970, 96981785, 97058478, 98867842, 100569068]::bigint[]),
(44002359, FALSE, ARRAY[9970, 96981785, 97058478, 98867842, 100569068]::bigint[])
) project_attributes (project_id, archived, traversal_ids) ON project_attributes.project_id = project_ids.project_id
LEFT OUTER JOIN (
SELECT
vulnerability_reads.project_id AS project_id, COUNT(*) AS total,
COUNT(*) FILTER (WHERE severity = 1) AS info,
COUNT(*) FILTER (WHERE severity = 2) AS unknown,
COUNT(*) FILTER (WHERE severity = 4) AS low,
COUNT(*) FILTER (WHERE severity = 5) AS medium,
COUNT(*) FILTER (WHERE severity = 6) AS high,
COUNT(*) FILTER (WHERE severity = 7) AS critical
FROM
vulnerability_reads
WHERE
vulnerability_reads.project_id IN (13728947, 21574229, 14485459, 50760550, 44002359)
AND vulnerability_reads.state IN (1, 4)
GROUP BY
vulnerability_reads.project_id) AS severity_counts ON severity_counts.project_id = project_ids.project_id
),
old_values AS (
SELECT
project_id,
traversal_ids,
total,
critical,
high,
medium,
low,
unknown,
info
FROM
vulnerability_statistics
WHERE
project_id IN (
SELECT
project_id
FROM
new_values)
),
upserted AS (
INSERT INTO vulnerability_statistics (project_id, archived, traversal_ids, total, info, unknown, low, medium, high, critical, letter_grade, created_at, updated_at) (
SELECT
project_id,
archived,
traversal_ids,
total,
info,
unknown,
low,
medium,
high,
critical,
letter_grade,
created_at,
updated_at
FROM
new_values)
ON CONFLICT (project_id)
DO UPDATE SET
total = EXCLUDED.total,
info = EXCLUDED.info,
unknown = EXCLUDED.unknown,
low = EXCLUDED.low,
medium = EXCLUDED.medium,
high = EXCLUDED.high,
critical = EXCLUDED.critical,
letter_grade = EXCLUDED.letter_grade,
updated_at = EXCLUDED.updated_at
RETURNING
project_id
),
diff_values AS (
SELECT
new_values.traversal_ids[array_length(new_values.traversal_ids, 1)] AS namespace_id,
new_values.traversal_ids,
SUM(new_values.total - COALESCE(old_values.total, 0)) AS total,
SUM(new_values.info - COALESCE(old_values.info, 0)) AS info,
SUM(new_values.unknown - COALESCE(old_values.unknown, 0)) AS unknown,
SUM(new_values.low - COALESCE(old_values.low, 0)) AS low,
SUM(new_values.medium - COALESCE(old_values.medium, 0)) AS medium,
SUM(new_values.high - COALESCE(old_values.high, 0)) AS high,
SUM(new_values.critical - COALESCE(old_values.critical, 0)) AS critical
FROM
new_values
LEFT JOIN old_values ON new_values.project_id = old_values.project_id
WHERE
EXISTS (
SELECT
1
FROM
upserted
WHERE
upserted.project_id = new_values.project_id)
GROUP BY
namespace_id,
new_values.traversal_ids
)
SELECT
*
FROM
diff_values
WHERE
total != 0
OR info != 0
OR unknown != 0
OR low != 0
OR medium != 0
OR high != 0
OR critical != 0Plan
See full plan here.
HashAggregate (cost=822.96..832.46 rows=200 width=264) (actual time=59167.269..59167.292 rows=1 loops=1)
Group Key: new_values.traversal_ids[array_length(new_values.traversal_ids, 1)], new_values.traversal_ids
Filter: ((sum((new_values.total - COALESCE(vulnerability_statistics.total, 0))) <> '0'::numeric) OR (sum((new_values.info - COALESCE(vulnerability_statistics.info, 0))) <> '0'::numeric) OR (sum((new_values.unknown - COALESCE(vulnerability_statistics.unknown, 0))) <> '0'::numeric) OR (sum((new_values.low - COALESCE(vulnerability_statistics.low, 0))) <> '0'::numeric) OR (sum((new_values.medium - COALESCE(vulnerability_statistics.medium, 0))) <> '0'::numeric) OR (sum((new_values.high - COALESCE(vulnerability_statistics.high, 0))) <> '0'::numeric) OR (sum((new_values.critical - COALESCE(vulnerability_statistics.critical, 0))) <> '0'::numeric))
Rows Removed by Filter: 0
Buffers: shared hit=837515 read=138707 dirtied=62543 written=2771
WAL: records=63632 fpi=62541 bytes=488552690
I/O Timings: read=56385.927 write=262.207
CTE new_values
-> Hash Right Join (cost=562972.27..564220.16 rows=1277 width=113) (actual time=59155.377..59156.617 rows=5 loops=1)
Hash Cond: (vulnerability_reads.project_id = project_ids.project_id)
Buffers: shared hit=837445 read=138687 dirtied=62536 written=2769
WAL: records=63617 fpi=62536 bytes=488529821
I/O Timings: read=56376.777 write=261.945
-> HashAggregate (cost=562971.96..563482.60 rows=51064 width=64) (actual time=59155.296..59156.505 rows=5 loops=1)
Group Key: vulnerability_reads.project_id
Buffers: shared hit=837445 read=138687 dirtied=62536 written=2769
WAL: records=63617 fpi=62536 bytes=488529821
I/O Timings: read=56376.777 write=261.945
-> Index Only Scan using index_vuln_reads_on_project_id_state_severity_and_vuln_id on public.vulnerability_reads (cost=0.57..411159.08 rows=4337511 width=10) (actual time=3.813..58165.096 rows=6284117 loops=1)
Index Cond: ((vulnerability_reads.project_id = ANY ('{13728947,21574229,14485459,50760550,44002359}'::bigint[])) AND (vulnerability_reads.state = ANY ('{1,4}'::integer[])))
Heap Fetches: 254741
Buffers: shared hit=837445 read=138687 dirtied=62536 written=2769
WAL: records=63617 fpi=62536 bytes=488529821
I/O Timings: read=56376.777 write=261.945
-> Hash (cost=0.25..0.25 rows=5 width=37) (actual time=0.030..0.040 rows=5 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
I/O Timings: read=0.000 write=0.000
-> Hash Join (cost=0.12..0.25 rows=5 width=37) (actual time=0.024..0.035 rows=5 loops=1)
Hash Cond: ("*VALUES*".column1 = project_ids.project_id)
I/O Timings: read=0.000 write=0.000
-> Values Scan on "*VALUES*" (cost=0.00..0.06 rows=5 width=37) (actual time=0.001..0.007 rows=5 loops=1)
I/O Timings: read=0.000 write=0.000
-> Hash (cost=0.05..0.05 rows=5 width=4) (actual time=0.010..0.010 rows=5 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
I/O Timings: read=0.000 write=0.000
-> Function Scan on unnest project_ids (cost=0.00..0.05 rows=5 width=4) (actual time=0.006..0.007 rows=5 loops=1)
I/O Timings: read=0.000 write=0.000
CTE upserted
-> ModifyTable on public.vulnerability_statistics vulnerability_statistics_1 (cost=0.00..57.46 rows=1277 width=103) (actual time=3.626..11.446 rows=5 loops=1)
Buffers: shared hit=47 read=20 dirtied=7 written=2
WAL: records=15 fpi=5 bytes=22869
I/O Timings: read=9.150 write=0.262
-> CTE Scan on new_values new_values_2 (cost=0.00..57.46 rows=1277 width=103) (actual time=0.617..1.865 rows=5 loops=1)
Buffers: shared hit=22 read=5 dirtied=1
WAL: records=1 fpi=0 bytes=99
I/O Timings: read=0.154 write=0.000
-> Hash Left Join (cost=757.44..797.44 rows=638 width=124) (actual time=59167.200..59167.211 rows=5 loops=1)
Hash Cond: (new_values.project_id = vulnerability_statistics.project_id)
Buffers: shared hit=837515 read=138707 dirtied=62543 written=2771
WAL: records=63632 fpi=62541 bytes=488552690
I/O Timings: read=56385.927 write=262.207
-> Hash Join (cost=33.23..69.22 rows=638 width=92) (actual time=59167.012..59167.021 rows=5 loops=1)
Hash Cond: (new_values.project_id = upserted.project_id)
Buffers: shared hit=837492 read=138707 dirtied=62543 written=2771
WAL: records=63632 fpi=62541 bytes=488552690
I/O Timings: read=56385.927 write=262.207
-> CTE Scan on new_values (cost=0.00..25.54 rows=1277 width=92) (actual time=59155.396..59155.399 rows=5 loops=1)
Buffers: shared hit=837445 read=138687 dirtied=62536 written=2769
WAL: records=63617 fpi=62536 bytes=488529821
I/O Timings: read=56376.777 write=261.945
-> Hash (cost=30.73..30.73 rows=200 width=8) (actual time=11.527..11.528 rows=5 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
Buffers: shared hit=47 read=20 dirtied=7 written=2
WAL: records=15 fpi=5 bytes=22869
I/O Timings: read=9.150 write=0.262
-> HashAggregate (cost=28.73..30.73 rows=200 width=8) (actual time=11.517..11.519 rows=5 loops=1)
Group Key: upserted.project_id
Buffers: shared hit=47 read=20 dirtied=7 written=2
WAL: records=15 fpi=5 bytes=22869
I/O Timings: read=9.150 write=0.262
-> CTE Scan on upserted (cost=0.00..25.54 rows=1277 width=8) (actual time=3.639..11.463 rows=5 loops=1)
Buffers: shared hit=47 read=20 dirtied=7 written=2
WAL: records=15 fpi=5 bytes=22869
I/O Timings: read=9.150 write=0.262
-> Hash (cost=708.25..708.25 rows=1277 width=36) (actual time=0.143..0.144 rows=5 loops=1)
Buckets: 2048 Batches: 1 Memory Usage: 17kB
Buffers: shared hit=23
I/O Timings: read=0.000 write=0.000
-> Nested Loop (cost=29.15..708.25 rows=1277 width=36) (actual time=0.117..0.134 rows=5 loops=1)
Buffers: shared hit=23
I/O Timings: read=0.000 write=0.000
-> HashAggregate (cost=28.73..30.73 rows=200 width=4) (actual time=0.034..0.035 rows=5 loops=1)
Group Key: new_values_1.project_id
I/O Timings: read=0.000 write=0.000
-> CTE Scan on new_values new_values_1 (cost=0.00..25.54 rows=1277 width=4) (actual time=0.003..0.004 rows=5 loops=1)
I/O Timings: read=0.000 write=0.000
-> Index Scan using index_vulnerability_statistics_on_unique_project_id on public.vulnerability_statistics (cost=0.42..3.39 rows=1 width=36) (actual time=0.015..0.015 rows=1 loops=5)
Index Cond: (vulnerability_statistics.project_id = new_values_1.project_id)
Buffers: shared hit=23
I/O Timings: read=0.000 write=0.000
Settings: random_page_cost = '1.5', jit = 'off', seq_page_cost = '4', work_mem = '100MB', effective_cache_size = '338688MB'Edited by Gal Katz