explain.depesz.com

PostgreSQL's explain analyze made readable

Result: vV3C : Optimization for: plan #ZtCwc

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.175 94,792.799 ↓ 3.0 2,000 1

Subquery Scan on inner1 (cost=49,251,203.46..49,251,278.46 rows=667 width=1,962) (actual time=94,790.160..94,792.799 rows=2,000 loops=1)

  • Filter: (inner1.rnum >= 1)
2. 0.187 94,792.624 ↑ 1.0 2,000 1

Limit (cost=49,251,203.46..49,251,253.46 rows=2,000 width=1,962) (actual time=94,790.159..94,792.624 rows=2,000 loops=1)

3. 1.306 94,792.437 ↑ 277.1 2,000 1

WindowAgg (cost=49,251,203.46..49,265,060.26 rows=554,272 width=1,962) (actual time=94,790.158..94,792.437 rows=2,000 loops=1)

4. 977.026 94,791.131 ↑ 277.1 2,000 1

Sort (cost=49,251,203.46..49,252,589.14 rows=554,272 width=1,986) (actual time=94,790.148..94,791.131 rows=2,000 loops=1)

  • Sort Key: (round((ordmst.odrnum)::numeric, 0)), ordlin.txnidr
  • Sort Method: external sort Disk: 180,296kB
5. 917.698 93,814.105 ↑ 1.2 466,090 1

WindowAgg (cost=48,622,531.48..48,720,914.76 rows=554,272 width=1,986) (actual time=93,257.631..93,814.105 rows=466,090 loops=1)

6. 426.576 92,896.407 ↑ 1.2 466,090 1

Unique (cost=48,622,531.48..48,702,900.92 rows=554,272 width=1,938) (actual time=92,369.059..92,896.407 rows=466,090 loops=1)

7.          

Initplan (for Unique)

8. 0.005 0.005 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=32) (actual time=0.005..0.005 rows=1 loops=1)

9. 5,651.744 92,469.826 ↑ 1.2 466,120 1

Sort (cost=48,622,531.46..48,623,917.14 rows=554,272 width=1,938) (actual time=92,369.057..92,469.826 rows=466,120 loops=1)

  • Sort Key: ordmst.odrnum, ordlin.txnidr, ordlin.odrtyp, lngmst.lngnam, ordmst.participating_prgcod, (COALESCE(trxtnbackpost.acrsta, trxtnnew.acrsta, ''::character varying)), ((COALESCE((CASE WHEN ((trxtnnew.acrsta)::text = 'X'::text) THEN 0 ELSE NULL::integer END)::numeric, trxtnnew.acrmle, '0'::numeric) - COALESCE(trxtnbackpost.acrmle, '0'::numeric))), (COALESCE(trxtnnew.acrmle, '0'::numeric)), (COALESCE(trxtnbackpost.acrmle, '0'::numeric)), ((SubPlan 1)), prgloc.prgnam, ordmst.rdmtyp, ordmst.rflflg, ordlin.extdriven, ((((ordmst.odrnum)::text || '-'::text) || (ordlin.txnidr)::text)), ordmst.odrdat, ordmst.net_frdscr, ordmst.mfa_type, ordlin.txntype, (estimate_penalty_start_time(timezone('UTC'::text, timezone($1, ordlin.penalty_start_time)), ((SubPlan 3))::character varying)), ordlin.mercod, brandmst.brand_name, ordlin.prdcod, ordlin.redemption_sub_type, ordlin.artnum, ((SubPlan 4)), (COALESCE(ordlin.mernam, mermst.mernam)), mermst.notify_odr_confirmation, ordmst.mem_conf_notif_sent, ordlin.mer_conf_email_sent, ordlin.qty, lnedet.netagtfee, lnedet.rfdfrc, lnedet.excratpgmb, (CASE WHEN ((ordlin.odrtyp)::text = 'E'::text) THEN (CASE WHEN ((ordmst.txntyp)::text = 'M'::text) THEN ((replace((ordlin.prdnam)::text, 'Etihad Upgrade'::text, 'Upgrade'::text) || ' - '::text) || ((SubPlan 5))::text) ELSE replace((ordlin.prdnam)::text, 'Booking Reference'::text, 'Flight - '::text) END)::character varying ELSE COALESCE(ordlin.prdnameng, ordlin.prdnam) END), pltodrlneshpdtl.trkinfoavl, pltodrlneshpdtl.merchant_reference, pltodrlneshpdtl.couriercod, pltodrlneshpdtl.agentname, pltodrlneshpdtl.othcouriername, pltodrlneshpdtl.refbillno, ((SubPlan 7)), lnedet.paytyp, ordlin.shptyp, ((SubPlan 8)), curloc.curlbl, (CASE WHEN ((ordmst.rdmtyp)::text = 'P'::text) THEN round((ordlin.untrtlprc)::numeric, 4) ELSE round(((ordlin.untrtlprc * (ordlin.qty)::double precision))::numeric, 2) END), lnedet.pnts, lnedet.valdoctyp, (CASE WHEN ((lnedet.valdoctyp IS NOT NULL) AND (btrim((lnedet.valdoctyp)::text) <> ''::text)) THEN CASE WHEN ((valdoc.valdocsts IS NOT NULL) AND (btrim((valdoc.valdocsts)::text) <> ''::text)) THEN valdoc.valdocsts ELSE 'V'::character varying END ELSE NULL::character varying END), prddet.instant_fulfillment, (round((lnedet.subamt)::numeric, 2)), ordmst.memidr, ordmst.is_encrypted, cntloc.cntnam, (decode((ordmst.frdscr)::numeric, VARIADIC ARRAY[NULL::numeric, '0'::numeric, (ordmst.frdscr)::numeric])), ordlin.cncldfrfraud
  • Sort Method: external sort Disk: 167,040kB
