explain.depesz.com

PostgreSQL's explain analyze made readable

Result: dD4K

Settings
# exclusive inclusive rows x rows loops node
1. 0.006 61.696 ↓ 0.0 0 1

Update on endtoend_839_central.dss_metric_results dmr (cost=19.22..21.86 rows=1 width=119) (actual time=61.696..61.696 rows=0 loops=1)

  • Buffers: shared hit=18552
2.          

CTE source

3. 8.246 26.134 ↓ 1,119.0 1,119 1

Nested Loop (cost=0.00..14.23 rows=1 width=20) (actual time=2.255..26.134 rows=1,119 loops=1)

  • Output: source.metric_id, source.metric_value_index, source.metric_num_value, source_child.object_type_id
  • Buffers: shared hit=6063
4. 2.943 12.252 ↓ 1,409.0 1,409 1

Nested Loop (cost=0.00..13.89 rows=1 width=20) (actual time=2.212..12.252 rows=1,409 loops=1)

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

Nested Loop (cost=0.00..4.99 rows=1 width=12) (actual time=0.019..0.078 rows=3 loops=1)

  • Output: source_link.next_object_id, source_child.object_type_id, source_child.object_id
  • Buffers: shared hit=17
6. 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.008..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
7. 0.033 0.033 ↑ 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.008..0.011 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
8. 9.231 9.231 ↓ 117.5 470 3

Index Scan using dss_metr_res_idx on endtoend_839_central.dss_metric_results source (cost=0.00..8.86 rows=4 width=20) (actual time=1.073..3.077 rows=470 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 > (-1)) AND (source.snapshot_id = 7))
  • Rows Removed by Filter: 3132
  • Buffers: shared hit=1810
9. 5.636 5.636 ↑ 1.0 1 1,409

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=1,409)

  • 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=4236
10.          

CTE target

11. 0.028 0.248 ↓ 3.0 3 1

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

  • Output: target_child.object_id, target_child.object_type_id
  • Buffers: shared hit=17
12. 0.178 0.178 ↓ 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.169..0.178 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=5
13. 0.042 0.042 ↑ 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.011..0.014 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
14. 0.025 61.690 ↓ 0.0 0 1

Nested Loop (cost=0.00..2.64 rows=1 width=119) (actual time=61.690..61.690 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=18552
15. 0.285 0.285 ↓ 3.0 3 1

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

  • Output: target.*, target.object_type_id, target.object_id
  • Buffers: shared hit=17
16. 16.032 61.380 ↓ 0.0 0 3

Nested Loop (cost=0.00..2.61 rows=1 width=91) (actual time=20.460..20.460 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: 254
  • Buffers: shared hit=18535
17. 31.920 31.920 ↓ 1,119.0 1,119 3

CTE Scan on source (cost=0.00..0.02 rows=1 width=64) (actual time=0.756..10.640 rows=1,119 loops=3)

  • Output: source.metric_num_value, source.*, source.object_type_id, source.metric_id, source.metric_value_index
  • Buffers: shared hit=6063
18. 13.428 13.428 ↓ 0.0 0 3,357

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=3,357)

  • 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: 0
  • Buffers: shared hit=12472