explain.depesz.com

PostgreSQL's explain analyze made readable

Result: dX7M

Settings
# exclusive inclusive rows x rows loops node
1. 0.046 1.171 ↓ 0.0 0 1

Sort (cost=97.71..97.72 rows=2 width=32) (actual time=1.171..1.171 rows=0 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, ((SubPlan 1))
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=125
2. 0.001 1.125 ↓ 0.0 0 1

Nested Loop (cost=24.82..97.70 rows=2 width=32) (actual time=1.125..1.125 rows=0 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)
  • Join Filter: (dmr.object_id = sr.object_id)
  • Buffers: shared hit=125
3. 0.025 1.124 ↓ 0.0 0 1

Hash Semi Join (cost=24.82..25.05 rows=4 width=12) (actual time=1.124..1.124 rows=0 loops=1)

  • Output: dmr.object_id, dmr.metric_id, li.next_object_id
  • Hash Cond: (dmr.object_id = li.next_object_id)
  • Buffers: shared hit=125
4. 0.003 0.085 ↓ 0.0 0 1

HashAggregate (cost=15.24..15.32 rows=8 width=12) (actual time=0.085..0.085 rows=0 loops=1)

  • Output: dmr.object_id, dmr.metric_id, dmr.snapshot_id
  • Buffers: shared hit=6
5. 0.015 0.082 ↓ 0.0 0 1

Nested Loop (cost=0.00..15.18 rows=8 width=12) (actual time=0.082..0.082 rows=0 loops=1)

  • Output: dmr.object_id, dmr.metric_id, dmr.snapshot_id
  • Buffers: shared hit=6
6. 0.005 0.039 ↑ 1.0 1 1

Unique (cost=0.00..4.28 rows=1 width=4) (actual time=0.036..0.039 rows=1 loops=1)

  • Output: dmht.metric_id
  • Buffers: shared hit=3
7. 0.034 0.034 ↑ 1.0 1 1

Index Only Scan using dss_metr_hty_idx on db_optimizer_central.dss_metric_histo_tree dmht (cost=0.00..4.27 rows=1 width=4) (actual time=0.033..0.034 rows=1 loops=1)

  • Output: dmht.metric_id
  • Index Cond: ((dmht.snapshot_id = 1) AND (dmht.metric_parent_id = 7688))
  • Heap Fetches: 1
  • Buffers: shared hit=3
8. 0.028 0.028 ↓ 0.0 0 1

Index Only Scan using dss_metr_res_idx2 on db_optimizer_central.dss_metric_results dmr (cost=0.00..10.82 rows=8 width=12) (actual time=0.028..0.028 rows=0 loops=1)

  • 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: 0
  • Buffers: shared hit=3
9. 0.284 1.014 ↓ 64.8 259 1

Hash (cost=9.53..9.53 rows=4 width=4) (actual time=1.014..1.014 rows=259 loops=1)

  • Output: li.next_object_id
  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
  • Buffers: shared hit=119
10. 0.146 0.730 ↓ 64.8 259 1

Nested Loop (cost=2.44..9.53 rows=4 width=4) (actual time=0.247..0.730 rows=259 loops=1)

  • Output: li.next_object_id
  • Buffers: shared hit=119
11. 0.019 0.118 ↓ 2.0 2 1

HashAggregate (cost=2.44..2.45 rows=1 width=4) (actual time=0.116..0.118 rows=2 loops=1)

  • Output: dml.module_id
  • Buffers: shared hit=1
12. 0.099 0.099 ↑ 1.0 2 1

Seq Scan on db_optimizer_central.dss_module_links dml (cost=0.00..2.44 rows=2 width=4) (actual time=0.043..0.099 rows=2 loops=1)

  • Output: dml.module_id
  • Filter: ((dml.object_id = 3) AND (dml.snapshot_id = 1))
  • Rows Removed by Filter: 94
  • Buffers: shared hit=1
13. 0.466 0.466 ↓ 32.5 130 2

Index Only Scan using dss_linkall_idx on db_optimizer_central.dss_link_info li (cost=0.00..7.03 rows=4 width=8) (actual time=0.056..0.233 rows=130 loops=2)

  • 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: 259
  • Buffers: shared hit=118
14. 0.000 0.000 ↓ 0.0 0

Index Scan using dss_snapshot_ranking_idx2 on db_optimizer_central.dss_snapshot_ranking sr (cost=0.00..0.94 rows=1 width=28) (never executed)

  • 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))
