explain.depesz.com

PostgreSQL's explain analyze made readable

Result: BRy1

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.460 118,041.593 ↓ 0.0 0 1

Insert on endtoend_840_central.dss_metric_delta_results (cost=39.39..39.43 rows=1 width=552) (actual time=118,041.593..118,041.593 rows=0 loops=1)

  • Buffers: shared hit=26157 read=1 dirtied=2
2.          

CTE tr

3. 37.859 287.992 ↓ 2,171.0 8,684 1

Unique (cost=30.78..30.81 rows=4 width=8) (actual time=223.694..287.992 rows=8,684 loops=1)

  • Output: t.object_id, l.next_object_id
  • Buffers: shared hit=26030
4. 51.545 250.133 ↓ 4,351.0 17,404 1

Sort (cost=30.78..30.79 rows=4 width=8) (actual time=223.689..250.133 rows=17,404 loops=1)

  • Output: t.object_id, l.next_object_id
  • Sort Key: t.object_id, l.next_object_id
  • Sort Method: quicksort Memory: 1584kB
  • Buffers: shared hit=26030
5. 93.069 198.588 ↓ 4,351.0 17,404 1

Nested Loop Anti Join (cost=0.84..30.74 rows=4 width=8) (actual time=0.044..198.588 rows=17,404 loops=1)

  • Output: t.object_id, l.next_object_id
  • Buffers: shared hit=26030
6. 39.813 70.711 ↓ 1,933.8 17,404 1

Nested Loop (cost=0.69..29.15 rows=9 width=8) (actual time=0.033..70.711 rows=17,404 loops=1)

  • Output: t.object_id, l.next_object_id
  • Buffers: shared hit=8626
7. 0.035 0.035 ↓ 7.0 7 1

Index Scan using efp_traninf_idx on endtoend_840_central.efp_tran_info t (cost=0.27..6.16 rows=1 width=4) (actual time=0.009..0.035 rows=7 loops=1)

  • Output: t.snapshot_id, t.previous_snapshot_id, t.object_id, t.object_checksum, t.impact_factor, t.status, t.fp_source, t.prev_object_checksum
  • Index Cond: ((t.snapshot_id = 3) AND (t.previous_snapshot_id = '-1'::integer))
  • Filter: (t.status IS NOT NULL)
  • Rows Removed by Filter: 5
  • Buffers: shared hit=3
8. 30.863 30.863 ↓ 276.2 2,486 7

Index Only Scan using dss_linkall_idx on endtoend_840_central.dss_link_info l (cost=0.42..22.90 rows=9 width=8) (actual time=0.012..4.409 rows=2,486 loops=7)

  • Output: l.snapshot_id, l.previous_object_id, l.next_object_id, l.link_type_id
  • Index Cond: ((l.snapshot_id = 3) AND (l.previous_object_id = t.object_id) AND (l.link_type_id >= 11003) AND (l.link_type_id <= 11006))
  • Heap Fetches: 17404
  • Buffers: shared hit=8623
9. 34.808 34.808 ↓ 0.0 0 17,404

Index Only Scan using efp_tranex_idx on endtoend_840_central.efp_tran_exclusion e (cost=0.15..0.17 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=17,404)

  • Output: e.snapshot_id, e.object_id, e.exclude_type
  • Index Cond: ((e.snapshot_id = 3) AND (e.object_id = l.next_object_id))
  • Heap Fetches: 0
  • Buffers: shared hit=17404
10. 0.029 118,041.133 ↓ 7.0 7 1

Subquery Scan on *SELECT* (cost=8.58..8.61 rows=1 width=552) (actual time=118,027.196..118,041.133 rows=7 loops=1)

  • Output: "*SELECT*".metric_id, "*SELECT*".object_id, "*SELECT*".i_metric_value_index, "*SELECT*".sum, 'NULL'::character varying(1000), 0, "*SELECT*".i_snapshot_id, "*SELECT*".l_prev_snapshot_id, 0
  • Buffers: shared hit=26114
11. 7.161 118,041.104 ↓ 7.0 7 1

GroupAggregate (cost=8.58..8.60 rows=1 width=28) (actual time=118,027.189..118,041.104 rows=7 loops=1)

  • Output: tr.object_id, dmr.metric_id, 2, sum(dmr.metric_num_value), 3, '-1'::integer
  • Group Key: tr.object_id, dmr.metric_id
  • Buffers: shared hit=26114
12. 34.411 118,033.943 ↓ 5,781.0 5,781 1

Sort (cost=8.58..8.59 rows=1 width=16) (actual time=118,027.118..118,033.943 rows=5,781 loops=1)

  • Output: tr.object_id, dmr.metric_id, dmr.metric_num_value
  • Sort Key: tr.object_id, dmr.metric_id
  • Sort Method: quicksort Memory: 463kB
  • Buffers: shared hit=26114
13. 58,612.668 117,999.532 ↓ 5,781.0 5,781 1

Nested Loop (cost=0.41..8.57 rows=1 width=16) (actual time=224.665..117,999.532 rows=5,781 loops=1)

  • Output: tr.object_id, dmr.metric_id, dmr.metric_num_value
  • Join Filter: (tr.next_object_id = dmr.object_id)
  • Rows Removed by Join Filter: 48537779
  • Buffers: shared hit=26114
14. 60.194 60.194 ↓ 5,590.0 5,590 1

Index Scan using dss_metr_dres_idx2 on endtoend_840_central.dss_metric_delta_results dmr (cost=0.41..8.44 rows=1 width=16) (actual time=0.018..60.194 rows=5,590 loops=1)

  • Output: dmr.metric_id, dmr.object_id, dmr.metric_value_index, dmr.metric_num_value, dmr.metric_char_value, dmr.metric_object_id, dmr.snapshot_id, dmr.previous_snapshot_id, dmr.position_id
  • Index Cond: ((dmr.snapshot_id = 3) AND (dmr.previous_snapshot_id = '-1'::integer) AND (dmr.metric_value_index = 1))
  • Filter: (dmr.metric_id = ANY ('{10351,10353,10354}'::integer[]))
  • Buffers: shared hit=84
15. 59,326.670 59,326.670 ↓ 2,171.0 8,684 5,590

CTE Scan on tr (cost=0.00..0.08 rows=4 width=8) (actual time=0.042..10.613 rows=8,684 loops=5,590)

  • Output: tr.object_id, tr.next_object_id
  • Buffers: shared hit=26030