explain.depesz.com

PostgreSQL's explain analyze made readable

Result: DlXH

Settings
# exclusive inclusive rows x rows loops node
1. 10.013 255,571.685 ↓ 0.0 0 1

Update on endtoend_839_central.dss_metric_results dmr (cost=13.43..16.01 rows=1 width=119) (actual time=255,571.685..255,571.685 rows=0 loops=1)

  • Buffers: shared hit=660282 read=3 dirtied=107
2.          

CTE source

3. 17.588 2,218.254 ↓ 2,363.0 2,363 1

Nested Loop (cost=0.00..9.24 rows=1 width=20) (actual time=1.911..2,218.254 rows=2,363 loops=1)

  • Output: source.metric_id, source.metric_value_index, source.metric_num_value, source_child.object_type_id
  • Buffers: shared hit=630756
4. 362.892 2,190.054 ↓ 2,653.0 2,653 1

Nested Loop (cost=0.00..6.71 rows=1 width=20) (actual time=1.893..2,190.054 rows=2,653 loops=1)

  • Output: source_child.object_type_id, source.metric_id, source.metric_value_index, source.metric_num_value
  • Join Filter: (source_child.object_id = source.object_id)
  • Rows Removed by Join Filter: 134582
  • Buffers: shared hit=622772
5. 88.852 88.852 ↓ 45,745.0 45,745 1

Index Scan using dss_metr_res_idx2 on endtoend_839_central.dss_metric_results source (cost=0.00..2.51 rows=1 width=20) (actual time=0.006..88.852 rows=45,745 loops=1)

  • Output: source.metric_id, source.object_id, source.metric_value_index, source.metric_num_value, source.metric_char_value, source.metric_object_id, source.snapshot_id, source.position_id
  • Index Cond: ((source.snapshot_id = 7) AND (source.metric_value_index > (-999999)))
  • Buffers: shared hit=28087
6. 1,052.135 1,738.310 ↓ 3.0 3 45,745

Nested Loop (cost=0.00..4.19 rows=1 width=12) (actual time=0.011..0.038 rows=3 loops=45,745)

  • Output: source_link.next_object_id, source_child.object_type_id, source_child.object_id
  • Buffers: shared hit=594685
7. 274.470 274.470 ↓ 3.0 3 45,745

Index Only Scan using dss_linkall_idx on endtoend_839_central.dss_link_info source_link (cost=0.00..1.87 rows=1 width=4) (actual time=0.003..0.006 rows=3 loops=45,745)

  • Output: source_link.snapshot_id, source_link.previous_object_id, source_link.next_object_id, source_link.link_type_id
  • Index Cond: ((source_link.snapshot_id = 7) AND (source_link.previous_object_id = 12) AND (source_link.link_type_id = 2))
  • Heap Fetches: 137235
  • Buffers: shared hit=182980
8. 411.705 411.705 ↑ 1.0 1 137,235

Index Scan using dss_object_info_idx on endtoend_839_central.dss_object_info source_child (cost=0.00..2.31 rows=1 width=8) (actual time=0.002..0.003 rows=1 loops=137,235)

  • Output: source_child.snapshot_id, source_child.object_id, source_child.object_type_id, source_child.object_checksum
  • Index Cond: ((source_child.object_id = source_link.next_object_id) AND (source_child.snapshot_id = 7))
  • Buffers: shared hit=411705
9. 10.612 10.612 ↑ 1.0 1 2,653

Index Scan using dss_metr_typ_pk on endtoend_839_central.dss_metric_types dmt (cost=0.00..2.53 rows=1 width=8) (actual time=0.003..0.004 rows=1 loops=2,653)

  • Output: dmt.metric_id, dmt.metric_name, dmt.metric_description, dmt.metric_type, dmt.metric_group, dmt.metric_depends_on, dmt.metric_options, dmt.scope_id, dmt.property_id
  • Index Cond: (dmt.metric_id = source.metric_id)
  • Filter: ((dmt.metric_group = ANY ('{0,1,4,5,10,13,15,99}'::integer[])) AND ((dmt.metric_group = ANY ('{1,4,5,10,13,15}'::integer[])) OR ((dmt.metric_group = ANY ('{0,99}'::integer[])) AND (source.metric_value_index <> 2) AND (source.metric_id >= 67010))) AND (((dmt.metric_group = ANY ('{1,4,5,10,13,15}'::integer[])) AND (dmt.metric_group = ANY ('{0,1,4,5,10,13,15,99}'::integer[]))) OR ((dmt.metric_group = ANY ('{0,99}'::integer[])) AND (dmt.metric_group = ANY ('{0,1,4,5,10,13,15,99}'::integer[])))))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=7984