10. 33,331.584 86,818.082 ↑ 1.2 466,120 1

Nested Loop (cost=5,685,399.42..48,103,607.27 rows=554,272 width=1,938) (actual time=11,621.117..86,818.082 rows=466,120 loops=1)

11. 205.515 20,391.978 ↓ 1.7 466,120 1

Hash Join (cost=5,685,399.14..6,529,041.90 rows=277,136 width=590) (actual time=11,619.554..20,391.978 rows=466,120 loops=1)

  • Hash Cond: (ordmst.shpingcnt = cntloc.cntcod)
12. 263.724 20,185.797 ↓ 1.7 466,120 1

Hash Join (cost=5,685,210.32..6,528,121.83 rows=273,643 width=583) (actual time=11,618.872..20,185.797 rows=466,120 loops=1)

  • Hash Cond: ((ordlin.prdcod)::text = (prddet.prdcod)::text)
13. 264.187 19,892.781 ↓ 1.7 466,120 1

Hash Join (cost=5,681,404.18..6,523,597.31 rows=273,643 width=582) (actual time=11,589.318..19,892.781 rows=466,120 loops=1)

  • Hash Cond: ((ordlin.mercod)::text = (mermst.mercod)::text)
14. 437.883 19,627.188 ↓ 1.7 466,120 1

Merge Left Join (cost=5,681,233.58..6,522,707.43 rows=273,643 width=563) (actual time=11,587.882..19,627.188 rows=466,120 loops=1)

  • Merge Cond: (((ordlin.odrnum)::text = (valdoc.ordnum)::text) AND (ordlin.txnidr = valdoc.odrlnenum))
  • Join Filter: (((valdoc.prgcod)::text = (ordlin.prgcod)::text) AND ((valdoc.valdoctyp)::text = (lnedet.valdoctyp)::text))
15. 137.814 18,398.284 ↓ 1.7 466,120 1

Merge Left Join (cost=5,650,376.86..6,489,854.23 rows=273,643 width=561) (actual time=10,827.229..18,398.284 rows=466,120 loops=1)

  • Merge Cond: (((ordmst.odrnum)::text = (trxtnbackpost.txncod)::text) AND (ordlin.txnidr = trxtnbackpost.txnidr))
  • Join Filter: ((ordlin.prgcod)::text = (trxtnbackpost.prgcod)::text)
