explain.depesz.com

PostgreSQL's explain analyze made readable

Result: y66T : better trbc

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

Unique (cost=1,084,313.96..1,084,369.09 rows=200 width=32) (actual rows= loops=)

2.          

CTE rdcmappingx

3. 0.000 0.000 ↓ 0.0

Append (cost=0.00..56,145.15 rows=392,286 width=34) (actual rows= loops=)

4. 0.000 0.000 ↓ 0.0

Seq Scan on rdcsecmapx (cost=0.00..18,554.51 rows=127,874 width=34) (actual rows= loops=)

  • Filter: ((ventype = 49) AND (rank = 1))
5. 0.000 0.000 ↓ 0.0

Seq Scan on rdcgsecmapx (cost=0.00..33,667.77 rows=264,412 width=34) (actual rows= loops=)

  • Filter: ((ventype = 49) AND (rank = 1))
6.          

CTE issmapping

7. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=50,998.56..336,684.05 rows=3,825,277 width=22) (actual rows= loops=)

  • Merge Cond: ((s.type_ = p.typ) AND (s.code = p.seccode))
8. 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[]))
9. 0.000 0.000 ↓ 0.0

Materialize (cost=50,998.12..52,959.55 rows=392,286 width=14) (actual rows= loops=)

10. 0.000 0.000 ↓ 0.0

Sort (cost=50,998.12..51,978.84 rows=392,286 width=14) (actual rows= loops=)

  • Sort Key: p.typ, p.seccode
11. 0.000 0.000 ↓ 0.0

CTE Scan on rdcmappingx p (cost=0.00..7,845.72 rows=392,286 width=14) (actual rows= loops=)

12.          

CTE primissmapping

13. 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))))
14. 0.000 0.000 ↓ 0.0

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

15. 0.000 0.000 ↓ 0.0

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

16. 0.000 0.000 ↓ 0.0

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

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

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

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

Sort (cost=308,854.43..308,882.00 rows=11,027 width=32) (actual rows= loops=)

  • Sort Key: sm.eid, (NULL::integer)
19. 0.000 0.000 ↓ 0.0

Append (cost=622.60..308,114.04 rows=11,027 width=32) (actual rows= loops=)

20. 0.000 0.000 ↓ 0.0

Nested Loop (cost=622.60..182,521.15 rows=10,002 width=32) (actual rows= loops=)

21. 0.000 0.000 ↓ 0.0

Hash Join (cost=622.03..98,219.04 rows=10,002 width=28) (actual rows= loops=)

  • Hash Cond: (p1.isrcode = p2.isrcode)
22. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.43..97,245.70 rows=105 width=24) (actual rows= loops=)

23. 0.000 0.000 ↓ 0.0

CTE Scan on issmapping p1 (cost=0.00..95,631.93 rows=191 width=12) (actual rows= loops=)

  • Filter: ((vencode IS NULL) AND ((typ = 1) OR (typ = 6)))
24. 0.000 0.000 ↓ 0.0

Index Scan using vw_secmaster_seccode_idx on vw_secmaster sm (cost=0.43..8.44 rows=1 width=18) (actual rows= loops=)

  • Index Cond: (seccode = p1.code)
  • Filter: (((type_ = 1) AND (p1.typ = 1)) OR ((type_ = 10) AND (p1.typ = 6)))
25. 0.000 0.000 ↓ 0.0

Hash (cost=382.52..382.52 rows=19,126 width=8) (actual rows= loops=)

26. 0.000 0.000 ↓ 0.0

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

27. 0.000 0.000 ↓ 0.0

Index Scan using rdcinstrinfo_pkey on rdcinstrinfo si (cost=0.57..8.42 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (instrid = p2.vencode)
28. 0.000 0.000 ↓ 0.0

Nested Loop (cost=107,470.28..125,482.62 rows=1,025 width=32) (actual rows= loops=)

29. 0.000 0.000 ↓ 0.0

Nested Loop (cost=107,469.72..116,689.47 rows=1,025 width=28) (actual rows= loops=)

30. 0.000 0.000 ↓ 0.0

Merge Join (cost=107,469.28..107,788.88 rows=1,871 width=24) (actual rows= loops=)

  • Merge Cond: ((sp.seccode = p1_1.code) AND (sp.typ = p1_1.typ))
31. 0.000 0.000 ↓ 0.0

Sort (cost=8,933.68..8,938.58 rows=1,961 width=12) (actual rows= loops=)

  • Sort Key: sp.seccode, sp.typ
32. 0.000 0.000 ↓ 0.0

CTE Scan on rdcmappingx sp (cost=0.00..8,826.43 rows=1,961 width=12) (actual rows= loops=)

  • Filter: (rank = 1)
33. 0.000 0.000 ↓ 0.0

Sort (cost=98,535.61..98,631.00 rows=38,157 width=12) (actual rows= loops=)

  • Sort Key: p1_1.code, p1_1.typ
34. 0.000 0.000 ↓ 0.0

CTE Scan on issmapping p1_1 (cost=0.00..95,631.93 rows=38,157 width=12) (actual rows= loops=)

  • Filter: ((typ = 1) OR (typ = 6))
35. 0.000 0.000 ↓ 0.0

Index Scan using vw_secmaster_seccode_idx on vw_secmaster sm_1 (cost=0.43..4.75 rows=1 width=18) (actual rows= loops=)

  • Index Cond: (seccode = p1_1.code)
  • Filter: (((type_ = 1) AND (p1_1.typ = 1)) OR ((type_ = 10) AND (p1_1.typ = 6)))
36. 0.000 0.000 ↓ 0.0

Index Scan using rdcinstrinfo_pkey on rdcinstrinfo si_1 (cost=0.57..8.57 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (instrid = sp.vencode)