explain.depesz.com

PostgreSQL's explain analyze made readable

Result: oCw5

Settings
# exclusive inclusive rows x rows loops node
1. 0.030 71,796.281 ↓ 9.0 9 1

Subquery Scan on rows (cost=8,415.94..8,415.97 rows=1 width=85) (actual time=71,796.184..71,796.281 rows=9 loops=1)

2. 0.093 71,796.251 ↓ 9.0 9 1

GroupAggregate (cost=8,415.94..8,415.96 rows=1 width=85) (actual time=71,796.176..71,796.251 rows=9 loops=1)

  • Group Key: det.email, det.alert_id
3. 0.020 71,796.158 ↓ 10.0 10 1

Sort (cost=8,415.94..8,415.94 rows=1 width=174) (actual time=71,796.149..71,796.158 rows=10 loops=1)

  • Sort Key: det.email, det.alert_id
  • Sort Method: quicksort Memory: 27kB
4. 0.023 71,796.138 ↓ 10.0 10 1

Subquery Scan on det (cost=8,415.88..8,415.93 rows=1 width=174) (actual time=71,796.101..71,796.138 rows=10 loops=1)

5. 0.021 71,796.115 ↓ 10.0 10 1

Unique (cost=8,415.88..8,415.92 rows=1 width=161) (actual time=71,796.086..71,796.115 rows=10 loops=1)

6. 0.125 71,796.094 ↓ 20.0 20 1

Sort (cost=8,415.88..8,415.89 rows=1 width=161) (actual time=71,796.085..71,796.094 rows=20 loops=1)

  • Sort Key: h.enterdt, l.orderno, l.ordersuf, os.stage, h.takenby, l.shipprod, l.qtyord, ph.vendno, (((CASE WHEN (lower(l.ordertype) = 'p'::text) THEN 'PO'::text WHEN (lower(l.ordertype) = 't'::text) THEN 'WT'::text ELSE NULL::text END || ' '::text) || (l.orderaltno)::text)), (COALESCE(ws.stage, ps.stage)), (encode(digest((l.orderno)::text, 'sha1'::text), 'base64'::text)), o.tqemailaddr
  • Sort Method: quicksort Memory: 30kB
7. 2.650 71,795.969 ↓ 20.0 20 1

Nested Loop Left Join (cost=0.85..8,415.87 rows=1 width=161) (actual time=54,400.252..71,795.969 rows=20 loops=1)

  • Join Filter: (lower(h.takenby) = lower(o.oper2))
  • Rows Removed by Join Filter: 1,210
8. 0.059 71,792.899 ↓ 10.0 10 1

Nested Loop Left Join (cost=0.85..8,396.46 rows=1 width=67) (actual time=54,400.215..71,792.899 rows=10 loops=1)

  • Join Filter: (ph.stagecd = ps.stagecd)
  • Rows Removed by Join Filter: 28
9. 0.071 71,792.770 ↓ 10.0 10 1

Nested Loop Left Join (cost=0.85..8,394.83 rows=1 width=62) (actual time=54,400.202..71,792.770 rows=10 loops=1)

10. 0.060 71,791.719 ↓ 10.0 10 1

Nested Loop Left Join (cost=0.57..8,357.35 rows=1 width=61) (actual time=54,400.101..71,791.719 rows=10 loops=1)

  • Join Filter: (lower(l.ordertype) = 'p'::text)
  • Rows Removed by Join Filter: 1
11. 0.052 71,789.909 ↓ 10.0 10 1

Nested Loop Left Join (cost=0.29..8,297.31 rows=1 width=57) (actual time=54,399.943..71,789.909 rows=10 loops=1)

  • Join Filter: (wh.stagecd = ws.stagecd)
  • Rows Removed by Join Filter: 89
12. 0.837 71,789.767 ↓ 10.0 10 1

Nested Loop Left Join (cost=0.29..8,295.68 rows=1 width=52) (actual time=54,399.924..71,789.767 rows=10 loops=1)

  • Join Filter: ((w.wtno = wh.wtno) AND (w.wtsuf = wh.wtsuf))
  • Rows Removed by Join Filter: 643
