explain.depesz.com

PostgreSQL's explain analyze made readable

Result: o6qG

Settings
# exclusive inclusive rows x rows loops node
1. 1,133.464 253,201.764 ↑ 4.8 1,212,352 1

Subquery Scan on ords (cost=769,574,402.46..769,927,178.58 rows=5,879,602 width=630) (actual time=238,021.534..253,201.764 rows=1,212,352 loops=1)

2. 4,970.005 252,068.300 ↑ 4.8 1,212,352 1

Unique (cost=769,574,402.46..769,853,683.55 rows=5,879,602 width=181) (actual time=238,021.523..252,068.300 rows=1,212,352 loops=1)

3. 56,613.641 247,098.295 ↑ 1.0 5,612,954 1

Sort (cost=769,574,402.46..769,589,101.46 rows=5,879,602 width=181) (actual time=238,021.520..247,098.295 rows=5,612,954 loops=1)

  • Sort Key: o.cust_ord_id, o.cust_id, o.cdate, o.bsn_flow_spec_id, o.sbmdate, o.edate, bi.name, bi.lang, s.name, h.amount, h.cur_code, h.card_number, h.cust_acct_pymnt_hstr_id, (COALESCE(CASE WHEN ((p.ident_shrt_code1)::text = 'MSISDN'::text) THEN p.ident_val1 ELSE NULL::character varying END, CASE WHEN ((prnt.ident_shrt_code1)::text = 'MSISDN'::text) THEN prnt.ident_val1 ELSE NULL::character varying END, (SubPlan 2))), (COALESCE(CASE WHEN ((p.ident_shrt_code1)::text = 'SERIAL_NUMBER'::text) THEN p.ident_val1 ELSE NULL::character varying END, CASE WHEN ((prnt.ident_shrt_code1)::text = 'SERIAL_NUMBER'::text) THEN prnt.ident_val1 ELSE NULL::character varying END, (SubPlan 4))), (CASE WHEN ((p.ident_shrt_code3)::text = 'MODEM_MAC_ADDR'::text) THEN p.ident_val3 ELSE NULL::character varying END), ((SubPlan 5)), ((SubPlan 8))
  • Sort Method: external merge Disk: 648056kB
4. 8,452.136 190,484.654 ↑ 1.0 5,612,954 1

Hash Join (cost=266,671.34..767,868,311.97 rows=5,879,602 width=181) (actual time=5,180.930..190,484.654 rows=5,612,954 loops=1)

  • Hash Cond: (o.bsn_inter_spec_id = bi.bsn_inter_spec_id)
5. 2,981.693 10,764.932 ↑ 1.1 1,914,612 1

Hash Join (cost=266,637.12..490,384.44 rows=2,051,867 width=159) (actual time=5,176.748..10,764.932 rows=1,914,612 loops=1)

  • Hash Cond: (i.cust_ord_id = o.cust_ord_id)
6. 1,744.205 5,198.722 ↑ 1.1 1,914,612 1

Hash Left Join (cost=142,936.95..277,192.10 rows=2,051,867 width=75) (actual time=2,591.659..5,198.722 rows=1,914,612 loops=1)

  • Hash Cond: (i.prod_id = p.prod_id)
7. 863.039 863.039 ↑ 1.1 1,914,612 1

Seq Scan on cust_ord_item i (cost=0.00..83,357.67 rows=2,051,867 width=15) (actual time=0.010..863.039 rows=1,914,612 loops=1)

8. 387.301 2,591.478 ↓ 1.0 856,322 1

Hash (cost=123,173.02..123,173.02 rows=850,314 width=60) (actual time=2,591.478..2,591.478 rows=856,322 loops=1)

  • Buckets: 65536 Batches: 32 Memory Usage: 1923kB
9. 751.374 2,204.177 ↓ 1.0 856,322 1

Hash Left Join (cost=54,334.07..123,173.02 rows=850,314 width=60) (actual time=918.176..2,204.177 rows=856,322 loops=1)

  • Hash Cond: (p.prnt_prod_id = prnt.prod_id)
10. 534.815 534.815 ↓ 1.0 856,322 1

Seq Scan on prod p (cost=0.00..38,722.14 rows=850,314 width=53) (actual time=0.004..534.815 rows=856,322 loops=1)

11. 379.774 917.988 ↓ 1.0 856,322 1

Hash (cost=38,722.14..38,722.14 rows=850,314 width=19) (actual time=917.988..917.988 rows=856,322 loops=1)

  • Buckets: 65536 Batches: 16 Memory Usage: 2755kB
12. 538.214 538.214 ↓ 1.0 856,322 1

Seq Scan on prod prnt (cost=0.00..38,722.14 rows=850,314 width=19) (actual time=0.003..538.214 rows=856,322 loops=1)