15.          

SubPlan (forNested Loop)

16. 0.000 0.000 ↓ 0.0 0

Index Scan using dss_objects_pk on db_optimizer_central.dss_objects o (cost=0.00..4.27 rows=1 width=107) (never executed)

  • Output: o.object_full_name
  • Index Cond: (o.object_id = dmr.object_id)
17. 0.000 0.000 ↓ 0.0 0

Index Scan using dss_obj_except_idx on db_optimizer_central.dss_object_exceptions oe (cost=0.00..4.27 rows=1 width=516) (never executed)

  • Output: oe.justify
  • Index Cond: ((oe.metric_id = 7688) AND (oe.object_id = dmr.object_id))
  • Filter: (oe.last_snapshot_id > 1)
18. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.00..2.31 rows=1 width=19) (never executed)

  • Output: ap.action_def
  • Join Filter: ((s.functional_date >= ap.first_snapshot_date) AND (s.functional_date <= ap.last_snapshot_date))
19. 0.000 0.000 ↓ 0.0 0

Seq Scan on db_optimizer_central.viewer_action_plans ap (cost=0.00..1.14 rows=1 width=35) (never executed)

  • 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
  • Filter: ((ap.object_id = dmr.object_id) AND (ap.metric_id = 7688))
20. 0.000 0.000 ↓ 0.0 0

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

  • 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)
21. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.00..2.31 rows=1 width=4) (never executed)

  • Output: ap.priority
  • Join Filter: ((s.functional_date >= ap.first_snapshot_date) AND (s.functional_date <= ap.last_snapshot_date))
22. 0.000 0.000 ↓ 0.0 0

Seq Scan on db_optimizer_central.viewer_action_plans ap (cost=0.00..1.14 rows=1 width=20) (never executed)

  • 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
  • Filter: ((ap.object_id = dmr.object_id) AND (ap.metric_id = 7688))
23. 0.000 0.000 ↓ 0.0 0

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

  • 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)
24. 0.000 0.000 ↓ 0.0 0

Index Scan using dss_objects_pk on db_optimizer_central.dss_objects o (cost=0.00..4.27 rows=1 width=16) (never executed)

  • Output: o.object_name
  • Index Cond: (o.object_id = dmr.object_id)
25. 0.000 0.000 ↓ 0.0 0

Index Scan using dss_metr_hty_idx on db_optimizer_central.dss_metric_histo_tree dmht (cost=0.00..4.28 rows=1 width=8) (never executed)

  • Output: dmht.aggregate_weight
  • Index Cond: ((dmht.snapshot_id = 1) AND (dmht.metric_parent_id = 7688) AND (dmht.metric_id = dmr.metric_id))
26. 0.000 0.000 ↓ 0.0 0

Index Scan using dss_violation_status_idx1 on db_optimizer_central.dss_violation_statuses vs (cost=0.00..4.27 rows=1 width=4) (never executed)

  • Output: CASE vs.violation_status WHEN 1 THEN 'Added'::text WHEN 2 THEN 'Deleted'::text WHEN 3 THEN 'Updated'::text ELSE 'Unchanged'::text END
  • Index Cond: ((vs.diag_id = 7688) AND (vs.snapshot_id = 1) AND (vs.previous_snapshot_id = (-1)))
  • Filter: (vs.object_id = dmr.object_id)
27. 0.000 0.000 ↓ 0.0 0

Nested Loop Left Join (cost=0.00..8.45 rows=1 width=8) (never executed)

  • 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)
28. 0.000 0.000 ↓ 0.0 0

Index Scan using dss_object_info_idx on db_optimizer_central.dss_object_info ocur (cost=0.00..4.27 rows=1 width=8) (never executed)

  • 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))
29. 0.000 0.000 ↓ 0.0 0

Index Scan using dss_object_info_idx on db_optimizer_central.dss_object_info oprev (cost=0.00..4.16 rows=1 width=8) (never executed)

  • 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)))