explain.depesz.com

PostgreSQL's explain analyze made readable

Result: rJ8J

Settings
# exclusive inclusive rows x rows loops node
1. 280.151 1,249,857.669 ↓ 0.0 0 1

Update on opus_c_central.efp_tran_info (cost=80,227,898.31..82,028,296.08 rows=34,857,807 width=82) (actual time=1,249,857.669..1,249,857.669 rows=0 loops=1)

  • Buffers: shared hit=68,272 read=1,958,088 dirtied=307 written=97, local hit=41, temp read=4,102,446 written=2,905,238
2.          

CTE objectschecksum

3. 37,053.363 1,249,470.515 ↑ 3,851.8 9,143 1

GroupAggregate (cost=78,613,788.73..80,226,793.83 rows=35,217,424 width=16) (actual time=1,121,573.318..1,249,470.515 rows=9,143 loops=1)

  • Output: l.previous_object_id, ((tmp.object_checksum + COALESCE(sum(o.object_checksum), '0'::bigint)) % '2147483648'::bigint), tmp.object_checksum
  • Group Key: l.previous_object_id, tmp.object_checksum
  • Buffers: shared hit=47 read=1,957,710 written=97, local hit=41, temp read=4,102,446 written=2,905,238
4. 427,523.109 1,212,417.152 ↓ 2.2 235,892,874 1

Sort (cost=78,613,788.73..78,884,974.67 rows=108,474,374 width=12) (actual time=1,121,573.286..1,212,417.152 rows=235,892,874 loops=1)

  • Output: l.previous_object_id, tmp.object_checksum, o.object_checksum
  • Sort Key: l.previous_object_id, tmp.object_checksum
  • Sort Method: external merge Disk: 5,073,024kB
  • Buffers: shared hit=47 read=1,957,710 written=97, local hit=41, temp read=4,102,446 written=2,905,238
5. 90,936.247 784,894.043 ↓ 2.2 235,892,874 1

Hash Join (cost=56,120,540.63..60,428,749.26 rows=108,474,374 width=12) (actual time=450,758.549..784,894.043 rows=235,892,874 loops=1)

  • Output: l.previous_object_id, tmp.object_checksum, o.object_checksum
  • Hash Cond: (l.previous_object_id = tmp.object_id)
  • Buffers: shared hit=47 read=1,957,710 written=97, local hit=41, temp read=2,524,370 written=1,327,162
6. 77,662.164 693,955.130 ↓ 2.2 235,892,874 1

Merge Join (cost=56,120,293.91..58,936,979.90 rows=108,474,374 width=8) (actual time=450,755.852..693,955.130 rows=235,892,874 loops=1)

  • Output: l.previous_object_id, o.object_checksum
  • Merge Cond: (l.next_object_id = o.object_id)
  • Buffers: shared hit=47 read=1,957,710 written=97, temp read=2,524,370 written=1,327,162
7. 414,310.391 577,031.557 ↓ 1.0 239,047,033 1

Sort (cost=56,005,989.10..56,601,709.04 rows=238,287,976 width=8) (actual time=449,335.370..577,031.557 rows=239,047,033 loops=1)

  • Output: l.previous_object_id, l.next_object_id
  • Sort Key: l.next_object_id
  • Sort Method: external merge Disk: 4,216,424kB
  • Buffers: shared hit=47 read=1,949,954 written=97, temp read=1,320,881 written=1,322,041
8. 135,679.906 162,721.166 ↓ 1.0 239,615,303 1

Bitmap Heap Scan on opus_c_central.fp_link_info l (cost=4,620,532.44..16,334,753.15 rows=238,287,976 width=8) (actual time=27,190.776..162,721.166 rows=239,615,303 loops=1)

  • Output: l.previous_object_id, l.next_object_id
  • Recheck Cond: (l.snapshot_id = 13)
  • Rows Removed by Index Recheck: 19
  • Filter: ((l.link_type_id >= 11,003) AND (l.link_type_id <= 11,007))
  • Rows Removed by Filter: 11,196
  • Heap Blocks: exact=279,869 lossy=1,015,410
  • Buffers: shared hit=47 read=1,949,954 written=97
9. 27,041.260 27,041.260 ↓ 1.0 239,626,499 1

Bitmap Index Scan on fp_link_nxt_idx (cost=0.00..4,560,960.45 rows=238,287,983 width=0) (actual time=27,041.260..27,041.260 rows=239,626,499 loops=1)

  • Index Cond: (l.snapshot_id = 13)
  • Buffers: shared read=654,722
10. 38,572.483 39,261.409 ↓ 496.3 236,226,949 1

Sort (cost=113,392.00..114,581.85 rows=475,938 width=8) (actual time=1,420.232..39,261.409 rows=236,226,949 loops=1)

  • Output: o.object_checksum, o.object_id
  • Sort Key: o.object_id
  • Sort Method: external sort Disk: 20,496kB
  • Buffers: shared read=7,756, temp read=604,305 written=5,121
11. 220.990 688.926 ↓ 2.0 952,549 1

