explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6dWH

Settings
# exclusive inclusive rows x rows loops node
1. 0.114 246.549 ↓ 0.0 0 1

Hash Join (cost=10,000,004,628.52..10,017,305,922.00 rows=110 width=8) (actual time=246.548..246.549 rows=0 loops=1)

  • Hash Cond: (CASE WHEN (e.generatedpk IS NOT NULL) THEN e.generatedpk ELSE c.generatedpk END = ids.generatedpk)
2. 33.966 207.316 ↓ 0.0 0 1

Nested Loop (cost=10,000,002,881.22..10,017,304,173.19 rows=110 width=16) (actual time=207.316..207.316 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))))
3. 37.827 48.168 ↓ 1.0 62,591 1

Hash Full Join (cost=1.07..2,254.27 rows=62,589 width=83) (actual time=0.082..48.168 rows=62,591 loops=1)

  • Hash Cond: (c.generatedpk = e.generatedpk)
  • Filter: ((e.generatedpk IS NULL) OR (e.deleted = '0'::numeric) OR (e.deleted IS NULL))
4. 10.309 10.309 ↑ 1.0 62,589 1

Seq Scan on cdl_bmap_mapping c (cost=0.00..2,088.89 rows=62,589 width=42) (actual time=0.005..10.309 rows=62,589 loops=1)

5. 0.013 0.032 ↑ 1.0 3 1

Hash (cost=1.03..1.03 rows=3 width=44) (actual time=0.032..0.032 rows=3 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
6. 0.019 0.019 ↑ 1.0 3 1

Seq Scan on cdl_bmap_mappinge e (cost=0.00..1.03 rows=3 width=44) (actual time=0.017..0.019 rows=3 loops=1)

7. 0.000 125.182 ↓ 0.0 0 62,591

Materialize (cost=2,880.16..3,605.65 rows=13,819 width=96) (actual time=0.002..0.002 rows=0 loops=62,591)

8. 80.714 136.915 ↓ 0.0 0 1

HashAggregate (cost=2,880.16..3,398.37 rows=13,819 width=96) (actual time=136.915..136.915 rows=0 loops=1)

  • 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: 62,591
9. 45.183 56.201 ↓ 1.0 62,591 1

Hash Full Join (cost=1.07..2,254.27 rows=62,589 width=96) (actual time=0.057..56.201 rows=62,591 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))
10. 11.001 11.001 ↑ 1.0 62,589 1

Seq Scan on cdl_bmap_mapping c_1 (cost=0.00..2,088.89 rows=62,589 width=42) (actual time=0.002..11.001 rows=62,589 loops=1)

11. 0.012 0.017 ↑ 1.0 3 1

Hash (cost=1.03..1.03 rows=3 width=44) (actual time=0.017..0.017 rows=3 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
12. 0.005 0.005 ↑ 1.0 3 1

Seq Scan on cdl_bmap_mappinge e_1 (cost=0.00..1.03 rows=3 width=44) (actual time=0.004..0.005 rows=3 loops=1)

13. 23.352 39.119 ↑ 1.0 62,591 1

Hash (cost=964.91..964.91 rows=62,591 width=8) (actual time=39.119..39.119 rows=62,591 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 2,957kB
14. 15.767 15.767 ↑ 1.0 62,591 1

Seq Scan on t1714 ids (cost=0.00..964.91 rows=62,591 width=8) (actual time=0.022..15.767 rows=62,591 loops=1)

Planning time : 0.827 ms
Execution time : 247.875 ms