explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4rVE

Settings
# exclusive inclusive rows x rows loops node
1. 19.915 584,192.611 ↓ 0.0 0 1

Nested Loop (cost=10,000,006,520.48..10,000,008,217.68 rows=1 width=8) (actual time=584,192.611..584,192.611 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))))
2. 61.767 130.900 ↓ 45,058.0 45,058 1

Hash Join (cost=3,260.24..4,957.39 rows=1 width=90) (actual time=41.976..130.900 rows=45,058 loops=1)

  • Hash Cond: (ids.generatedpk = CASE WHEN (e.generatedpk IS NOT NULL) THEN e.generatedpk ELSE c.generatedpk END)
3. 29.334 29.334 ↓ 1.0 61,723 1

Seq Scan on t522 ids (cost=0.00..1,466.10 rows=61,610 width=8) (actual time=2.165..29.334 rows=61,723 loops=1)

4. 10.398 39.799 ↓ 45,058.0 45,058 1

Hash (cost=3,260.23..3,260.23 rows=1 width=90) (actual time=39.799..39.799 rows=45,058 loops=1)

  • Buckets: 65,536 (originally 1024) Batches: 1 (originally 1) Memory Usage: 3,771kB
5. 17.945 29.401 ↓ 45,058.0 45,058 1

Hash Full Join (cost=771.96..3,260.23 rows=1 width=90) (actual time=6.882..29.401 rows=45,058 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,665
6. 4.611 4.611 ↑ 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.611 rows=61,723 loops=1)

7. 2.863 6.845 ↑ 1.0 16,665 1

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

  • Buckets: 32,768 Batches: 1 Memory Usage: 1,661kB
8. 3.982 3.982 ↑ 1.0 16,665 1

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

9. 584,009.095 584,041.796 ↓ 0.0 0 45,058

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

  • 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,058
  • -> Sort (cost=3,260.24..3260.24 rows=1 width=96) (actual time=0.004..2.130 rows=45,058 loops=45,058)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
10. 20.739 32.701 ↓ 45,058.0 45,058 1

Hash Full Join (cost=771.96..3,260.23 rows=1 width=96) (actual time=7.451..32.701 rows=45,058 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,665
11. 4.652 4.652 ↑ 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.005..4.652 rows=61,723 loops=1)

12. 3.601 7.310 ↑ 1.0 16,665 1

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

  • Buckets: 32,768 Batches: 1 Memory Usage: 1,661kB
13. 3.709 3.709 ↑ 1.0 16,665 1

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

Planning time : 0.549 ms
Execution time : 584,192.918 ms