16. 150.865 18,246.124 ↓ 1.7 466,120 1

Merge Left Join (cost=5,650,376.44..6,487,994.90 rows=273,643 width=556) (actual time=10,812.879..18,246.124 rows=466,120 loops=1)

  • Merge Cond: (((ordmst.odrnum)::text = (trxtnnew.txncod)::text) AND (ordlin.txnidr = trxtnnew.txnidr))
  • Join Filter: ((ordlin.prgcod)::text = (trxtnnew.prgcod)::text)
17. 490.367 18,071.754 ↓ 1.7 466,120 1

Nested Loop Left Join (cost=5,650,236.40..6,486,027.57 rows=273,643 width=551) (actual time=10,789.371..18,071.754 rows=466,120 loops=1)

18. 260.965 12,454.067 ↓ 3.1 466,120 1

Nested Loop (cost=5,650,235.85..5,655,268.78 rows=150,123 width=502) (actual time=10,789.340..12,454.067 rows=466,120 loops=1)

19. 779.009 12,193.102 ↓ 3.1 466,120 1

Merge Left Join (cost=5,650,235.85..5,653,387.21 rows=150,123 width=481) (actual time=10,789.297..12,193.102 rows=466,120 loops=1)

  • Merge Cond: (((ordlin.odrnum)::text = (pltodrlneshpdtl.odrnum)::text) AND (ordlin.txnidr = pltodrlneshpdtl.txnidr))
  • Join Filter: ((pltodrlneshpdtl.prgcod)::text = (ordlin.prgcod)::text)
20. 4,222.711 9,413.522 ↓ 3.1 466,081 1

Sort (cost=5,576,496.08..5,576,871.39 rows=150,123 width=448) (actual time=8,844.579..9,413.522 rows=466,081 loops=1)

  • Sort Key: ordmst.odrnum, ordlin.txnidr
  • Sort Method: external merge Disk: 140,608kB
21. 142.752 5,190.811 ↓ 3.1 466,081 1

Hash Join (cost=1,052,226.15..5,533,313.67 rows=150,123 width=448) (actual time=2,566.044..5,190.811 rows=466,081 loops=1)

  • Hash Cond: (ordmst.paycur = curloc.curcod)
22. 149.337 5,047.582 ↓ 3.2 466,081 1

Hash Left Join (cost=1,052,091.07..5,532,783.95 rows=146,461 width=446) (actual time=2,565.551..5,047.582 rows=466,081 loops=1)

  • Hash Cond: (ordmst.odrlangcode = lngmst.lngcod)
23. 160.352 4,898.223 ↓ 3.2 466,081 1

Hash Left Join (cost=1,052,089.15..5,530,768.19 rows=146,461 width=336) (actual time=2,565.507..4,898.223 rows=466,081 loops=1)

  • Hash Cond: ((ordlin.brand_code)::text = (brandmst.brand_code)::text)
24. 1,245.906 4,736.816 ↓ 3.2 466,081 1

Hash Join (cost=1,051,949.58..5,530,243.74 rows=146,461 width=332) (actual time=2,564.435..4,736.816 rows=466,081 loops=1)

  • Hash Cond: ((ordlin.odrnum)::text = (ordmst.odrnum)::text)
25. 964.637 1,129.255 ↑ 2.1 466,666 1

Bitmap Heap Scan on pltodrlne ordlin (cost=438,406.77..4,850,200.75 rows=958,162 width=176) (actual time=198.768..1,129.255 rows=466,666 loops=1)

  • Recheck Cond: ((prgcod)::text = 'PAMREX'::text)
  • Filter: ((dlvsta)::text = 'S'::text)
  • Rows Removed by Filter: 1,414,930
  • Heap Blocks: exact=193,511
26. 164.618 164.618 ↓ 1.1 2,028,672 1

Bitmap Index Scan on pltodrlne_prgcod (cost=0.00..438,167.23 rows=1,866,490 width=0) (actual time=164.618..164.618 rows=2,028,672 loops=1)

  • Index Cond: ((prgcod)::text = 'PAMREX'::text)
