explain.depesz.com

PostgreSQL's explain analyze made readable

Result: w5hK

Settings
# exclusive inclusive rows x rows loops node
1. 33.906 586,183.600 ↓ 0.0 0 1

Nested Loop (cost=4,032.62..6,521.06 rows=1 width=8) (actual time=586,183.600..586,183.600 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. 44.949 530.868 ↓ 45,058.0 45,058 1

Nested Loop (cost=772.38..3,260.77 rows=1 width=90) (actual time=7.201..530.868 rows=45,058 loops=1)

3. 57.182 80.397 ↓ 45,058.0 45,058 1

Hash Full Join (cost=771.96..3,260.23 rows=1 width=90) (actual time=7.158..80.397 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
4. 16.094 16.094 ↑ 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.003..16.094 rows=61,723 loops=1)

5. 3.289 7.121 ↑ 1.0 16,665 1

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

  • Buckets: 32,768 Batches: 1 Memory Usage: 1,661kB
6. 3.832 3.832 ↑ 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.011..3.832 rows=16,665 loops=1)

7. 405.522 405.522 ↑ 1.0 1 45,058

Index Only Scan using t522_pk on t522 ids (cost=0.41..0.53 rows=1 width=8) (actual time=0.005..0.009 rows=1 loops=45,058)

  • Index Cond: (generatedpk = CASE WHEN (e.generatedpk IS NOT NULL) THEN e.generatedpk ELSE c.generatedpk END)
  • Heap Fetches: 99,628
8. 484,643.848 585,618.826 ↓ 0.0 0 45,058

GroupAggregate (cost=3,260.24..3,260.27 rows=1 width=96) (actual time=12.997..12.997 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
9. 100,945.373 100,974.978 ↓ 45,058.0 45,058 45,058

Sort (cost=3,260.24..3,260.24 rows=1 width=96) (actual time=0.004..2.241 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. 18.317 29.605 ↓ 45,058.0 45,058 1

Hash Full Join (cost=771.96..3,260.23 rows=1 width=96) (actual time=6.967..29.605 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.495 4.495 ↑ 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.495 rows=61,723 loops=1)

12. 3.778 6.793 ↑ 1.0 16,665 1

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

  • Buckets: 32,768 Batches: 1 Memory Usage: 1,661kB
13. 3.015 3.015 ↑ 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.005..3.015 rows=16,665 loops=1)

Planning time : 0.520 ms
Execution time : 586,183.925 ms