explain.depesz.com

PostgreSQL's explain analyze made readable

Result: cumY

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 1,013,949.833 ↓ 4.1 52,739 1

Sort (cost=104,281,796.90..104,281,829.13 rows=12,892 width=1,204) (actual time=1,013,938.492..1,013,949.833 rows=52,739 loops=1)

  • Output: dmr.object_id, ((SubPlan 1)), (COALESCE((SubPlan 2), '550e8400e29b41d'::character varying)), (COALESCE((SubPlan 3), '550e8400e29b41d'::character varying)), (COALESCE((SubPlan 4), 2)), ((SubPlan 5)), dmr.metric_id, ((SubPlan 6)), sr.risk_propagation_factor, sr.violation_index, sr.propagated_risk_index, ((SubPlan 7)), ((SubPlan 8))'
  • Sort Key: sr.propagated_risk_index DESC, ((SubPlan 1))
  • Sort Method: quicksort Memory: 15545kB
  • Buffers: shared hit=315121486
  • Output: dmr.object_id, (SubPlan 1), COALESCE((SubPlan 2), 550e8400e29b41d::character varying), COALESCE((SubPlan 3), 550e8400e29b41d::character varying), COALESCE((SubPlan 4), 2), (SubPlan 5), dmr.metric_id, (SubPlan 6), sr.risk_propagation_factor, sr.violation_index, sr.propagated_risk_index, (SubPlan 7), (SubPlan 8)
  • Hash Cond: (dmr.object_id = sr.object_id)
  • Buffers: shared hit=315121480
  • Output: dmr.object_id, dmr.metric_id, dmr.snapshot_id
  • Group Key: dmr.object_id, dmr.metric_id, dmr.snapshot_id
  • Buffers: shared hit=703
  • Output: dmr.object_id, dmr.metric_id, dmr.snapshot_id
  • Buffers: shared hit=703
  • Output: dmht.metric_id
  • Buffers: shared hit=3
  • Output: dmht.metric_id
  • Index Cond: ((dmht.snapshot_id = 1) AND (dmht.metric_parent_id = 7688))
  • Heap Fetches: 1
  • Buffers: shared hit=3
  • Output: dmr.snapshot_id, dmr.metric_id, dmr.object_id, dmr.metric_value_index
  • Index Cond: ((dmr.snapshot_id = 1) AND (dmr.metric_id = dmht.metric_id) AND (dmr.metric_value_index = 1))
  • Heap Fetches: 52739
  • Buffers: shared hit=700
  • Output: sr.risk_propagation_factor, sr.violation_index, sr.propagated_risk_index, sr.object_id, li.next_object_id
  • Buckets: 131072 (originally 1024) Batches: 1 (originally 1) Memory Usage: 5553kB
  • Buffers: shared hit=9286329
  • Output: sr.risk_propagation_factor, sr.violation_index, sr.propagated_risk_index, sr.object_id, li.next_object_id
  • Buffers: shared hit=9286329
  • Output: li.next_object_id
  • Group Key: li.next_object_id
  • Buffers: shared hit=1855865
  • Output: li.next_object_id
  • Buffers: shared hit=1855865
  • Output: dml.module_id
  • Group Key: dml.module_id
  • Buffers: shared hit=1
  • Output: dml.module_id, dml.object_id, dml.object_type_id, dml.snapshot_id
  • Filter: ((dml.object_id = 3) AND (dml.snapshot_id = 1))
  • Rows Removed by Filter: 10
  • Buffers: shared hit=1
  • Output: li.snapshot_id, li.previous_object_id, li.next_object_id, li.link_type_id
  • Index Cond: ((li.snapshot_id = 1) AND (li.previous_object_id = dml.module_id) AND (li.link_type_id = 3))
  • Heap Fetches: 2449601
  • Buffers: shared hit=1855864
  • Output: sr.snapshot_id, sr.business_criterion_id, sr.object_id, sr.violated_rules_nb, sr.violations_nb, sr.risk_propagation_factor, sr.violation_index, sr.propagated_risk_index
  • Index Cond: ((sr.object_id = li.next_object_id) AND (sr.snapshot_id = 1) AND (sr.business_criterion_id = 60013))
  • Buffers: shared hit=7430464
  • Output: o.object_full_name
  • Index Cond: (o.object_id = dmr.object_id)
  • Buffers: shared hit=211231
  • Output: oe.justify
  • Index Cond: ((oe.metric_id = 7688) AND (oe.object_id = dmr.object_id))
  • Filter: (oe.last_snapshot_id > 1)
  • Buffers: shared hit=52739
  • Output: ap.action_def
  • Join Filter: ((s.functional_date >= ap.first_snapshot_date) AND (s.functional_date <= ap.last_snapshot_date))
  • Buffers: shared hit=52739
  • Output: ap.metric_id, ap.object_id, ap.first_snapshot_date, ap.last_snapshot_date, ap.user_name, ap.sel_date, ap.priority, ap.action_def, ap.tag
  • Index Cond: ((ap.metric_id = 7688) AND (ap.object_id = dmr.object_id))
  • Buffers: shared hit=52739
  • Output: s.snapshot_id, s.application_id, s.functional_date, s.snapshot_type_id, s.snapshot_name, s.snapshot_description, s.snapshot_date, s.compute_start_date, s.compute_end_date, s.snapshot_status, s.external_id, s.snapshot_conso_id, s.version, s.consolidation_mode, s.enhancement_measure
  • Filter: (s.snapshot_id = 1)
  • Output: ap_1.priority
  • Join Filter: ((s_1.functional_date >= ap_1.first_snapshot_date) AND (s_1.functional_date <= ap_1.last_snapshot_date))
  • Buffers: shared hit=52739
  • Output: ap_1.metric_id, ap_1.object_id, ap_1.first_snapshot_date, ap_1.last_snapshot_date, ap_1.user_name, ap_1.sel_date, ap_1.priority, ap_1.action_def, ap_1.tag
  • Index Cond: ((ap_1.metric_id = 7688) AND (ap_1.object_id = dmr.object_id))
  • Buffers: shared hit=52739
  • Output: s_1.snapshot_id, s_1.application_id, s_1.functional_date, s_1.snapshot_type_id, s_1.snapshot_name, s_1.snapshot_description, s_1.snapshot_date, s_1.compute_start_date, s_1.compute_end_date, s_1.snapshot_status, s_1.external_id, s_1.snapshot_conso_id, s_1.version, s_1.consolidation_mode, s_1.enhancement_measure
  • Filter: (s_1.snapshot_id = 1)
  • Output: o_1.object_name
  • Index Cond: (o_1.object_id = dmr.object_id)
  • Buffers: shared hit=211231
  • Output: dmht_1.aggregate_weight
  • Index Cond: ((dmht_1.snapshot_id = 1) AND (dmht_1.metric_parent_id = 7688) AND (dmht_1.metric_id = dmr.metric_id))
  • Buffers: shared hit=158217
  • Output: CASE vs.violation_status WHEN 1 THEN Added::text WHEN 2 THEN Deleted::text WHEN 3 THEN Updated::text ELSE Unchanged::text END
  • Recheck Cond: ((vs.diag_id = 7688) AND (vs.snapshot_id = 1) AND (vs.previous_snapshot_id = -1::integer))
  • Filter: (vs.object_id = dmr.object_id)
  • Rows Removed by Filter: 52738
  • Heap Blocks: exact=293914447
  • Buffers: shared hit=304725942
  • Index Cond: ((vs.diag_id = 7688) AND (vs.snapshot_id = 1) AND (vs.previous_snapshot_id = -1::integer))
  • Buffers: shared hit=10811495
  • Output: CASE WHEN (oprev.object_checksum IS NULL) THEN Added::text WHEN (oprev.object_checksum = ocur.object_checksum) THEN Unchanged::text ELSE Updated::text END
  • Join Filter: (oprev.object_id = ocur.object_id)
  • Buffers: shared hit=369610
  • Output: ocur.snapshot_id, ocur.object_id, ocur.object_type_id, ocur.object_checksum
  • Index Cond: ((ocur.object_id = dmr.object_id) AND (ocur.snapshot_id = 1))
  • Buffers: shared hit=211245
  • Output: oprev.snapshot_id, oprev.object_id, oprev.object_type_id, oprev.object_checksum
  • Index Cond: ((oprev.object_id = dmr.object_id) AND (oprev.snapshot_id = -1::integer))
  • Buffers: shared hit=158365