27. 824.834 2,361.655 ↓ 2.3 1,777,890 1

Hash (cost=586,772.34..586,772.34 rows=765,718 width=156) (actual time=2,361.655..2,361.655 rows=1,777,890 loops=1)

  • Buckets: 524,288 (originally 524288) Batches: 8 (originally 4) Memory Usage: 61,441kB
28. 1,364.297 1,536.821 ↓ 2.3 1,777,890 1

Bitmap Heap Scan on pltodrmst ordmst (cost=43,143.10..586,772.34 rows=765,718 width=156) (actual time=222.216..1,536.821 rows=1,777,890 loops=1)

  • Recheck Cond: ((prgcod)::text = 'PAMREX'::text)
  • Filter: ((odrdat >= '2019-01-02 00:00:00'::timestamp without time zone) AND ((odrsta)::text = 'F'::text))
  • Rows Removed by Filter: 18,914
  • Heap Blocks: exact=237,622
29. 172.524 172.524 ↑ 1.0 1,796,921 1

Bitmap Index Scan on idx_odrmst_prgcod (cost=0.00..42,951.68 rows=1,809,499 width=0) (actual time=172.524..172.524 rows=1,796,921 loops=1)

  • Index Cond: ((prgcod)::text = 'PAMREX'::text)
30. 0.552 1.055 ↓ 1.0 3,815 1