10.          

CTE target

11. 0.023 0.070 ↓ 3.0 3 1

Nested Loop (cost=0.00..4.19 rows=1 width=8) (actual time=0.042..0.070 rows=3 loops=1)

  • Output: target_child.object_id, target_child.object_type_id
  • Buffers: shared hit=13
12. 0.026 0.026 ↓ 3.0 3 1

Index Only Scan using dss_linkall_idx on endtoend_839_central.dss_link_info target_link (cost=0.00..1.87 rows=1 width=4) (actual time=0.022..0.026 rows=3 loops=1)

  • Output: target_link.snapshot_id, target_link.previous_object_id, target_link.next_object_id, target_link.link_type_id
  • Index Cond: ((target_link.snapshot_id = 7) AND (target_link.previous_object_id = 9) AND (target_link.link_type_id = 2))
  • Heap Fetches: 3
  • Buffers: shared hit=4
13. 0.021 0.021 ↑ 1.0 1 3

Index Scan using dss_object_info_idx on endtoend_839_central.dss_object_info target_child (cost=0.00..2.31 rows=1 width=8) (actual time=0.005..0.007 rows=1 loops=3)

  • Output: target_child.snapshot_id, target_child.object_id, target_child.object_type_id, target_child.object_checksum
  • Index Cond: ((target_child.object_id = target_link.next_object_id) AND (target_child.snapshot_id = 7))
  • Buffers: shared hit=9
14. 309.690 255,561.672 ↓ 151.0 151 1

Nested Loop (cost=0.00..2.58 rows=1 width=119) (actual time=3,114.077..255,561.672 rows=151 loops=1)

  • Output: dmr.metric_id, dmr.object_id, dmr.metric_value_index, source.metric_num_value, dmr.metric_char_value, dmr.metric_object_id, dmr.snapshot_id, dmr.position_id, dmr.ctid, source.*, target.*
  • Join Filter: ((source.object_type_id = target.object_type_id) AND (dmr.object_id = target.object_id))
  • Rows Removed by Join Filter: 104801
  • Buffers: shared hit=658856
15. 120,189.031 255,077.062 ↓ 34,984.0 34,984 1

Nested Loop (cost=0.00..2.54 rows=1 width=91) (actual time=2,337.053..255,077.062 rows=34,984 loops=1)

  • Output: dmr.metric_id, dmr.object_id, dmr.metric_value_index, dmr.metric_char_value, dmr.metric_object_id, dmr.snapshot_id, dmr.position_id, dmr.ctid, source.metric_num_value, source.*, source.object_type_id
  • Join Filter: ((dmr.metric_num_value <> source.metric_num_value) AND (dmr.metric_id = source.metric_id) AND (dmr.metric_value_index = source.metric_value_index))
  • Rows Removed by Join Filter: 108060451
  • Buffers: shared hit=658843
16. 397.731 397.731 ↓ 45,745.0 45,745 1

Index Scan using dss_metr_res_idx2 on endtoend_839_central.dss_metric_results dmr (cost=0.00..2.51 rows=1 width=43) (actual time=0.019..397.731 rows=45,745 loops=1)

  • Output: dmr.metric_id, dmr.object_id, dmr.metric_value_index, dmr.metric_char_value, dmr.metric_object_id, dmr.snapshot_id, dmr.position_id, dmr.ctid, dmr.metric_num_value
  • Index Cond: (dmr.snapshot_id = 7)
  • Buffers: shared hit=28087
17. 134,490.300 134,490.300 ↓ 2,363.0 2,363 45,745

CTE Scan on source (cost=0.00..0.02 rows=1 width=64) (actual time=0.001..2.940 rows=2,363 loops=45,745)

  • Output: source.metric_num_value, source.*, source.object_type_id, source.metric_id, source.metric_value_index
  • Buffers: shared hit=630756
18. 174.920 174.920 ↓ 3.0 3 34,984

CTE Scan on target (cost=0.00..0.02 rows=1 width=40) (actual time=0.002..0.005 rows=3 loops=34,984)

  • Output: target.*, target.object_type_id, target.object_id
  • Buffers: shared hit=13