explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Aiwq : Optimization for: plan #BRy1

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.524 348,099.389 ↓ 0.0 0 1

Insert on endtoend_840_central.dss_metric_delta_results (cost=36.61..36.64 rows=1 width=552) (actual time=348,099.389..348,099.389 rows=0 loops=1)

  • Buffers: shared hit=26154 read=1 dirtied=1
2. 0.029 348,098.865 ↓ 7.0 7 1

Subquery Scan on *SELECT* (cost=36.61..36.64 rows=1 width=552) (actual time=348,084.938..348,098.865 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=26111
3. 7.223 348,098.836 ↓ 7.0 7 1

GroupAggregate (cost=36.61..36.63 rows=1 width=28) (actual time=348,084.931..348,098.836 rows=7 loops=1)

  • Output: t.object_id, dss_metric_delta_results_1.metric_id, 2, sum(dss_metric_delta_results_1.metric_num_value), 3, '-1'::integer
  • Group Key: t.object_id, dss_metric_delta_results_1.metric_id
  • Buffers: shared hit=26111
4. 34.538 348,091.613 ↓ 5,781.0 5,781 1

Sort (cost=36.61..36.62 rows=1 width=16) (actual time=348,084.844..348,091.613 rows=5,781 loops=1)

  • Output: t.object_id, dss_metric_delta_results_1.metric_id, dss_metric_delta_results_1.metric_num_value
  • Sort Key: t.object_id, dss_metric_delta_results_1.metric_id
  • Sort Method: quicksort Memory: 463kB
  • Buffers: shared hit=26111
5. 58,496.315 348,057.075 ↓ 5,781.0 5,781 1

Nested Loop (cost=30.70..36.60 rows=1 width=16) (actual time=49,135.389..348,057.075 rows=5,781 loops=1)

  • Output: t.object_id, dss_metric_delta_results_1.metric_id, dss_metric_delta_results_1.metric_num_value
  • Join Filter: (l.next_object_id = dss_metric_delta_results_1.object_id)
  • Rows Removed by Join Filter: 48537779
  • Buffers: shared hit=26111
6. 71.430 71.430 ↓ 5,590.0 5,590 1

Index Scan using dss_metr_dres_idx2 on endtoend_840_central.dss_metric_delta_results dss_metric_delta_results_1 (cost=0.41..6.19 rows=1 width=16) (actual time=0.017..71.430 rows=5,590 loops=1)

  • Output: dss_metric_delta_results_1.metric_id, dss_metric_delta_results_1.object_id, dss_metric_delta_results_1.metric_value_index, dss_metric_delta_results_1.metric_num_value, dss_metric_delta_results_1.metric_char_value, dss_metric_delta_results_1.metric_object_id, dss_metric_delta_results_1.snapshot_id, dss_metric_delta_results_1.previous_snapshot_id, dss_metric_delta_results_1.position_id
  • Index Cond: ((dss_metric_delta_results_1.snapshot_id = 3) AND (dss_metric_delta_results_1.previous_snapshot_id = '-1'::integer) AND (dss_metric_delta_results_1.metric_value_index = 1))
  • Filter: (dss_metric_delta_results_1.metric_id = ANY ('{10351,10353,10354}'::integer[]))
  • Buffers: shared hit=83
7. 173,418.570 289,489.330 ↓ 2,171.0 8,684 5,590

Unique (cost=30.29..30.32 rows=4 width=8) (actual time=0.046..51.787 rows=8,684 loops=5,590)

  • Output: t.object_id, l.next_object_id
  • Buffers: shared hit=26028
8. 115,867.255 116,070.760 ↓ 4,351.0 17,404 5,590

Sort (cost=30.29..30.30 rows=4 width=8) (actual time=0.043..20.764 rows=17,404 loops=5,590)

  • 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=26028
9. 94.649 203.505 ↓ 4,351.0 17,404 1

Nested Loop Anti Join (cost=0.57..30.25 rows=4 width=8) (actual time=0.060..203.505 rows=17,404 loops=1)

  • Output: t.object_id, l.next_object_id
  • Buffers: shared hit=26028
10. 40.521 74.048 ↓ 1,933.8 17,404 1

Nested Loop (cost=0.42..28.66 rows=9 width=8) (actual time=0.051..74.048 rows=17,404 loops=1)

  • Output: t.object_id, l.next_object_id
  • Buffers: shared hit=8624
11. 0.060 0.060 ↓ 7.0 7 1

Seq Scan on endtoend_840_central.efp_tran_info t (cost=0.00..5.69 rows=1 width=4) (actual time=0.024..0.060 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
  • Filter: ((t.status IS NOT NULL) AND (t.snapshot_id = 3) AND (t.previous_snapshot_id = '-1'::integer))
  • Rows Removed by Filter: 184
  • Buffers: shared hit=3
12. 33.467 33.467 ↓ 276.2 2,486 7

Index Only Scan using dss_linkall_idx on endtoend_840_central.dss_link_info l (cost=0.42..22.88 rows=9 width=8) (actual time=0.009..4.781 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=8621
13. 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