explain.depesz.com

PostgreSQL's explain analyze made readable

Result: DCMe

Settings
# exclusive inclusive rows x rows loops node
1. 17.675 606,313.986 ↓ 0.0 0 1

Nested Loop (cost=10,000,006,520.48..10,000,007,703.24 rows=1 width=8) (actual time=606,313.986..606,313.986 rows=0 loops=1)

  • Join Filter: (((CASE WHEN (e.generatedpk IS NOT NULL) THEN e.source_rec_cd ELSE c.source_rec_cd END IS NOT NULL) OR ((CASE WHEN (e_1.generatedpk IS NOT NULL) THEN e_1.source_rec_cd ELSE c_1.source_rec_cd END) IS NOT NULL) OR (CASE WHEN (e.generatedpk IS NOT NULL) THEN e.ref_name ELSE c.ref_name END IS NOT NULL) OR ((CASE WHEN (e_1.generatedpk IS NOT NULL) THEN e_1.ref_name ELSE c_1.ref_name END) IS NOT NULL) OR (CASE WHEN (e.generatedpk IS NOT NULL) THEN e.rec_source_system_cd ELSE c.rec_source_system_cd END IS NOT NULL) OR ((CASE WHEN (e_1.generatedpk IS NOT NULL) THEN e_1.rec_source_system_cd ELSE c_1.rec_source_system_cd END) IS NOT NULL)) AND ((CASE WHEN (e.generatedpk IS NOT NULL) THEN e.source_rec_cd ELSE c.source_rec_cd END = (CASE WHEN (e_1.generatedpk IS NOT NULL) THEN e_1.source_rec_cd ELSE c_1.source_rec_cd END)) OR ((CASE WHEN (e.generatedpk IS NOT NULL) THEN e.source_rec_cd ELSE c.source_rec_cd END IS NULL) AND ((CASE WHEN (e_1.generatedpk IS NOT NULL) THEN e_1.source_rec_cd ELSE c_1.source_rec_cd END) IS NULL))) AND ((CASE WHEN (e.generatedpk IS NOT NULL) THEN e.ref_name ELSE c.ref_name END = (CASE WHEN (e_1.generatedpk IS NOT NULL) THEN e_1.ref_name ELSE c_1.ref_name END)) OR ((CASE WHEN (e.generatedpk IS NOT NULL) THEN e.ref_name ELSE c.ref_name END IS NULL) AND ((CASE WHEN (e_1.generatedpk IS NOT NULL) THEN e_1.ref_name ELSE c_1.ref_name END) IS NULL))) AND ((CASE WHEN (e.generatedpk IS NOT NULL) THEN e.rec_source_system_cd ELSE c.rec_source_system_cd END = (CASE WHEN (e_1.generatedpk IS NOT NULL) THEN e_1.rec_source_system_cd ELSE c_1.rec_source_system_cd END)) OR ((CASE WHEN (e.generatedpk IS NOT NULL) THEN e.rec_source_system_cd ELSE c.rec_source_system_cd END IS NULL) AND ((CASE WHEN (e_1.generatedpk IS NOT NULL) THEN e_1.rec_source_system_cd ELSE c_1.rec_source_system_cd END) IS NULL))))
  • Buffers: shared hit=4,546
2. 56.813 117.584 ↓ 45,059.0 45,059 1

Hash Join (cost=3,260.24..4,442.94 rows=1 width=90) (actual time=40.134..117.584 rows=45,059 loops=1)

  • Hash Cond: (ids.generatedpk = CASE WHEN (e.generatedpk IS NOT NULL) THEN e.generatedpk ELSE c.generatedpk END)
  • Buffers: shared hit=2,440
3. 20.667 20.667 ↑ 1.0 61,723 1

Seq Scan on t524 ids (cost=0.00..951.23 rows=61,723 width=8) (actual time=0.012..20.667 rows=61,723 loops=1)

  • Buffers: shared hit=334
4. 9.174 40.104 ↓ 45,059.0 45,059 1

Hash (cost=3,260.23..3,260.23 rows=1 width=90) (actual time=40.103..40.104 rows=45,059 loops=1)

  • Buckets: 65,536 (originally 1024) Batches: 1 (originally 1) Memory Usage: 3,771kB
  • Buffers: shared hit=2,106
5. 18.959 30.930 ↓ 45,059.0 45,059 1

