explain.depesz.com

PostgreSQL's explain analyze made readable

Result: SDL7

Settings
# exclusive inclusive rows x rows loops node
1. 5.300 22,046.678 ↓ 0.0 0 1

Insert on pg_temp_3.tmp_artifact_status (cost=19.49..35.59 rows=1 width=20) (actual time=22,046.678..22,046.678 rows=0 loops=1)

  • Buffers: shared hit=33937926, local hit=569 read=4 dirtied=4
2. 482.324 22,041.378 ↓ 561.0 561 1

Nested Loop Semi Join (cost=19.49..35.58 rows=1 width=20) (actual time=23.841..22,041.378 rows=561 loops=1)

  • Output: dmr.object_id, dmr.metric_id, dmr.metric_num_value, 2
  • Join Filter: (dmr.object_id = fli.next_object_id)
  • Rows Removed by Join Filter: 3962329
  • Buffers: shared hit=33937926
3.          

CTE dmr

4. 4.865 37.139 ↓ 2,474.0 2,474 1

Nested Loop Anti Join (cost=4.61..18.92 rows=1 width=16) (actual time=12.315..37.139 rows=2,474 loops=1)

  • Output: dmr_1.metric_id, dmr_1.object_id, dmr_1.metric_num_value
  • Join Filter: (e.object_id = dmr_1.object_id)
  • Buffers: shared hit=37054
5. 32.274 32.274 ↓ 2,474.0 2,474 1

Index Scan using dss_metr_res_idx2 on tstmaint_8324_central.dss_metric_results dmr_1 (cost=0.43..6.20 rows=1 width=16) (actual time=12.294..32.274 rows=2,474 loops=1)

  • Output: dmr_1.metric_id, dmr_1.object_id, dmr_1.metric_value_index, dmr_1.metric_num_value, dmr_1.metric_char_value, dmr_1.metric_object_id, dmr_1.snapshot_id, dmr_1.position_id
  • Index Cond: ((dmr_1.snapshot_id = 16) AND (dmr_1.metric_value_index = 1))
  • Filter: (dmr_1.metric_id = ANY ('{10351,10353,10354}'::integer[]))
  • Rows Removed by Filter: 38075
  • Buffers: shared hit=37053
6. 0.000 0.000 ↓ 0.0 0 2,474

Materialize (cost=4.18..12.66 rows=4 width=4) (actual time=0.000..0.000 rows=0 loops=2,474)

  • Output: e.object_id
  • Buffers: shared hit=1
7. 0.002 0.008 ↓ 0.0 0 1

Bitmap Heap Scan on tstmaint_8324_central.efp_tran_exclusion e (cost=4.18..12.64 rows=4 width=4) (actual time=0.008..0.008 rows=0 loops=1)

  • Output: e.object_id
  • Recheck Cond: (e.snapshot_id = 16)
  • Buffers: shared hit=1
8. 0.006 0.006 ↓ 0.0 0 1

Bitmap Index Scan on efp_tranex_idx (cost=0.00..4.18 rows=4 width=0) (actual time=0.006..0.006 rows=0 loops=1)

  • Index Cond: (e.snapshot_id = 16)
  • Buffers: shared hit=1
9. 42.676 42.676 ↓ 2,474.0 2,474 1

CTE Scan on dmr (cost=0.00..0.02 rows=1 width=16) (actual time=12.332..42.676 rows=2,474 loops=1)

  • Output: dmr.metric_id, dmr.object_id, dmr.metric_num_value
  • Buffers: shared hit=37054
10. 0.000 21,516.378 ↓ 1,602.0 1,602 2,474

Nested Loop (cost=0.57..16.63 rows=1 width=4) (actual time=0.013..8.697 rows=1,602 loops=2,474)

  • Output: fli.next_object_id
  • Buffers: shared hit=33900872
11. 4,661.016 4,661.016 ↓ 3,546.0 3,546 2,474

Index Scan using fp_link_nxt_idx on tstmaint_8324_central.fp_link_info fli (cost=0.29..8.32 rows=1 width=8) (actual time=0.009..1.884 rows=3,546 loops=2,474)

  • Output: fli.snapshot_id, fli.previous_object_id, fli.next_object_id, fli.link_type_id
  • Index Cond: (fli.snapshot_id = 16)
  • Filter: ((fli.link_type_id >= 11003) AND (fli.link_type_id <= 11007))
  • Rows Removed by Filter: 229
  • Buffers: shared hit=7409036
12. 17,545.308 17,545.308 ↓ 0.0 0 8,772,654

Index Scan using efp_traninf_idx on tstmaint_8324_central.efp_tran_info eti (cost=0.28..8.30 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=8,772,654)

  • Output: eti.snapshot_id, eti.previous_snapshot_id, eti.object_id, eti.object_checksum, eti.impact_factor, eti.status, eti.fp_source, eti.prev_object_checksum
  • Index Cond: ((eti.snapshot_id = 16) AND (eti.previous_snapshot_id = 10) AND (eti.object_id = fli.previous_object_id))
  • Filter: (eti.status IS NOT NULL)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=26491836