explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 3YEz

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

Append (cost=865,843.33..1,347,918.08 rows=1,849,024 width=28) (actual rows= loops=)

2.          

CTE issmapping

3. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=79,352.21..399,765.50 rows=3,825,277 width=58) (actual rows= loops=)

  • Merge Cond: ((s.type_ = p.type_) AND (s.code = p.seccode))
4. 0.000 0.000 ↓ 0.0

Index Scan using prciss_pkey on prciss s (cost=0.43..262,622.80 rows=3,825,277 width=16) (actual rows= loops=)

  • Index Cond: (type_ = ANY ('{1,6}'::integer[]))
5. 0.000 0.000 ↓ 0.0

Sort (cost=79,351.77..79,456.69 rows=41,967 width=22) (actual rows= loops=)

  • Sort Key: p.type_, p.seccode
6. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on vw_secmap p (cost=937.68..76,129.35 rows=41,967 width=22) (actual rows= loops=)

  • Recheck Cond: (ventype = 47)
7. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on vw_secmap_ventype_vencode_idx (cost=0.00..927.19 rows=41,967 width=0) (actual rows= loops=)

  • Index Cond: (ventype = 47)
8.          

CTE primissmapping

9. 0.000 0.000 ↓ 0.0

Hash Join (cost=95,543.30..382,630.33 rows=19,126 width=8) (actual rows= loops=)

  • Hash Cond: ((m1.isrcode = issmapping.isrcode) AND (m1.rank = (min(issmapping.rank))))
10. 0.000 0.000 ↓ 0.0

CTE Scan on issmapping m1 (cost=0.00..76,505.54 rows=3,825,277 width=40) (actual rows= loops=)

11. 0.000 0.000 ↓ 0.0

Hash (cost=95,540.30..95,540.30 rows=200 width=36) (actual rows= loops=)

12. 0.000 0.000 ↓ 0.0

HashAggregate (cost=95,536.30..95,538.30 rows=200 width=36) (actual rows= loops=)

  • Group Key: issmapping.isrcode
13. 0.000 0.000 ↓ 0.0

CTE Scan on issmapping (cost=0.00..76,505.54 rows=3,806,151 width=36) (actual rows= loops=)

  • Filter: (vencode IS NOT NULL)
14. 0.000 0.000 ↓ 0.0

Merge Join (cost=83,447.50..110,978.42 rows=1,829,019 width=28) (actual rows= loops=)

  • Merge Cond: (p2.isrcode = p1.isrcode)
15. 0.000 0.000 ↓ 0.0

Sort (cost=5,581.79..5,629.61 rows=19,126 width=8) (actual rows= loops=)

  • Sort Key: p2.isrcode
16. 0.000 0.000 ↓ 0.0

Hash Join (cost=3,074.12..4,221.62 rows=19,126 width=8) (actual rows= loops=)

  • Hash Cond: (p2.vencode = si.code)
17. 0.000 0.000 ↓ 0.0

CTE Scan on primissmapping p2 (cost=0.00..382.52 rows=19,126 width=8) (actual rows= loops=)

18. 0.000 0.000 ↓ 0.0

Hash (cost=1,597.61..1,597.61 rows=89,961 width=8) (actual rows= loops=)

19. 0.000 0.000 ↓ 0.0

Seq Scan on spg2secinfo si (cost=0.00..1,597.61 rows=89,961 width=8) (actual rows= loops=)

20. 0.000 0.000 ↓ 0.0

Sort (cost=77,865.71..77,913.52 rows=19,126 width=20) (actual rows= loops=)

  • Sort Key: p1.isrcode
21. 0.000 0.000 ↓ 0.0

CTE Scan on issmapping p1 (cost=0.00..76,505.54 rows=19,126 width=20) (actual rows= loops=)

  • Filter: (vencode IS NULL)
22. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=34,199.46..436,053.59 rows=20,005 width=28) (actual rows= loops=)

  • Hash Cond: (p1_1.eid = sp.eid)
23. 0.000 0.000 ↓ 0.0

CTE Scan on issmapping p1_1 (cost=0.00..76,505.54 rows=3,825,277 width=12) (actual rows= loops=)

24. 0.000 0.000 ↓ 0.0

Hash (cost=34,170.29..34,170.29 rows=2,333 width=24) (actual rows= loops=)

25. 0.000 0.000 ↓ 0.0

Merge Join (cost=1.34..34,170.29 rows=2,333 width=24) (actual rows= loops=)

  • Merge Cond: (sp.vencode = si_1.code)
26. 0.000 0.000 ↓ 0.0

Index Scan using vw_secmap_ventype_vencode_idx on vw_secmap sp (cost=0.43..135,973.20 rows=41,750 width=20) (actual rows= loops=)

  • Index Cond: (ventype = 47)
  • Filter: (rank = 1)
27. 0.000 0.000 ↓ 0.0

Index Scan using spg2secinfo_pkey on spg2secinfo si_1 (cost=0.29..3,042.71 rows=89,961 width=8) (actual rows= loops=)