Hash Full Join (cost=771.96..3,260.23 rows=1 width=90) (actual time=7.251..30.930 rows=45,059 loops=1)

  • Hash Cond: (c.generatedpk = e.generatedpk)
  • Filter: ((e.generatedpk IS NULL) OR (e.deleted = '0'::numeric) OR (e.deleted IS NULL))
  • Rows Removed by Filter: 16,664
  • Buffers: shared hit=2,106
6. 4.761 4.761 ↑ 1.0 61,723 1

Seq Scan on cdl_bmap_mapping c (cost=0.00..2,326.23 rows=61,723 width=42) (actual time=0.004..4.761 rows=61,723 loops=1)

  • Buffers: shared hit=1,709
7. 3.149 7.210 ↑ 1.0 16,664 1

Hash (cost=563.65..563.65 rows=16,665 width=53) (actual time=7.210..7.210 rows=16,664 loops=1)

  • Buckets: 32,768 Batches: 1 Memory Usage: 1,661kB
  • Buffers: shared hit=397
8. 4.061 4.061 ↑ 1.0 16,664 1

Seq Scan on cdl_bmap_mappinge e (cost=0.00..563.65 rows=16,665 width=53) (actual time=0.008..4.061 rows=16,664 loops=1)

  • Buffers: shared hit=397
9. 504,886.095 606,178.727 ↓ 0.0 0 45,059

GroupAggregate (cost=3,260.24..3,260.27 rows=1 width=96) (actual time=13.453..13.453 rows=0 loops=45,059)

  • Group Key: (CASE WHEN (e_1.generatedpk IS NOT NULL) THEN e_1.source_rec_cd ELSE c_1.source_rec_cd END), (CASE WHEN (e_1.generatedpk IS NOT NULL) THEN e_1.ref_name ELSE c_1.ref_name END), (CASE WHEN (e_1.generatedpk IS NOT NULL) THEN e_1.rec_source_system_cd ELSE c_1.rec_source_system_cd END)
  • Filter: (count(*) > 1)
  • Rows Removed by Filter: 45,059
  • Buffers: shared hit=2,106
10. 101,263.380 101,292.632 ↓ 45,059.0 45,059 45,059

Sort (cost=3,260.24..3,260.24 rows=1 width=96) (actual time=0.004..2.248 rows=45,059 loops=45,059)

  • Sort Key: (CASE WHEN (e_1.generatedpk IS NOT NULL) THEN e_1.source_rec_cd ELSE c_1.source_rec_cd END), (CASE WHEN (e_1.generatedpk IS NOT NULL) THEN e_1.ref_name ELSE c_1.ref_name END), (CASE WHEN (e_1.generatedpk IS NOT NULL) THEN e_1.rec_source_system_cd ELSE c_1.rec_source_system_cd END)
  • Sort Method: quicksort Memory: 5,244kB
  • Buffers: shared hit=2,106
11. 18.095 29.252 ↓ 45,059.0 45,059 1

Hash Full Join (cost=771.96..3,260.23 rows=1 width=96) (actual time=6.772..29.252 rows=45,059 loops=1)

  • Hash Cond: (c_1.generatedpk = e_1.generatedpk)
  • Filter: ((e_1.generatedpk IS NULL) OR (e_1.deleted = '0'::numeric) OR (e_1.deleted IS NULL))
  • Rows Removed by Filter: 16,664
  • Buffers: shared hit=2,106
12. 4.427 4.427 ↑ 1.0 61,723 1

Seq Scan on cdl_bmap_mapping c_1 (cost=0.00..2,326.23 rows=61,723 width=42) (actual time=0.003..4.427 rows=61,723 loops=1)

  • Buffers: shared hit=1,709
13. 3.091 6.730 ↑ 1.0 16,664 1

Hash (cost=563.65..563.65 rows=16,665 width=53) (actual time=6.730..6.730 rows=16,664 loops=1)

  • Buckets: 32,768 Batches: 1 Memory Usage: 1,661kB
  • Buffers: shared hit=397
14. 3.639 3.639 ↑ 1.0 16,664 1

Seq Scan on cdl_bmap_mappinge e_1 (cost=0.00..563.65 rows=16,665 width=53) (actual time=0.008..3.639 rows=16,664 loops=1)

  • Buffers: shared hit=397
Planning time : 0.528 ms
Execution time : 606,314.091 ms