2. 1,013,682.409 1,013,682.409 ↓ 4.1 52,739 1

Hash Join (cost=50,797.47..104,280,916.75 rows=12,892 width=1,204) (actual time=8,954.642..1,013,682.409 rows=52,739 loops=1)

3. 155.931 155.931 ↓ 1.4 52,739 1

HashAggregate (cost=50,179.95..50,562.18 rows=38,223 width=12) (actual time=32.615..155.931 rows=52,739 loops=1)

4. 17.938 17.938 ↓ 1.4 52,739 1

Nested Loop (cost=0.71..49,893.28 rows=38,223 width=12) (actual time=0.463..17.938 rows=52,739 loops=1)

5. 0.268 0.268 ↑ 1.0 1 1

Unique (cost=0.28..8.30 rows=1 width=4) (actual time=0.265..0.268 rows=1 loops=1)

6. 0.265 0.265 ↑ 1.0 1 1

Index Only Scan using dss_metr_hty_idx on mf_winris_central.dss_metric_histo_tree dmht (cost=0.28..8.30 rows=1 width=4) (actual time=0.263..0.265 rows=1 loops=1)

7. 14.291 14.291 ↓ 1.4 52,739 1

Index Only Scan using dss_metr_res_idx2 on mf_winris_central.dss_metric_results dmr (cost=0.43..49,502.74 rows=38,223 width=12) (actual time=0.189..14.291 rows=52,739 loops=1)

