explain.depesz.com

PostgreSQL's explain analyze made readable

Result: sZRh

Settings
# exclusive inclusive rows x rows loops node
1. 0.032 74,107.683 ↓ 9.0 9 1

Subquery Scan on rows (cost=8,443.52..8,443.55 rows=1 width=85) (actual time=74,107.583..74,107.683 rows=9 loops=1)

2. 0.102 74,107.651 ↓ 9.0 9 1

GroupAggregate (cost=8,443.52..8,443.54 rows=1 width=85) (actual time=74,107.575..74,107.651 rows=9 loops=1)

  • Group Key: det.email, det.alert_id
3. 0.051 74,107.549 ↓ 10.0 10 1

Sort (cost=8,443.52..8,443.52 rows=1 width=174) (actual time=74,107.540..74,107.549 rows=10 loops=1)

  • Sort Key: det.email, det.alert_id
  • Sort Method: quicksort Memory: 27kB
4. 0.062 74,107.498 ↓ 10.0 10 1

Subquery Scan on det (cost=8,443.46..8,443.51 rows=1 width=174) (actual time=74,107.459..74,107.498 rows=10 loops=1)

5. 0.022 74,107.436 ↓ 10.0 10 1

Unique (cost=8,443.46..8,443.50 rows=1 width=161) (actual time=74,107.406..74,107.436 rows=10 loops=1)

6. 0.163 74,107.414 ↓ 20.0 20 1

Sort (cost=8,443.46..8,443.47 rows=1 width=161) (actual time=74,107.404..74,107.414 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.677 74,107.251 ↓ 20.0 20 1

Nested Loop Left Join (cost=0.85..8,443.45 rows=1 width=161) (actual time=56,922.810..74,107.251 rows=20 loops=1)

  • Join Filter: (lower(h.takenby) = lower(o.oper2))
  • Rows Removed by Join Filter: 1,210
8. 0.056 74,104.194 ↓ 10.0 10 1

Nested Loop Left Join (cost=0.85..8,424.04 rows=1 width=67) (actual time=56,922.749..74,104.194 rows=10 loops=1)

  • Join Filter: (ph.stagecd = ps.stagecd)
  • Rows Removed by Join Filter: 28
9. 0.064 74,104.068 ↓ 10.0 10 1

Nested Loop Left Join (cost=0.85..8,422.41 rows=1 width=62) (actual time=56,922.736..74,104.068 rows=10 loops=1)

10. 0.062 74,102.834 ↓ 10.0 10 1

Nested Loop Left Join (cost=0.57..8,384.93 rows=1 width=61) (actual time=56,922.586..74,102.834 rows=10 loops=1)

  • Join Filter: (lower(l.ordertype) = 'p'::text)
  • Rows Removed by Join Filter: 1
11. 0.048 74,100.862 ↓ 10.0 10 1

Nested Loop Left Join (cost=0.29..8,324.89 rows=1 width=57) (actual time=56,922.271..74,100.862 rows=10 loops=1)

  • Join Filter: (wh.stagecd = ws.stagecd)
  • Rows Removed by Join Filter: 89
12. 0.788 74,100.724 ↓ 10.0 10 1

Nested Loop Left Join (cost=0.29..8,323.26 rows=1 width=52) (actual time=56,922.256..74,100.724 rows=10 loops=1)

  • Join Filter: ((w.wtno = wh.wtno) AND (w.wtsuf = wh.wtsuf))
  • Rows Removed by Join Filter: 643
13. 0.764 74,099.796 ↓ 10.0 10 1

Nested Loop Left Join (cost=0.29..8,316.64 rows=1 width=56) (actual time=56,922.151..74,099.796 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.806 74,098.872 ↓ 10.0 10 1

Nested Loop (cost=0.29..8,309.45 rows=1 width=48) (actual time=56,922.056..74,098.872 rows=10 loops=1)

  • Join Filter: (h.stagecd = os.stagecd)
  • Rows Removed by Join Filter: 533
15. 53.188 74,096.515 ↓ 141.0 141 1

Nested Loop (cost=0.29..8,307.71 rows=1 width=43) (actual time=578.699..74,096.515 rows=141 loops=1)

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

Seq Scan on oeeh h (cost=0.00..6,173.68 rows=1 width=20) (actual time=0.130..255.850 rows=33,863 loops=1)

  • Filter: ((lower(transtype) = 'so'::text) AND (lower(approvty) = 'y'::text))
  • Rows Removed by Filter: 4,692
17. 73,787.477 73,787.477 ↓ 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.177..2.179 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.160 0.160 ↑ 1.0 73 10

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

20. 0.140 0.140 ↑ 1.0 64 10

Seq Scan on wteh wh (cost=0.00..5.65 rows=65 width=12) (actual time=0.003..0.014 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.006..0.009 rows=9 loops=10)

  • Filter: (process = 'po'::text)
  • Rows Removed by Filter: 31
22. 1.910 1.910 ↑ 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.163..0.191 rows=1 loops=10)

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

Index Scan using poeh_pkey on poeh ph (cost=0.28..37.48 rows=1 width=17) (actual time=0.106..0.117 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.380 0.380 ↑ 1.0 123 10

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

Planning time : 27.367 ms
Execution time : 74,109.339 ms