explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 8e4G

Settings
# exclusive inclusive rows x rows loops node
1. 50.087 7,448,369.433 ↓ 0.0 0 1

Insert on pg_temp_3.tmp_apmaep_object_status (cost=15.23..47.22 rows=1 width=12) (actual time=7,448,369.433..7,448,369.433 rows=0 loops=1)

  • Buffers: shared hit=24808930, local hit=3481 read=21 dirtied=19
2. 34.228 7,448,319.346 ↓ 3,448.0 3,448 1

GroupAggregate (cost=15.23..47.21 rows=1 width=16) (actual time=14,843.933..7,448,319.346 rows=3,448 loops=1)

  • Output: dl3.next_object_id, min(CASE WHEN (prev.object_checksum IS NULL) THEN 1 WHEN (prev.object_checksum <> cur.object_checksum) THEN 3 ELSE 4 END), min(COALESCE(dt.techno_type_id, 0))
  • Buffers: shared hit=24808930
3. 47.237 7,448,285.118 ↓ 3,464.0 3,464 1

Nested Loop Left Join (cost=15.23..47.19 rows=1 width=16) (actual time=12,716.893..7,448,285.118 rows=3,464 loops=1)

  • Output: dl3.next_object_id, dt.techno_type_id, cur.object_checksum, prev.object_checksum
  • Buffers: shared hit=24808930
4. 15,205.534 7,448,168.601 ↓ 3,464.0 3,464 1

Nested Loop (cost=15.23..44.70 rows=1 width=12) (actual time=12,716.864..7,448,168.601 rows=3,464 loops=1)

  • Output: dl3.next_object_id, dt.techno_type_id, cur.object_checksum
  • Join Filter: (dl3.next_object_id = cur.object_id)
  • Rows Removed by Join Filter: 12182888
  • Buffers: shared hit=24798506
5. 16.573 16.573 ↓ 3,518.0 3,518 1

Index Scan using dss_object_info_idx on tstmaint_8322_central.dss_object_info cur (cost=0.00..2.47 rows=1 width=8) (actual time=0.012..16.573 rows=3,518 loops=1)

  • Output: cur.snapshot_id, cur.object_id, cur.object_type_id, cur.object_checksum
  • Index Cond: (cur.snapshot_id = 13)
  • Buffers: shared hit=108
6. 46,018.958 7,432,946.494 ↓ 314.9 3,464 3,518

Merge Join (cost=15.23..42.10 rows=11 width=8) (actual time=0.341..2,112.833 rows=3,464 loops=3,518)

  • Output: dl3.next_object_id, dt.techno_type_id
  • Merge Cond: (dl.object_id = dl2.module_id)
  • Buffers: shared hit=24798398
7. 455,514.158 7,372,123.792 ↓ 314.9 3,464 3,518

Nested Loop (cost=1.86..44.02 rows=11 width=20) (actual time=0.330..2,095.544 rows=3,464 loops=3,518)

  • Output: dl3.next_object_id, dl3.previous_object_id, dl.object_id, dl.module_id, dt.techno_type_id
  • Join Filter: (dl.object_id = dl3.previous_object_id)
  • Rows Removed by Join Filter: 37096
  • Buffers: shared hit=24798390
8. 43,704.114 43,704.114 ↓ 6,760.0 6,760 3,518

Index Only Scan using dss_linkall_idx on tstmaint_8322_central.dss_link_info dl3 (cost=0.00..2.49 rows=1 width=8) (actual time=0.021..12.423 rows=6,760 loops=3,518)

  • Output: dl3.snapshot_id, dl3.previous_object_id, dl3.next_object_id, dl3.link_type_id
  • Index Cond: ((dl3.snapshot_id = 13) AND (dl3.link_type_id = 3))
  • Heap Fetches: 23781680
  • Buffers: shared hit=1016702
9. 3,638,596.999 6,872,905.520 ↑ 1.8 6 23,781,680

Hash Right Join (cost=1.86..41.40 rows=11 width=12) (actual time=0.094..0.289 rows=6 loops=23,781,680)

  • Output: dl.object_id, dl.module_id, dt.techno_type_id
  • Hash Cond: (dt.module_type_id = dl.object_type_id)
  • Buffers: shared hit=23781688
10. 3,234,308.480 3,234,308.480 ↑ 18.6 115 23,781,680

Seq Scan on tstmaint_8322_central.dss_technologies dt (cost=0.00..31.40 rows=2,140 width=8) (actual time=0.003..0.136 rows=115 loops=23,781,680)

  • Output: dt.module_type_id, dt.techno_type_id
  • Buffers: shared hit=23781680
11. 0.012 0.041 ↓ 6.0 6 1

Hash (cost=1.85..1.85 rows=1 width=12) (actual time=0.041..0.041 rows=6 loops=1)

  • Output: dl.object_id, dl.object_type_id, dl.module_id
  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
  • Buffers: shared hit=8
12. 0.029 0.029 ↓ 6.0 6 1

Index Scan using dss_mod_lnk_idx2 on tstmaint_8322_central.dss_module_links dl (cost=0.00..1.85 rows=1 width=12) (actual time=0.017..0.029 rows=6 loops=1)

  • Output: dl.object_id, dl.object_type_id, dl.module_id
  • Index Cond: (dl.snapshot_id = 13)
  • Filter: (dl.object_id = dl.module_id)
  • Rows Removed by Filter: 34
  • Buffers: shared hit=8
13. 14,803.727 14,803.744 ↓ 3,441.0 3,441 3,518

Sort (cost=1.86..1.87 rows=1 width=4) (actual time=0.002..4.208 rows=3,441 loops=3,518)

  • Output: dl2.module_id
  • Sort Key: dl2.module_id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=8
14. 0.017 0.017 ↓ 6.0 6 1

Index Scan using dss_mod_lnk_idx2 on tstmaint_8322_central.dss_module_links dl2 (cost=0.00..1.85 rows=1 width=4) (actual time=0.005..0.017 rows=6 loops=1)

  • Output: dl2.module_id
  • Index Cond: (dl2.snapshot_id = 13)
  • Filter: (dl2.object_type_id = 20000)
  • Rows Removed by Filter: 34
  • Buffers: shared hit=8
15. 69.280 69.280 ↑ 1.0 1 3,464

Index Scan using dss_object_info_idx on tstmaint_8322_central.dss_object_info prev (cost=0.00..2.47 rows=1 width=8) (actual time=0.019..0.020 rows=1 loops=3,464)

  • Output: prev.snapshot_id, prev.object_id, prev.object_type_id, prev.object_checksum
  • Index Cond: ((prev.snapshot_id = 9) AND (prev.object_id = dl3.next_object_id))
  • Buffers: shared hit=10424