8. 8,891.477 8,891.477 ↓ 414.0 72,458 1

Hash (cost=615.33..615.33 rows=175 width=32) (actual time=8,891.477..8,891.477 rows=72,458 loops=1)

9. 8,863.462 8,863.462 ↓ 414.0 72,458 1

Nested Loop (cost=488.68..615.33 rows=175 width=32) (actual time=2,888.993..8,863.462 rows=72,458 loops=1)

10. 4,331.869 4,331.869 ↓ 9,421.5 2,449,601 1

HashAggregate (cost=488.26..490.86 rows=260 width=4) (actual time=2,888.368..4,331.869 rows=2,449,601 loops=1)

11. 1,144.355 1,144.355 ↓ 9,421.5 2,449,601 1

Nested Loop (cost=1.64..487.61 rows=260 width=4) (actual time=0.243..1,144.355 rows=2,449,601 loops=1)

12. 0.029 0.029 ↓ 1.5 3 1

HashAggregate (cost=1.20..1.22 rows=2 width=4) (actual time=0.026..0.029 rows=3 loops=1)

13. 0.019 0.019 ↑ 1.0 3 1

Seq Scan on mf_winris_central.dss_module_links dml (cost=0.00..1.20 rows=3 width=4) (actual time=0.018..0.019 rows=3 loops=1)

14. 987.261 987.261 ↓ 6,281.0 816,534 3

Index Only Scan using dss_linkall_idx on mf_winris_central.dss_link_info li (cost=0.43..241.88 rows=130 width=8) (actual time=0.085..329.087 rows=816,534 loops=3)

15. 4,899.202 4,899.202 ↓ 0.0 0 2,449,601