Hash Anti Join (cost=18,342.16..68,510.06 rows=475,938 width=8) (actual time=138.772..688.926 rows=952,549 loops=1)

  • Output: o.object_checksum, o.object_id
  • Hash Cond: (o.object_id = e.object_id)
  • Buffers: shared read=7,756
12. 330.763 467.522 ↓ 1.0 952,549 1

Bitmap Heap Scan on opus_c_central.dss_object_info o (cost=18,329.46..60,703.90 rows=951,875 width=8) (actual time=138.329..467.522 rows=952,549 loops=1)

  • Output: o.snapshot_id, o.object_id, o.object_type_id, o.object_checksum
  • Recheck Cond: (o.snapshot_id = 13)
  • Heap Blocks: exact=5,149
  • Buffers: shared read=7,755
13. 136.759 136.759 ↓ 1.0 952,549 1

Bitmap Index Scan on dss_object_info_idx (cost=0.00..18,091.49 rows=951,875 width=0) (actual time=136.759..136.759 rows=952,549 loops=1)

  • Index Cond: (o.snapshot_id = 13)
  • Buffers: shared read=2,606
14. 0.001 0.414 ↓ 0.0 0 1

Hash (cost=12.64..12.64 rows=4 width=4) (actual time=0.414..0.414 rows=0 loops=1)

  • Output: e.object_id
  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
  • Buffers: shared read=1
15. 0.002 0.413 ↓ 0.0 0 1

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

  • Output: e.object_id
  • Recheck Cond: (e.snapshot_id = 14)
  • Buffers: shared read=1
16. 0.411 0.411 ↓ 0.0 0 1

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

  • Index Cond: (e.snapshot_id = 14)
  • Buffers: shared read=1
17. 1.399 2.666 ↑ 1.0 9,143 1

Hash (cost=132.43..132.43 rows=9,143 width=8) (actual time=2.666..2.666 rows=9,143 loops=1)

  • Output: tmp.object_checksum, tmp.object_id
  • Buckets: 16,384 Batches: 1 Memory Usage: 486kB
  • Buffers: local hit=41
18. 1.267 1.267 ↑ 1.0 9,143 1

Seq Scan on pg_temp_4.tmp_prev_trans_info tmp (cost=0.00..132.43 rows=9,143 width=8) (actual time=0.015..1.267 rows=9,143 loops=1)

  • Output: tmp.object_checksum, tmp.object_id
  • Buffers: local hit=41
19. 28.723 1,249,577.518 ↑ 3,812.5 9,143 1

Hash Join (cost=1,104.48..1,801,502.25 rows=34,857,807 width=82) (actual time=1,121,614.820..1,249,577.518 rows=9,143 loops=1)

  • Output: efp_tran_info.snapshot_id, efp_tran_info.previous_snapshot_id, efp_tran_info.object_id, efp_tran_info.object_checksum, efp_tran_info.impact_factor, efp_tran_info.status, efp_tran_info.fp_source, oc.checksum, efp_tran_info.ctid, oc.*
  • Hash Cond: (oc.object_id = efp_tran_info.object_id)
  • Buffers: shared hit=47 read=1,958,005 written=97, local hit=41, temp read=4,102,446 written=2,905,238
20. 1,249,507.379 1,249,507.379 ↑ 3,851.8 9,143 1

CTE Scan on objectschecksum oc (cost=0.00..704,348.48 rows=35,217,424 width=48) (actual time=1,121,573.359..1,249,507.379 rows=9,143 loops=1)

  • Output: oc.checksum, oc.*, oc.object_id
  • Buffers: shared hit=47 read=1,957,710 written=97, local hit=41, temp read=4,102,446 written=2,905,238
21. 2.300 41.416 ↑ 1.2 9,181 1

Hash (cost=965.14..965.14 rows=11,147 width=42) (actual time=41.416..41.416 rows=9,181 loops=1)

  • Output: efp_tran_info.snapshot_id, efp_tran_info.previous_snapshot_id, efp_tran_info.object_id, efp_tran_info.object_checksum, efp_tran_info.impact_factor, efp_tran_info.status, efp_tran_info.fp_source, efp_tran_info.ctid
  • Buckets: 16,384 Batches: 1 Memory Usage: 720kB
  • Buffers: shared read=295
22. 11.463 39.116 ↑ 1.2 9,181 1

Bitmap Heap Scan on opus_c_central.efp_tran_info (cost=294.81..965.14 rows=11,147 width=42) (actual time=29.042..39.116 rows=9,181 loops=1)

  • Output: efp_tran_info.snapshot_id, efp_tran_info.previous_snapshot_id, efp_tran_info.object_id, efp_tran_info.object_checksum, efp_tran_info.impact_factor, efp_tran_info.status, efp_tran_info.fp_source, efp_tran_info.ctid
  • Recheck Cond: (efp_tran_info.snapshot_id = 14)
  • Heap Blocks: exact=165
  • Buffers: shared read=295
23. 27.653 27.653 ↓ 2.0 22,309 1

Bitmap Index Scan on efp_traninf_idx (cost=0.00..292.02 rows=11,147 width=0) (actual time=27.653..27.653 rows=22,309 loops=1)

  • Index Cond: (efp_tran_info.snapshot_id = 14)
  • Buffers: shared read=130