explain.depesz.com

PostgreSQL's explain analyze made readable

Result: LaWU

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 131.911 ↓ 0.0 0 1

Nested Loop (cost=4,032.58..6,521.02 rows=1 width=8) (actual time=131.911..131.911 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. 8.253 131.911 ↓ 0.0 0 1

Nested Loop (cost=772.34..3,260.73 rows=1 width=90) (actual time=131.911..131.911 rows=0 loops=1)

3. 21.792 33.542 ↓ 45,058.0 45,058 1

Hash Full Join (cost=771.96..3,260.23 rows=1 width=90) (actual time=7.084..33.542 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. 4.711 4.711 ↑ 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.711 rows=61,723 loops=1)

5. 3.096 7.039 ↑ 1.0 16,665 1

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

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

7. 90.116 90.116 ↓ 0.0 0 45,058

Index Only Scan using t522_pk on t522 ids (cost=0.38..0.49 rows=1 width=8) (actual time=0.002..0.002 rows=0 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. 0.000 0.000 ↓ 0.0 0

GroupAggregate (cost=3,260.24..3,260.27 rows=1 width=96) (never executed)

  • 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)
9. 0.000 0.000 ↓ 0.0 0

Sort (cost=3,260.24..3,260.24 rows=1 width=96) (never executed)

  • 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)
10. 0.000 0.000 ↓ 0.0 0

Hash Full Join (cost=771.96..3,260.23 rows=1 width=96) (never executed)

  • 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))
11. 0.000 0.000 ↓ 0.0 0

Seq Scan on cdl_bmap_mapping c_1 (cost=0.00..2,326.23 rows=61,723 width=42) (never executed)

12. 0.000 0.000 ↓ 0.0 0

Hash (cost=563.65..563.65 rows=16,665 width=53) (never executed)

13. 0.000 0.000 ↓ 0.0 0

Seq Scan on cdl_bmap_mappinge e_1 (cost=0.00..563.65 rows=16,665 width=53) (never executed)

Planning time : 0.501 ms
Execution time : 131.999 ms