13. 0.768 71,788.760 ↓ 10.0 10 1

Nested Loop Left Join (cost=0.29..8,289.06 rows=1 width=56) (actual time=54,399.769..71,788.760 rows=10 loops=1)

  • Join Filter: ((l.orderaltno = w.wtno) AND (l.linealtno = w.lineno) AND (lower(l.ordertype) = 't'::text))
  • Rows Removed by Join Filter: 729
14. 0.816 71,787.812 ↓ 10.0 10 1

Nested Loop (cost=0.29..8,281.87 rows=1 width=48) (actual time=54,399.658..71,787.812 rows=10 loops=1)

  • Join Filter: (h.stagecd = os.stagecd)
  • Rows Removed by Join Filter: 533
15. 45.092 71,785.445 ↓ 141.0 141 1

Nested Loop (cost=0.29..8,280.13 rows=1 width=43) (actual time=600.903..71,785.445 rows=141 loops=1)

16. 221.697 221.697 ↓ 33,863.0 33,863 1

Seq Scan on oeeh h (cost=0.00..6,146.10 rows=1 width=20) (actual time=0.053..221.697 rows=33,863 loops=1)

  • Filter: ((lower(transtype) = 'so'::text) AND (lower(approvty) = 'y'::text))
  • Rows Removed by Filter: 4,692
17. 71,518.656 71,518.656 ↓ 0.0 0 33,863

Index Scan using oeel_cono_orderno_ordersuf_prodcat_index on oeel l (cost=0.29..2,134.02 rows=1 width=31) (actual time=2.110..2.112 rows=0 loops=33,863)

  • Index Cond: ((orderno = h.orderno) AND (ordersuf = h.ordersuf))
  • Filter: ((canceldt IS NOT NULL) AND (lower(ordertype) = ANY ('{p,t}'::text[])))
  • Rows Removed by Filter: 2
18. 1.551 1.551 ↑ 1.5 4 141

Seq Scan on stage_map os (cost=0.00..1.67 rows=6 width=13) (actual time=0.006..0.011 rows=4 loops=141)

  • Filter: ((process = 'so'::text) AND (stage <> ALL ('{Cancelled,Invoiced,Paid}'::text[])))
  • Rows Removed by Filter: 35
19. 0.180 0.180 ↑ 1.0 73 10

Seq Scan on wtel w (cost=0.00..5.73 rows=73 width=12) (actual time=0.004..0.018 rows=73 loops=10)

20. 0.170 0.170 ↑ 1.0 64 10

Seq Scan on wteh wh (cost=0.00..5.65 rows=65 width=12) (actual time=0.002..0.017 rows=64 loops=10)

21. 0.090 0.090 ↑ 1.0 9 10

Seq Scan on stage_map ws (cost=0.00..1.51 rows=9 width=13) (actual time=0.007..0.009 rows=9 loops=10)

  • Filter: (process = 'po'::text)
  • Rows Removed by Filter: 31
22. 1.750 1.750 ↑ 1.0 1 10

Index Only Scan using poel_pkey on poel p (cost=0.28..60.02 rows=1 width=12) (actual time=0.149..0.175 rows=1 loops=10)

  • Index Cond: ((pono = l.orderaltno) AND (lineno = l.linealtno))
  • Heap Fetches: 6
23. 0.980 0.980 ↑ 1.0 1 10

Index Scan using poeh_pkey on poeh ph (cost=0.28..37.48 rows=1 width=17) (actual time=0.090..0.098 rows=1 loops=10)

  • Index Cond: ((pono = p.pono) AND (posuf = p.posuf))
24. 0.070 0.070 ↑ 2.2 4 10

Seq Scan on stage_map ps (cost=0.00..1.51 rows=9 width=13) (actual time=0.007..0.007 rows=4 loops=10)

  • Filter: (process = 'po'::text)
  • Rows Removed by Filter: 25
25. 0.420 0.420 ↑ 1.0 123 10

Seq Scan on sasoo o (cost=0.00..17.23 rows=123 width=25) (actual time=0.003..0.042 rows=123 loops=10)

Planning time : 12.411 ms
Execution time : 71,796.853 ms