explain.depesz.com

PostgreSQL's explain analyze made readable

Result: RNmM

Settings
# exclusive inclusive rows x rows loops node
1. 0.007 115.508 ↓ 0.0 0 1

Update on endtoend_839_central.dss_metric_results dmr (cost=140.52..143.16 rows=1 width=119) (actual time=115.508..115.508 rows=0 loops=1)

  • Buffers: shared hit=35899 read=3
2.          

CTE source_child

3. 0.028 0.082 ↓ 3.0 3 1

Nested Loop (cost=0.00..4.99 rows=1 width=8) (actual time=0.022..0.082 rows=3 loops=1)

  • Output: source_child.object_id, source_child.object_type_id
  • Buffers: shared hit=17
4. 0.018 0.018 ↓ 3.0 3 1

Index Only Scan using dss_linkall_idx on endtoend_839_central.dss_link_info source_link (cost=0.00..2.50 rows=1 width=4) (actual time=0.007..0.018 rows=3 loops=1)

  • 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: 3
  • Buffers: shared hit=4
5. 0.036 0.036 ↑ 1.0 1 3

Index Scan using dss_object_info_idx on endtoend_839_central.dss_object_info source_child (cost=0.00..2.48 rows=1 width=8) (actual time=0.010..0.012 rows=1 loops=3)

  • 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=13
6.          

CTE source

7. 16.788 42.360 ↓ 2,363.0 2,363 1

Nested Loop (cost=0.00..130.55 rows=1 width=20) (actual time=1.480..42.360 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=9811
8. 5.738 14.960 ↓ 663.2 2,653 1

Nested Loop (cost=0.00..129.19 rows=4 width=20) (actual time=1.457..14.960 rows=2,653 loops=1)

  • Output: source_child.object_type_id, source.metric_id, source.metric_value_index, source.metric_num_value
  • Buffers: shared hit=1827
9. 0.096 0.096 ↓ 3.0 3 1

CTE Scan on source_child (cost=0.00..0.02 rows=1 width=8) (actual time=0.026..0.096 rows=3 loops=1)

  • Output: source_child.object_id, source_child.object_type_id
  • Buffers: shared hit=17
10. 9.126 9.126 ↓ 221.0 884 3

Index Scan using dss_metr_res_idx on endtoend_839_central.dss_metric_results source (cost=0.00..129.13 rows=4 width=20) (actual time=0.816..3.042 rows=884 loops=3)

  • 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.object_id = source_child.object_id)
  • Filter: ((source.metric_value_index > (-99999)) AND (source.snapshot_id = 7))
  • Rows Removed by Filter: 2717
  • Buffers: shared hit=1810
11. 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..0.33 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
12.          

CTE target

13. 0.031 0.101 ↓ 3.0 3 1

Nested Loop (cost=0.00..4.99 rows=1 width=8) (actual time=0.039..0.101 rows=3 loops=1)

  • Output: target_child.object_id, target_child.object_type_id
  • Buffers: shared hit=16
14. 0.031 0.031 ↓ 3.0 3 1

Index Only Scan using dss_linkall_idx on endtoend_839_central.dss_link_info target_link (cost=0.00..2.50 rows=1 width=4) (actual time=0.022..0.031 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
15. 0.039 0.039 ↑ 1.0 1 3

Index Scan using dss_object_info_idx on endtoend_839_central.dss_object_info target_child (cost=0.00..2.48 rows=1 width=8) (actual time=0.010..0.013 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=12
16. 0.023 115.501 ↓ 0.0 0 1

Nested Loop (cost=0.00..2.64 rows=1 width=119) (actual time=115.501..115.501 rows=0 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.*
  • Buffers: shared hit=35899 read=3
17. 0.128 0.128 ↓ 3.0 3 1

CTE Scan on target (cost=0.00..0.02 rows=1 width=40) (actual time=0.053..0.128 rows=3 loops=1)

  • Output: target.*, target.object_type_id, target.object_id
  • Buffers: shared hit=16
18. 31.911 115.350 ↓ 0.0 0 3

Nested Loop (cost=0.00..2.61 rows=1 width=91) (actual time=38.450..38.450 rows=0 loops=3)

  • 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: (target.object_type_id = source.object_type_id)
  • Rows Removed by Join Filter: 380
  • Buffers: shared hit=35883 read=3
19. 55.083 55.083 ↓ 2,363.0 2,363 3

CTE Scan on source (cost=0.00..0.02 rows=1 width=64) (actual time=0.497..18.361 rows=2,363 loops=3)

  • Output: source.metric_num_value, source.*, source.object_type_id, source.metric_id, source.metric_value_index
  • Buffers: shared hit=9811
20. 28.356 28.356 ↓ 0.0 0 7,089

Index Scan using dss_metr_res_idx2 on endtoend_839_central.dss_metric_results dmr (cost=0.00..2.58 rows=1 width=43) (actual time=0.004..0.004 rows=0 loops=7,089)

  • 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) AND (dmr.metric_id = source.metric_id) AND (dmr.object_id = target.object_id) AND (dmr.metric_value_index = source.metric_value_index))
  • Filter: (dmr.metric_num_value <> source.metric_num_value)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=26072 read=3