explain.depesz.com

PostgreSQL's explain analyze made readable

Result: eOgd

Settings
# exclusive inclusive rows x rows loops node
1. 0.004 222.121 ↓ 0.0 0 1

Merge Join (cost=10,017,304,177.21..10,017,305,855.21 rows=110 width=8) (actual time=222.121..222.121 rows=0 loops=1)

  • Merge Cond: (ids.generatedpk = (CASE WHEN (e.generatedpk IS NOT NULL) THEN e.generatedpk ELSE c.generatedpk END))
  • Buffers: shared hit=2,931
2. 0.076 0.076 ↑ 62,591.0 1 1

Index Only Scan using t524_pk on t524 ids (cost=0.29..1,520.15 rows=62,591 width=8) (actual time=0.076..0.076 rows=1 loops=1)

  • Heap Fetches: 1
  • Buffers: shared hit=3
3. 0.007 222.041 ↓ 0.0 0 1

Sort (cost=10,017,304,176.92..10,017,304,177.20 rows=110 width=16) (actual time=222.041..222.041 rows=0 loops=1)

  • Sort Key: (CASE WHEN (e.generatedpk IS NOT NULL) THEN e.generatedpk ELSE c.generatedpk END)
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=2,928
4. 40.104 222.034 ↓ 0.0 0 1

Nested Loop (cost=10,000,002,881.22..10,017,304,173.19 rows=110 width=16) (actual time=222.034..222.034 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=2,928
5. 44.755 56.756 ↑ 1.0 62,587 1

Hash Full Join (cost=1.07..2,254.27 rows=62,589 width=83) (actual time=0.045..56.756 rows=62,587 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: 4
  • Buffers: shared hit=1,464
6. 11.976 11.976 ↑ 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.006..11.976 rows=62,589 loops=1)

  • Buffers: shared hit=1,463
7. 0.008 0.025 ↓ 2.3 7 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=1
8. 0.017 0.017 ↓ 2.3 7 1

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

  • Buffers: shared hit=1
9. 62.395 125.174 ↓ 0.0 0 62,587

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

  • Buffers: shared hit=1,464
  • -> HashAggregate (cost=2,880.16..3398.37 rows=13,819 width=96) (actual time=139.311..139.311 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,587
  • Buffers: shared hit=1,464
10. 50.426 62.779 ↑ 1.0 62,587 1

Hash Full Join (cost=1.07..2,254.27 rows=62,589 width=96) (actual time=0.028..62.779 rows=62,587 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: 4
  • Buffers: shared hit=1,464
11. 12.339 12.339 ↑ 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.003..12.339 rows=62,589 loops=1)

  • Buffers: shared hit=1,463
12. 0.006 0.014 ↓ 2.3 7 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=1
13. 0.008 0.008 ↓ 2.3 7 1

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

  • Buffers: shared hit=1
Planning time : 0.944 ms
Execution time : 222.567 ms