explain.depesz.com

PostgreSQL's explain analyze made readable

Result: UB5x

Settings
# exclusive inclusive rows x rows loops node
1. 50.149 11,647.904 ↓ 0.0 0 1

Insert on pg_temp_2.tmp_linkedpackages (cost=964,476.15..1,056,189.70 rows=4,076,158 width=16) (actual time=11,647.904..11,647.904 rows=0 loops=1)

  • Buffers: shared hit=1806225, local hit=48087 read=521 dirtied=251, temp read=12724 written=12724
2. 518.351 11,597.755 ↑ 88.0 46,328 1

Unique (cost=964,476.15..1,015,428.12 rows=4,076,158 width=16) (actual time=9,822.355..11,597.755 rows=46,328 loops=1)

  • Output: tcmclr.packageid, tcmclr.classid, tcmcle.packageid, tcmcle.classid
  • Buffers: shared hit=1806225, local hit=1262 read=268, temp read=12724 written=12724
3. 4,714.845 11,079.404 ↑ 1.0 4,006,094 1

Sort (cost=964,476.15..974,666.54 rows=4,076,158 width=16) (actual time=9,822.354..11,079.404 rows=4,006,094 loops=1)

  • Output: tcmclr.packageid, tcmclr.classid, tcmcle.packageid, tcmcle.classid
  • Sort Key: tcmclr.packageid, tcmclr.classid, tcmcle.packageid, tcmcle.classid
  • Sort Method: external merge Disk: 101736kB
  • Buffers: shared hit=1806225, local hit=1262 read=268, temp read=12724 written=12724
4. 2,282.577 6,364.559 ↑ 1.0 4,006,094 1

Hash Join (cost=46,376.55..447,274.89 rows=4,076,158 width=16) (actual time=1,181.998..6,364.559 rows=4,006,094 loops=1)

  • Output: tcmclr.packageid, tcmclr.classid, tcmcle.packageid, tcmcle.classid
  • Hash Cond: (dcls.caller_id = tcmclr.memberid)
  • Join Filter: (tcmcle.packageid <> tcmclr.packageid)
  • Rows Removed by Join Filter: 652671
  • Buffers: shared hit=1806225, local hit=1262 read=268
5. 1,258.594 4,050.432 ↓ 1.4 5,383,889 1

Merge Join (cost=43,622.23..149,467.40 rows=3,825,487 width=12) (actual time=1,147.157..4,050.432 rows=5,383,889 loops=1)

  • Output: dcls.caller_id, tcmcle.packageid, tcmcle.classid
  • Merge Cond: (tcmcle.classid = dcls.called_id)
  • Buffers: shared hit=1806225, local hit=728 read=268
6. 39.069 39.069 ↑ 1.0 98,681 1

Index Scan using idx_tmp_classmembers_classid on pg_temp_2.tmp_classmembers tcmcle (cost=0.29..3,108.12 rows=98,681 width=8) (actual time=0.020..39.069 rows=98,681 loops=1)

  • Output: tcmcle.packageid, tcmcle.classid, tcmcle.memberid
  • Buffers: local hit=728 read=268
7. 686.323 2,752.769 ↓ 3.8 7,099,867 1

Materialize (cost=0.43..93,442.02 rows=1,865,277 width=8) (actual time=0.055..2,752.769 rows=7,099,867 loops=1)

  • Output: dcls.caller_id, dcls.called_id, dcls.snapshot_id
  • Buffers: shared hit=1806225
8. 2,066.446 2,066.446 ↑ 1.0 1,865,016 1

Index Scan using diag_lnks_sim_cle on castoncast_local_2019_08.diag_ctv_links_simple dcls (cost=0.43..88,778.83 rows=1,865,277 width=8) (actual time=0.044..2,066.446 rows=1,865,016 loops=1)

  • Output: dcls.caller_id, dcls.called_id, dcls.snapshot_id
  • Buffers: shared hit=1806225
9. 22.796 31.550 ↑ 1.0 98,681 1

Hash (cost=1,520.81..1,520.81 rows=98,681 width=12) (actual time=31.550..31.550 rows=98,681 loops=1)

  • Output: tcmclr.packageid, tcmclr.classid, tcmclr.memberid
  • Buckets: 131072 Batches: 1 Memory Usage: 5265kB
  • Buffers: local hit=534
10. 8.754 8.754 ↑ 1.0 98,681 1

Seq Scan on pg_temp_2.tmp_classmembers tcmclr (cost=0.00..1,520.81 rows=98,681 width=12) (actual time=0.024..8.754 rows=98,681 loops=1)

  • Output: tcmclr.packageid, tcmclr.classid, tcmclr.memberid
  • Buffers: local hit=534