explain.depesz.com

PostgreSQL's explain analyze made readable

Result: WkSv

Settings
# exclusive inclusive rows x rows loops node
1. 3.122 145,610.085 ↑ 224.7 21 1

Hash Join (cost=2,939,573.97..2,944,408.61 rows=4,719 width=12) (actual time=70,904.433..145,610.085 rows=21 loops=1)

  • Output: removed_tran.transaction_id, dvs.diag_id, removed_tran.object_id
  • Hash Cond: (removed_tran.object_id = dvs.object_id)
  • Buffers: shared hit=7976, local read=81617 written=4096, temp read=40560 written=40560
2.          

CTE removed_tran

3. 22,421.578 144,398.866 ↑ 26.1 639 1

Merge Anti Join (cost=2,580,026.54..2,806,220.31 rows=16,652 width=8) (actual time=68,052.932..144,398.866 rows=639 loops=1)

  • Output: prev.transaction_id, prev.object_id
  • Merge Cond: ((prev.transaction_id = cur.transaction_id) AND (prev.object_id = cur.object_id))
  • Buffers: local read=81617 written=4096, temp read=40560 written=40560
4. 40,410.244 51,108.037 ↓ 1.0 9,222,965 1

Sort (cost=1,290,054.58..1,313,111.90 rows=9,222,931 width=8) (actual time=34,080.373..51,108.037 rows=9,222,965 loops=1)

  • Output: prev.transaction_id, prev.object_id
  • Sort Key: prev.transaction_id, prev.object_id
  • Sort Method: external merge Disk: 162160kB
  • Buffers: local read=40810 written=4096, temp read=20281 written=20281
5. 10,697.793 10,697.793 ↓ 1.0 9,222,965 1

Seq Scan on pg_temp_8.work_previous_transaction_detail prev (cost=0.00..133,039.31 rows=9,222,931 width=8) (actual time=0.078..10,697.793 rows=9,222,965 loops=1)

  • Output: prev.transaction_id, prev.object_id
  • Buffers: local read=40810 written=4096
6. 19,906.894 70,869.251 ↓ 1.0 9,222,352 1

Materialize (cost=1,289,965.67..1,336,077.37 rows=9,222,341 width=8) (actual time=33,937.120..70,869.251 rows=9,222,352 loops=1)

  • Output: cur.transaction_id, cur.object_id
  • Buffers: local read=40807, temp read=20279 written=20279
7. 40,278.173 50,962.357 ↓ 1.0 9,222,352 1

Sort (cost=1,289,965.67..1,313,021.52 rows=9,222,341 width=8) (actual time=33,937.111..50,962.357 rows=9,222,352 loops=1)

  • Output: cur.transaction_id, cur.object_id
  • Sort Key: cur.transaction_id, cur.object_id
  • Sort Method: external merge Disk: 162144kB
  • Buffers: local read=40807, temp read=20279 written=20279
8. 10,684.184 10,684.184 ↓ 1.0 9,222,352 1

Seq Scan on pg_temp_8.work_current_transaction_detail cur (cost=0.00..133,030.41 rows=9,222,341 width=8) (actual time=0.035..10,684.184 rows=9,222,352 loops=1)

  • Output: cur.transaction_id, cur.object_id
  • Buffers: local read=40807
9. 144,402.167 144,402.167 ↑ 26.1 639 1

CTE Scan on removed_tran (cost=0.00..333.04 rows=16,652 width=8) (actual time=68,052.941..144,402.167 rows=639 loops=1)

  • Output: removed_tran.transaction_id, removed_tran.object_id
  • Buffers: local read=81617 written=4096, temp read=40560 written=40560
10. 20.529 1,204.796 ↑ 2.4 15,444 1

Hash (cost=132,886.60..132,886.60 rows=37,365 width=8) (actual time=1,204.796..1,204.796 rows=15,444 loops=1)

  • Output: dvs.diag_id, dvs.object_id
  • Buckets: 4096 Batches: 1 Memory Usage: 604kB
  • Buffers: shared hit=7976
11. 579.447 1,184.267 ↑ 2.4 15,444 1

Hash Join (cost=17,619.78..132,886.60 rows=37,365 width=8) (actual time=55.406..1,184.267 rows=15,444 loops=1)

  • Output: dvs.diag_id, dvs.object_id
  • Hash Cond: (dvs.diag_id = dmt.metric_id)
  • Buffers: shared hit=7976
12. 549.850 604.265 ↓ 1.0 493,494 1

Bitmap Heap Scan on c839_18850_vam.dss_violation_statuses dvs (cost=17,571.53..127,143.91 rows=472,959 width=8) (actual time=54.820..604.265 rows=493,494 loops=1)

  • Output: dvs.diag_id, dvs.object_id, dvs.snapshot_id, dvs.violation_status, dvs.previous_snapshot_id
  • Recheck Cond: ((dvs.snapshot_id = 51) AND (dvs.violation_status = ANY ('{3,4}'::integer[])))
  • Buffers: shared hit=7951
13. 54.415 54.415 ↓ 1.0 494,482 1

Bitmap Index Scan on dss_violation_status_idx1 (cost=0.00..17,453.29 rows=472,959 width=0) (actual time=54.415..54.415 rows=494,482 loops=1)

  • Index Cond: ((dvs.snapshot_id = 51) AND (dvs.violation_status = ANY ('{3,4}'::integer[])))
  • Buffers: shared hit=4800
14. 0.231 0.555 ↑ 1.0 191 1

Hash (cost=45.86..45.86 rows=191 width=4) (actual time=0.555..0.555 rows=191 loops=1)

  • Output: dmt.metric_id
  • Buckets: 1024 Batches: 1 Memory Usage: 7kB
  • Buffers: shared hit=25
15. 0.324 0.324 ↑ 1.0 191 1

Index Scan using dss_metr_typ_idx3 on c839_18850_vam.dss_metric_type_trees dmt (cost=0.00..45.86 rows=191 width=4) (actual time=0.036..0.324 rows=191 loops=1)

  • Output: dmt.metric_id
  • Index Cond: (dmt.metric_critical = 1)
  • Buffers: shared hit=25