Hash (cost=92.03..92.03 rows=3,803 width=18) (actual time=1.055..1.055 rows=3,815 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 223kB
31. 0.503 0.503 ↓ 1.0 3,815 1

Seq Scan on brand_master brandmst (cost=0.00..92.03 rows=3,803 width=18) (actual time=0.007..0.503 rows=3,815 loops=1)

32. 0.007 0.022 ↑ 1.0 41 1

Hash (cost=1.41..1.41 rows=41 width=126) (actual time=0.022..0.022 rows=41 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
33. 0.015 0.015 ↑ 1.0 41 1

Seq Scan on pltlngmst lngmst (cost=0.00..1.41 rows=41 width=126) (actual time=0.006..0.015 rows=41 loops=1)

34. 0.026 0.477 ↑ 1.0 162 1

Hash (cost=133.03..133.03 rows=164 width=8) (actual time=0.477..0.477 rows=162 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 15kB
35. 0.451 0.451 ↑ 1.0 162 1

Seq Scan on pltcurlocinf curloc (cost=0.00..133.03 rows=164 width=8) (actual time=0.006..0.451 rows=162 loops=1)

  • Filter: (lancod = 1)
  • Rows Removed by Filter: 6,480
36. 1,903.021 2,000.571 ↓ 1.3 312,587 1

Sort (cost=73,722.28..74,322.85 rows=240,228 width=55) (actual time=1,944.708..2,000.571 rows=312,587 loops=1)

  • Sort Key: pltodrlneshpdtl.odrnum, pltodrlneshpdtl.txnidr
  • Sort Method: quicksort Memory: 46,539kB
37. 74.906 97.550 ↓ 1.3 312,587 1

Bitmap Heap Scan on pltodrlneshpdtl (cost=6,914.20..52,253.05 rows=240,228 width=55) (actual time=24.178..97.550 rows=312,587 loops=1)

  • Recheck Cond: ((prgcod)::text = 'PAMREX'::text)
  • Heap Blocks: exact=12,347
38. 22.644 22.644 ↓ 1.3 312,589 1

Bitmap Index Scan on idx_pltodrlneshpdtl_1 (cost=0.00..6,854.14 rows=240,228 width=0) (actual time=22.644..22.644 rows=312,589 loops=1)

  • Index Cond: ((prgcod)::text = 'PAMREX'::text)
39. 0.000 0.000 ↑ 1.0 1 466,120

Materialize (cost=0.00..5.03 rows=1 width=26) (actual time=0.000..0.000 rows=1 loops=466,120)

40. 0.031 0.031 ↑ 1.0 1 1

Seq Scan on pltprglocinf prgloc (cost=0.00..5.03 rows=1 width=26) (actual time=0.028..0.031 rows=1 loops=1)

  • Filter: (((prgcod)::text = 'PAMREX'::text) AND (lngcod = 1))
  • Rows Removed by Filter: 202
41. 5,127.320 5,127.320 ↑ 1.0 1 466,120

Index Scan using pltodrlnedet_pkey on pltodrlnedet lnedet (cost=0.56..5.53 rows=1 width=67) (actual time=0.011..0.011 rows=1 loops=466,120)

  • Index Cond: (((ordlin.prgcod)::text = (prgcod)::text) AND ((prgcod)::text = 'PAMREX'::text) AND ((ordlin.odrnum)::text = (odrnum)::text) AND (ordlin.txnidr = txnidr) AND ((backpost)::text = 'N'::text))
42. 23.505 23.505 ↓ 0.0 0 1

Index Scan using pltodrlnetxn_idx on pltodrlnetxn trxtnnew (cost=0.42..13,639.27 rows=684 width=22) (actual time=23.505..23.505 rows=0 loops=1)

  • Index Cond: (((txnflg)::text = 'N'::text) AND ((prgcod)::text = 'PAMREX'::text))
43. 14.346 14.346 ↓ 0.0 0 1

Index Scan using pltodrlnetxn_idx on pltodrlnetxn trxtnbackpost (cost=0.42..11,286.06 rows=23 width=22) (actual time=14.346..14.346 rows=0 loops=1)

  • Index Cond: (((txnflg)::text = 'B'::text) AND ((prgcod)::text = 'PAMREX'::text))
44. 593.268 791.021 ↓ 1.2 102,454 1

Sort (cost=30,856.72..31,062.55 rows=82,334 width=24) (actual time=760.644..791.021 rows=102,454 loops=1)

  • Sort Key: valdoc.ordnum, valdoc.odrlnenum
  • Sort Method: quicksort Memory: 11,077kB
45. 9.221 197.753 ↓ 1.2 102,454 1

Subquery Scan on valdoc (cost=22,487.80..24,134.48 rows=82,334 width=24) (actual time=162.650..197.753 rows=102,454 loops=1)

46. 86.681 188.532 ↓ 1.2 102,454 1

HashAggregate (cost=22,487.80..23,311.14 rows=82,334 width=24) (actual time=162.649..188.532 rows=102,454 loops=1)

  • Group Key: val.ordnum, val.prgcod, val.odrlnenum, val.valdoctyp, val.valdocsts
47. 101.851 101.851 ↓ 1.1 102,592 1

Seq Scan on pltvaldocmst val (cost=0.00..21,304.30 rows=94,680 width=24) (actual time=0.823..101.851 rows=102,592 loops=1)

  • Filter: (((prgcod)::text = 'PAMREX'::text) AND ((valdocsts)::text = ANY ('{I,U,E,O}'::text[])))
  • Rows Removed by Filter: 374,530
48. 0.578 1.406 ↑ 1.0 3,093 1

Hash (cost=131.93..131.93 rows=3,093 width=26) (actual time=1.406..1.406 rows=3,093 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 210kB
49. 0.828 0.828 ↑ 1.0 3,093 1

Seq Scan on merchant_master mermst (cost=0.00..131.93 rows=3,093 width=26) (actual time=0.007..0.828 rows=3,093 loops=1)

50. 8.667 29.292 ↓ 1.0 53,252 1

Hash (cost=3,140.51..3,140.51 rows=53,251 width=9) (actual time=29.292..29.292 rows=53,252 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 2,687kB
51. 20.625 20.625 ↓ 1.0 53,252 1

Seq Scan on product_details prddet (cost=0.00..3,140.51 rows=53,251 width=9) (actual time=0.005..20.625 rows=53,252 loops=1)

52. 0.053 0.666 ↑ 1.0 235 1

Hash (cost=185.84..185.84 rows=238 width=19) (actual time=0.666..0.666 rows=235 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 21kB
53. 0.613 0.613 ↑ 1.0 235 1

Seq Scan on pltcntlocinf cntloc (cost=0.00..185.84 rows=238 width=19) (actual time=0.006..0.613 rows=235 loops=1)

  • Filter: (lngcod = 1)
  • Rows Removed by Filter: 9,164
54. 0.000 0.000 ↑ 2.0 1 466,120

Materialize (cost=0.29..8.33 rows=2 width=6) (actual time=0.000..0.000 rows=1 loops=466,120)

55. 0.047 0.047 ↑ 2.0 1 1

Index Only Scan using pltusracsprg_pkey on pltusracsprg usrprg (cost=0.29..8.32 rows=2 width=6) (actual time=0.046..0.047 rows=1 loops=1)

  • Index Cond: ((usrnam = 'Vajiya'::text) AND (prgcod = 'PAMREX'::text))
  • Heap Fetches: 0
56.          

SubPlan (for Nested Loop)

57. 6,525.680 6,525.680 ↑ 1.0 1 466,120

Seq Scan on pltprglocinf inf (cost=0.00..5.03 rows=1 width=21) (actual time=0.014..0.014 rows=1 loops=466,120)

  • Filter: (((prgcod)::text = (ordmst.participating_prgcod)::text) AND (lngcod = 1))
  • Rows Removed by Filter: 202
58. 932.240 932.240 ↑ 1.0 1 466,120

Index Scan using merchant_contact_info_pkey on merchant_contact_info a (cost=0.29..8.31 rows=1 width=32) (actual time=0.002..0.002 rows=1 loops=466,120)

  • Index Cond: (contact_code = ordlin.warehouse_contact_id)
59. 2,796.720 2,796.720 ↑ 1.0 1 466,120

Index Scan using pltonetim_pkey on pltonetim (cost=0.29..8.30 rows=1 width=22) (actual time=0.005..0.006 rows=1 loops=466,120)

  • Index Cond: (((fldcod)::text = 'platform.boa.deliveryStatus'::text) AND ((fldval)::text = (ordlin.dlvsta)::text))
60. 0.000 0.000 ↓ 0.0 0

Subquery Scan on tmpatr (cost=0.56..8.76 rows=1 width=9) (never executed)

  • Filter: (tmpatr.rn = 1)
61. 0.000 0.000 ↓ 0.0 0

WindowAgg (cost=0.56..8.74 rows=1 width=17) (never executed)

62. 0.000 0.000 ↓ 0.0 0

Index Scan using pltodrlneatr_pkey on pltodrlneatr atr (cost=0.56..8.73 rows=1 width=9) (never executed)

  • Index Cond: (((odrnum)::text = (ordlin.odrnum)::text) AND (txnidr = ordlin.txnidr) AND ((prgcod)::text = (ordlin.prgcod)::text))
  • Filter: (upper((atrnam)::text) = 'PNR'::text)
63. 0.000 0.000 ↓ 0.0 0 466,120

Index Scan using pltonetim_pkey on pltonetim pltonetim_1 (cost=18.14..26.16 rows=1 width=22) (actual time=0.000..0.000 rows=0 loops=466,120)

  • Index Cond: (((fldcod)::text = 'platform.boa.reasonforFailure'::text) AND ((fldval)::text = ($8)::text))
64.          

Initplan (for Index Scan)

65. 23,306.000 23,306.000 ↓ 0.0 0 466,120

Seq Scan on error_codes err (cost=0.00..17.86 rows=3 width=8) (actual time=0.049..0.050 rows=0 loops=466,120)

  • Filter: (((loyerrcode)::character varying)::text = (ordmst.reason_failure)::text)
  • Rows Removed by Filter: 507
66. 22,839.880 22,839.880 ↓ 0.0 0 466,120

Seq Scan on error_codes err_cods (cost=0.00..17.86 rows=3 width=59) (actual time=0.049..0.049 rows=0 loops=466,120)

  • Filter: (((loyerrcode)::character varying)::text = (ordmst.reason_failure)::text)
  • Rows Removed by Filter: 507
Planning time : 21.252 ms
Execution time : 94,868.616 ms