Index Scan using dss_snapshot_ranking_idx2 on mf_winris_central.dss_snapshot_ranking sr (cost=0.42..0.47 rows=1 width=28) (actual time=0.002..0.002 rows=0 loops=2,449,601)

16.          

SubPlan (forSort)

17. 527.390 527.390 ↑ 1.0 1 52,739

Index Scan using dss_objects_pk on mf_winris_central.dss_objects o (cost=0.43..8.45 rows=1 width=91) (actual time=0.010..0.010 rows=1 loops=52,739)

18. 158.217 158.217 ↓ 0.0 0 52,739

Index Scan using dss_obj_except_idx on mf_winris_central.dss_object_exceptions oe (cost=0.14..8.16 rows=1 width=516) (actual time=0.003..0.003 rows=0 loops=52,739)

19. 158.217 158.217 ↓ 0.0 0 52,739

Nested Loop (cost=0.14..9.19 rows=1 width=516) (actual time=0.003..0.003 rows=0 loops=52,739)

20. 105.478 105.478 ↓ 0.0 0 52,739

Index Scan using vwer_action_idx on mf_winris_central.viewer_action_plans ap (cost=0.14..8.16 rows=1 width=532) (actual time=0.002..0.002 rows=0 loops=52,739)

21. 0.000 0.000 ↓ 0.0 0

Seq Scan on mf_winris_central.dss_snapshots s (cost=0.00..1.01 rows=1 width=8) (never executed)

22. 105.478 105.478 ↓ 0.0 0 52,739

Nested Loop (cost=0.14..9.19 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=52,739)

23. 52.739 52.739 ↓ 0.0 0 52,739

Index Scan using vwer_action_idx on mf_winris_central.viewer_action_plans ap_1 (cost=0.14..8.16 rows=1 width=20) (actual time=0.001..0.001 rows=0 loops=52,739)

24. 0.000 0.000 ↓ 0.0 0

Seq Scan on mf_winris_central.dss_snapshots s_1 (cost=0.00..1.01 rows=1 width=8) (never executed)

25. 105.478 105.478 ↑ 1.0 1 52,739

Index Scan using dss_objects_pk on mf_winris_central.dss_objects o_1 (cost=0.43..8.45 rows=1 width=17) (actual time=0.002..0.002 rows=1 loops=52,739)

26. 316.434 316.434 ↑ 1.0 1 52,739

Index Scan using dss_metr_hty_idx on mf_winris_central.dss_metric_histo_tree dmht_1 (cost=0.28..8.30 rows=1 width=8) (actual time=0.006..0.006 rows=1 loops=52,739)

27. 999,140.355 999,140.355 ↑ 1.0 1 52,739

Bitmap Heap Scan on mf_winris_central.dss_violation_statuses vs (cost=1,424.85..8,016.14 rows=1 width=32) (actual time=11.865..18.945 rows=1 loops=52,739)

28. 216,915.507 216,915.507 ↓ 1.0 52,739 52,739

Bitmap Index Scan on dss_violation_status_idx1 (cost=0.00..1,424.85 rows=50,914 width=0) (actual time=4.113..4.113 rows=52,739 loops=52,739)

29. 1,160.258 1,160.258 ↑ 1.0 1 52,739

Nested Loop Left Join (cost=0.86..16.91 rows=1 width=32) (actual time=0.022..0.022 rows=1 loops=52,739)

30. 738.346 738.346 ↑ 1.0 1 52,739

Index Scan using dss_object_info_idx on mf_winris_central.dss_object_info ocur (cost=0.43..8.45 rows=1 width=8) (actual time=0.014..0.014 rows=1 loops=52,739)

31. 105.478 105.478 ↓ 0.0 0 52,739

Index Scan using dss_object_info_idx on mf_winris_central.dss_object_info oprev (cost=0.43..8.45 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=52,739)