13. 338.298 2,584.517 ↑ 1.0 608,127 1

Hash (cost=106,688.92..106,688.92 rows=626,580 width=92) (actual time=2,584.517..2,584.517 rows=608,127 loops=1)

  • Buckets: 32768 Batches: 32 Memory Usage: 2069kB
14. 581.092 2,246.219 ↑ 1.0 608,127 1

Hash Left Join (cost=38,032.17..106,688.92 rows=626,580 width=92) (actual time=862.676..2,246.219 rows=608,127 loops=1)

  • Hash Cond: (o.cust_ord_id = h.cust_ord_id)
15. 580.508 802.579 ↑ 1.0 608,127 1

Hash Join (cost=1.56..48,288.84 rows=626,580 width=65) (actual time=0.043..802.579 rows=608,127 loops=1)

  • Hash Cond: (o.ord_st_id = s.ord_st_id)
16. 222.047 222.047 ↑ 1.0 608,127 1

Seq Scan on cust_ord o (cost=0.00..39,671.80 rows=626,580 width=55) (actual time=0.005..222.047 rows=608,127 loops=1)

17. 0.015 0.024 ↑ 1.0 25 1

Hash (cost=1.25..1.25 rows=25 width=19) (actual time=0.024..0.024 rows=25 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
18. 0.009 0.009 ↑ 1.0 25 1

Seq Scan on ord_st s (cost=0.00..1.25 rows=25 width=19) (actual time=0.001..0.009 rows=25 loops=1)

19. 56.679 862.548 ↓ 114.3 115,103 1

Hash (cost=38,018.02..38,018.02 rows=1,007 width=36) (actual time=862.548..862.548 rows=115,103 loops=1)

  • Buckets: 65536 (originally 1024) Batches: 4 (originally 1) Memory Usage: 3585kB
20. 74.054 805.869 ↓ 114.3 115,104 1

Subquery Scan on h (cost=31,473.07..38,018.02 rows=1,007 width=36) (actual time=433.830..805.869 rows=115,104 loops=1)

  • Filter: (h.row_number = 1)
  • Rows Removed by Filter: 88211
21. 195.557 731.815 ↓ 1.0 203,315 1

WindowAgg (cost=31,473.07..35,500.73 rows=201,383 width=36) (actual time=433.825..731.815 rows=203,315 loops=1)

22. 417.414 536.258 ↓ 1.0 203,315 1

Sort (cost=31,473.07..31,976.53 rows=201,383 width=36) (actual time=433.816..536.258 rows=203,315 loops=1)

  • Sort Key: cust_acct_pymnt_hstr.cust_ord_id, cust_acct_pymnt_hstr.cust_acct_pymnt_hstr_id DESC
  • Sort Method: external merge Disk: 8920kB
23. 118.844 118.844 ↓ 1.0 203,315 1

Seq Scan on cust_acct_pymnt_hstr (cost=0.00..8,222.65 rows=201,383 width=36) (actual time=0.009..118.844 rows=203,315 loops=1)

  • Filter: ((auth_number)::text <> '000000'::text)
  • Rows Removed by Filter: 3620
24. 0.391 0.700 ↑ 1.0 854 1

Hash (cost=23.54..23.54 rows=854 width=32) (actual time=0.700..0.700 rows=854 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 62kB
25. 0.309 0.309 ↑ 1.0 854 1

Seq Scan on bsn_inter_spec_lang bi (cost=0.00..23.54 rows=854 width=32) (actual time=0.006..0.309 rows=854 loops=1)

26.          

SubPlan (forHash Join)

27. 3,950.628 8,539.650 ↓ 0.0 0 2,846,550

Nested Loop (cost=0.85..25.35 rows=1 width=14) (actual time=0.003..0.003 rows=0 loops=2,846,550)

28. 2,846.550 2,846.550 ↓ 0.0 0 2,846,550

Index Scan using idx_prod_rel_prod_id2 on prod_rel rel (cost=0.42..8.45 rows=1 width=6) (actual time=0.001..0.001 rows=0 loops=2,846,550)

  • Index Cond: (prod_id2 = p.prod_id)
  • Filter: (is_actv = '1'::numeric)
  • Rows Removed by Filter: 0
29. 1,742.472 1,742.472 ↓ 0.0 0 580,824

Index Scan using pk_prod on prod relprod (cost=0.42..8.45 rows=1 width=20) (actual time=0.003..0.003 rows=0 loops=580,824)

  • Index Cond: (prod_id = rel.prod_id1)
  • Filter: ((ident_shrt_code1)::text = 'MSISDN'::text)
  • Rows Removed by Filter: 1
30.          

SubPlan (forNested Loop)

31. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_prod on prod prntprod (cost=0.42..8.45 rows=1 width=13) (never executed)

  • Index Cond: (prod_id = relprod.prnt_prod_id)
32. 9,204.658 28,016.340 ↓ 0.0 0 5,603,268

Nested Loop (cost=0.85..25.35 rows=1 width=14) (actual time=0.005..0.005 rows=0 loops=5,603,268)

33. 11,206.536 11,206.536 ↓ 0.0 0 5,603,268

Index Scan using idx_prod_rel_prod_id2 on prod_rel rel_1 (cost=0.42..8.45 rows=1 width=6) (actual time=0.002..0.002 rows=0 loops=5,603,268)

  • Index Cond: (prod_id2 = p.prod_id)
  • Filter: (is_actv = '1'::numeric)
  • Rows Removed by Filter: 0
34. 7,594.986 7,594.986 ↓ 0.0 0 2,531,662

Index Scan using pk_prod on prod relprod_1 (cost=0.42..8.45 rows=1 width=20) (actual time=0.003..0.003 rows=0 loops=2,531,662)

  • Index Cond: (prod_id = rel_1.prod_id1)
  • Filter: ((ident_shrt_code1)::text = 'SERIAL_NUMBER'::text)
  • Rows Removed by Filter: 1
35.          

SubPlan (forNested Loop)

36. 10.160 10.160 ↑ 1.0 1 2,540

Index Scan using pk_prod on prod prntprod_1 (cost=0.42..8.45 rows=1 width=13) (actual time=0.003..0.004 rows=1 loops=2,540)

  • Index Cond: (prod_id = relprod_1.prnt_prod_id)
37. 5,612.954 72,968.402 ↓ 0.0 0 5,612,954

Unique (cost=0.98..46.58 rows=1 width=7) (actual time=0.009..0.013 rows=0 loops=5,612,954)

38. 5,418.976 67,355.448 ↑ 1.0 1 5,612,954

Nested Loop Semi Join (cost=0.98..46.57 rows=1 width=7) (actual time=0.009..0.012 rows=1 loops=5,612,954)

39. 2,690.351 56,129.540 ↑ 1.0 1 5,612,954

Nested Loop (cost=0.70..30.26 rows=1 width=12) (actual time=0.008..0.010 rows=1 loops=5,612,954)

  • Join Filter: (cv.char_id = gnl_char.char_id)
  • Rows Removed by Join Filter: 1
40. 33,677.724 33,677.724 ↑ 1.0 1 5,612,954

Index Scan using idx_cucv_cust_ord_id on cust_ord_char_val cv (cost=0.43..21.95 rows=1 width=17) (actual time=0.004..0.006 rows=1 loops=5,612,954)

  • Index Cond: (cust_ord_id = o.cust_ord_id)
  • Filter: ((val)::text = '1'::text)
  • Rows Removed by Filter: 7
41. 19,761.465 19,761.465 ↑ 1.0 1 6,587,155

Index Scan using nui_gnl_char_shrt_code on gnl_char (cost=0.27..8.29 rows=1 width=5) (actual time=0.002..0.003 rows=1 loops=6,587,155)

  • Index Cond: ((shrt_code)::text = 'IS_MNP'::text)
42. 5,806.932 5,806.932 ↑ 1.0 1 2,903,466

Index Scan using gnl_char_val_pkey on gnl_char_val (cost=0.28..8.30 rows=1 width=5) (actual time=0.002..0.002 rows=1 loops=2,903,466)

  • Index Cond: (char_val_id = cv.char_val_id)
  • Filter: ((shrt_code)::text = 'IS_MNP_1'::text)
43. 61,742.470 61,742.494 ↓ 0.0 0 5,612,954

Index Scan using idx_prod_char_val_prod_id on prod_char_val v (cost=17.02..33.22 rows=1 width=6) (actual time=0.011..0.011 rows=0 loops=5,612,954)

  • Index Cond: (i.prod_id = prod_id)
  • Filter: ((st_id = $8) AND (char_id = $9))
  • Rows Removed by Filter: 16
44.          

Initplan (forIndex Scan)

45. 0.014 0.014 ↑ 1.0 1 1

Index Scan using nui_gnl_st_02 on gnl_st gg (cost=0.27..8.29 rows=1 width=5) (actual time=0.014..0.014 rows=1 loops=1)

  • Index Cond: (((shrt_code)::text = 'ACTV'::text) AND ((ent_code_name)::text = 'PROD_CHAR_VAL'::text))
  • Filter: (is_actv = '1'::numeric)
46. 0.010 0.010 ↑ 1.0 1 1

Index Scan using nui_gnl_char_shrt_code on gnl_char gc (cost=0.27..8.29 rows=1 width=5) (actual time=0.009..0.010 rows=1 loops=1)

  • Index Cond: ((shrt_code)::text = 'SERVICE_NUMBER'::text)