explain.depesz.com

PostgreSQL's explain analyze made readable

Result: QnVT

Settings
# exclusive inclusive rows x rows loops node
1. 6.392 74,090.074 ↓ 1.1 66,194 1

Append (cost=1,523,728.68..1,552,174.28 rows=58,165 width=979) (actual time=72,960.180..74,090.074 rows=66,194 loops=1)

2.          

CTE right_side

3. 21.194 60,680.059 ↑ 1.9 30,151 1

Hash Left Join (cost=855,197.42..863,775.50 rows=56,937 width=766) (actual time=60,330.794..60,680.059 rows=30,151 loops=1)

  • Hash Cond: (orders.order_id = mp.customerorderid)
4. 36.325 60,306.940 ↓ 63.9 30,151 1

Merge Right Join (cost=834,471.89..842,906.37 rows=472 width=688) (actual time=59,978.671..60,306.940 rows=30,151 loops=1)

  • Merge Cond: (a_1.payer_id = u_2.dopuserid)
5. 99.326 4,121.693 ↑ 1.3 26,540 1

GroupAggregate (cost=148,832.38..156,837.27 rows=33,817 width=164) (actual time=3,950.418..4,121.693 rows=26,540 loops=1)

  • Group Key: a_1.payer_id, a_1.error_code, a_1.error_desc
6. 829.741 4,022.367 ↑ 1.0 270,706 1

Sort (cost=148,832.38..149,529.35 rows=278,790 width=152) (actual time=3,950.371..4,022.367 rows=270,706 loops=1)

  • Sort Key: a_1.payer_id, a_1.error_code, a_1.error_desc
  • Sort Method: external sort Disk: 22728kB
7. 29.537 3,192.626 ↑ 1.0 270,706 1

Subquery Scan on a_1 (cost=88,015.99..102,652.47 rows=278,790 width=152) (actual time=2,832.928..3,192.626 rows=270,706 loops=1)

8. 198.514 3,163.089 ↑ 1.0 270,706 1

WindowAgg (cost=88,015.99..99,864.57 rows=278,790 width=160) (actual time=2,832.924..3,163.089 rows=270,706 loops=1)

9. 700.218 2,964.575 ↑ 1.0 270,706 1

Sort (cost=88,015.99..88,712.97 rows=278,790 width=143) (actual time=2,832.886..2,964.575 rows=270,706 loops=1)

  • Sort Key: pk.payer_id, pk.creation_date DESC
  • Sort Method: external merge Disk: 31152kB
10. 240.206 2,264.357 ↑ 1.0 270,706 1

Hash Join (cost=14,818.25..42,788.08 rows=278,790 width=143) (actual time=1,464.673..2,264.357 rows=270,706 loops=1)

  • Hash Cond: (pt.payment_id = pk.id)
11. 598.800 598.800 ↑ 1.0 276,774 1

Seq Scan on paymenttransaction pt (cost=0.00..14,749.96 rows=284,896 width=115) (actual time=28.031..598.800 rows=276,774 loops=1)

12. 116.678 1,425.351 ↓ 1.0 170,432 1

Hash (cost=10,266.58..10,266.58 rows=167,654 width=94) (actual time=1,425.351..1,425.351 rows=170,432 loops=1)

  • Buckets: 65536 Batches: 4 Memory Usage: 5864kB
13. 1,308.673 1,308.673 ↓ 1.0 170,432 1

Seq Scan on payment pk (cost=0.00..10,266.58 rows=167,654 width=94) (actual time=7.609..1,308.673 rows=170,432 loops=1)

  • Filter: (creation_date >= '2019-05-10 00:00:00'::timestamp without time zone)
  • Rows Removed by Filter: 3581
14. 18.872 56,148.922 ↓ 63.9 30,151 1

Materialize (cost=685,639.51..685,643.56 rows=472 width=524) (actual time=56,028.233..56,148.922 rows=30,151 loops=1)

15. 26.774 56,130.050 ↓ 63.9 30,151 1

Merge Left Join (cost=685,639.51..685,642.38 rows=472 width=524) (actual time=56,028.208..56,130.050 rows=30,151 loops=1)

  • Merge Cond: (d_u.dop_user_id = verification_event.dop_user_id)
  • Filter: ((orders.ordertype <> ALL ('{NEW_NUMBER,MNP_IN}'::text[])) OR (orders.order_status <> 'Cancelled'::text) OR (verification_event.verification_status IS NOT NULL))
  • Rows Removed by Filter: 1178
16. 224.756 23,131.041 ↓ 66.4 31,329 1

Sort (cost=435,440.19..435,441.37 rows=472 width=390) (actual time=23,100.852..23,131.041 rows=31,329 loops=1)

  • Sort Key: u_2.dopuserid
  • Sort Method: external merge Disk: 8920kB
17. 39.002 22,906.285 ↓ 66.4 31,329 1

Nested Loop (cost=430,128.38..435,419.23 rows=472 width=390) (actual time=11,433.356..22,906.285 rows=31,329 loops=1)

18. 103.850 11,637.181 ↓ 66.3 31,369 1

Hash Join (cost=430,127.95..434,572.36 rows=473 width=347) (actual time=11,385.468..11,637.181 rows=31,369 loops=1)

  • Hash Cond: (orders.owner_id = u_2.id)
19. 17.830 11,471.790 ↓ 66.3 31,372 1

Subquery Scan on orders (cost=423,921.55..426,994.46 rows=473 width=280) (actual time=11,321.617..11,471.790 rows=31,372 loops=1)

  • Filter: (orders.rownum = 1)
  • Rows Removed by Filter: 57221
20. 77.712 11,453.960 ↑ 1.1 88,593 1

WindowAgg (cost=423,921.55..425,812.57 rows=94,551 width=297) (actual time=11,321.604..11,453.960 rows=88,593 loops=1)

21. 672.284 11,376.248 ↑ 1.1 88,593 1

Sort (cost=423,921.55..424,157.93 rows=94,551 width=250) (actual time=11,321.591..11,376.248 rows=88,593 loops=1)

  • Sort Key: o_1.account_id, o_1.createddate DESC
  • Sort Method: external merge Disk: 14400kB
22. 107.244 10,703.964 ↑ 1.1 88,593 1

GroupAggregate (cost=399,122.42..404,795.48 rows=94,551 width=250) (actual time=10,350.613..10,703.964 rows=88,593 loops=1)

  • Group Key: o_1.order_id, x.state, x.npmessagecode, x.reasoncode, x.portingmode, x.processtype, o_1.ordertype, o_1.account_id, o_1.own
23. 1,361.584 10,596.720 ↓ 2.1 202,229 1

Sort (cost=399,122.42..399,358.80 rows=94,551 width=250) (actual time=10,350.594..10,596.720 rows=202,229 loops=1)

  • Sort Key: o_1.order_id, x.state, x.npmessagecode, x.reasoncode, x.portingmode, x.processtype, o_1.ordertype, o_1.account_id, o_
  • Sort Method: external merge Disk: 32688kB
24. 79.221 9,235.136 ↓ 2.1 202,229 1

Hash Left Join (cost=297,409.32..379,996.34 rows=94,551 width=250) (actual time=8,285.306..9,235.136 rows=202,229 loops=1)

  • Hash Cond: (o_1.order_id = x_1.orderid)
25. 8,369.054 8,487.468 ↓ 2.1 202,229 1

Hash Left Join (cost=271,997.15..353,638.46 rows=94,551 width=240) (actual time=7,616.830..8,487.468 rows=202,229 loops=1)

  • Hash Cond: (o_1.order_id = np.order_id)
  • -> Hash Left Join (cost=269280.37..350207.39 rows=94551 width=213) (actual time=7498.352..8290.147 rows=202199 lo
  • Hash Cond: (o_1.order_id = sim.order_id)
  • -> Hash Left Join (cost=11791.51..87163.32 rows=94551 width=117) (actual time=325.497..884.339 rows=202199
  • Hash Cond: (o_1.coodproductid = a_2.coodproductid)
  • -> Seq Scan on orderitems o_1 (cost=0.00..69554.82 rows=94551 width=147) (actual time=3.292..359.125
  • Filter: ((orderstatus <> 'Draft'::text) AND (COALESCE(ordertype, 'NEW_NUMBER'::text) <> ALL ('{Mo
  • Rows Removed by Filter: 590584
  • -> Hash (cost=9125.78..9125.78 rows=125178 width=44) (actual time=321.836..321.836 rows=125167 loops=
  • Buckets: 131072 Batches: 2 Memory Usage: 5713kB
  • -> Seq Scan on assets a_2 (cost=0.00..9125.78 rows=125178 width=44) (actual time=7.249..281.139
  • -> Hash (cost=254099.35..254099.35 rows=112681 width=115) (actual time=7170.282..7170.282 rows=168192 loops
  • Buckets: 65536 Batches: 4 Memory Usage: 3136kB
  • -> Subquery Scan on sim (cost=235979.55..254099.35 rows=112681 width=115) (actual time=3698.341..7100
  • -> GroupAggregate (cost=235979.55..252972.54 rows=112681 width=211) (actual time=3698.339..7077
  • Group Key: o1.order_id
  • -> Sort (cost=235979.55..237962.82 rows=793309 width=261) (actual time=3698.247..4455.126
  • Sort Key: o1.order_id
  • Sort Method: external merge Disk: 200184kB
  • -> Seq Scan on orderitems o1 (cost=0.00..60630.09 rows=793309 width=261) (actual ti
26. 34.345 118.414 ↑ 1.0 14,700 1

Hash (cost=2,525.67..2,525.67 rows=15,289 width=46) (actual time=118.414..118.414 rows=14,700 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 1137kB
  • -> Hash Left Join (cost=1887.73..2525.67 rows=15289 width=46) (actual time=89.719..114.732 rows=15289 loops
  • Hash Cond: (np.id = x.customerrequestid)
  • -> Seq Scan on numberportabilityrequests np (cost=0.00..560.89 rows=15289 width=38) (actual time=5.61
27. 84.069 84.069 ↓ 198.8 12,127 1

Hash (cost=1,886.96..1,886.96 rows=61 width=45) (actual time=84.069..84.069 rows=12,127 loops=1)

  • Buckets: 16384 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1022kB
  • -> Subquery Scan on x (cost=1491.73..1886.96 rows=61 width=45) (actual time=72.332..81.324 rows
  • Filter: (x.npc_rownumber = 1)
  • Rows Removed by Filter: 33
  • -> WindowAgg (cost=1491.73..1734.95 rows=12161 width=513) (actual time=72.326..79.799 row
  • -> Sort (cost=1491.73..1522.13 rows=12161 width=53) (actual time=71.718..72.543 row
  • Sort Key: npcase.customerrequestid, npcase.bpmprocesscreationdate DESC
  • Sort Method: quicksort Memory: 2093kB
  • -> Seq Scan on npcase (cost=0.00..666.61 rows=12161 width=53) (actual time=3.
28. 5.900 668.447 ↓ 704.6 16,911 1

Hash (cost=25,411.87..25,411.87 rows=24 width=29) (actual time=668.447..668.447 rows=16,911 loops=1)

  • Buckets: 32768 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1298kB
  • -> Subquery Scan on x_1 (cost=25257.82..25411.87 rows=24 width=29) (actual time=653.536..664.542 rows=16911 loops
  • Filter: (x_1.latest = 1)
  • Rows Removed by Filter: 2
29. 662.547 662.547 ↓ 3.6 16,913 1

WindowAgg (cost=25,257.82..25,352.62 rows=4,740 width=77) (actual time=653.533..662.547 rows=16,913 loops=1)

  • -> Sort (cost=25257.82..25269.67 rows=4740 width=37) (actual time=653.520..654.757 rows=16913 loops=1
  • Sort Key: ordershistory.orderid, ordershistory.createddate DESC
  • Sort Method: quicksort Memory: 2090kB
  • -> Seq Scan on ordershistory (cost=0.00..24968.42 rows=4740 width=37) (actual time=13.915..592.
  • Filter: ((field = 'Status'::text) AND (newvalue = 'Cancelled'::text))
  • Rows Removed by Filter: 600982
30. 28.138 61.541 ↑ 1.0 97,176 1

Hash (cost=3,662.70..3,662.70 rows=97,176 width=86) (actual time=61.540..61.541 rows=97,176 loops=1)

  • Buckets: 65536 Batches: 2 Memory Usage: 6288kB
31. 33.403 33.403 ↑ 1.0 97,176 1

Seq Scan on users u_2 (cost=0.00..3,662.70 rows=97,176 width=86) (actual time=0.024..33.403 rows=97,176 loops=1)

  • Filter: (email <> ALL ('{artstan2@gmail.com,mariusz.gaca@orange.com,artstan3@gmail.com,rafal.dop@prod.com}'::text[]))
  • Rows Removed by Filter: 4
32. 11,230.102 11,230.102 ↑ 1.0 1 31,369

Index Scan using users_pkey on users d_u (cost=0.42..1.79 rows=1 width=43) (actual time=0.358..0.358 rows=1 loops=31,369)

  • Index Cond: (dop_user_id = u_2.dopuserid)
  • Filter: ((NOT closed_record) AND (createddate >= '2019-05-09 00:00:00'::timestamp without time zone))
  • Rows Removed by Filter: 0
33. 4.324 32,972.235 ↓ 14,685.0 14,685 1

Materialize (cost=250,199.32..250,199.81 rows=1 width=171) (actual time=32,927.300..32,972.235 rows=14,685 loops=1)

34. 4.709 32,967.911 ↓ 14,685.0 14,685 1

Subquery Scan on verification_event (cost=250,199.32..250,199.81 rows=1 width=171) (actual time=32,927.284..32,967.911 rows=14,685 loops=1)

  • Filter: (verification_event.row_num = 1)
  • Rows Removed by Filter: 21479
35. 28.492 32,963.202 ↓ 2,781.8 36,164 1

WindowAgg (cost=250,199.32..250,199.65 rows=13 width=207) (actual time=32,927.264..32,963.202 rows=36,164 loops=1)

36. 101.114 32,934.710 ↓ 2,781.8 36,164 1

Sort (cost=250,199.32..250,199.35 rows=13 width=87) (actual time=32,927.208..32,934.710 rows=36,164 loops=1)

  • Sort Key: se_1.dop_user_id, onf.verified_at DESC
  • Sort Method: quicksort Memory: 6256kB
37. 32,681.008 32,833.596 ↓ 2,781.8 36,164 1

Hash Join (cost=1,380.16..250,199.08 rows=13 width=87) (actual time=152.782..32,833.596 rows=36,164 loops=1)

  • Hash Cond: (se_1.event_details_id = onf.event_details_id)
  • -> Index Scan using idx7asaen4a22lv0lsr88m955nw3 on subscriber_event se_1 (cost=0.57..248300.35 rows=197770 width=44) (actual time=0.080..32602
  • Index Cond: (event_type = 'CUSTOMER_VERIFICATION_STATUS_CHANGE'::text)
38. 152.588 152.588 ↓ 2.9 36,164 1

Hash (cost=1,224.03..1,224.03 rows=12,445 width=51) (actual time=152.588..152.588 rows=36,164 loops=1)

  • Buckets: 65536 (originally 16384) Batches: 1 (originally 1) Memory Usage: 3638kB
  • -> Seq Scan on kong_customer_verification_status_event onf (cost=0.00..1224.03 rows=12445 width=51) (actual time=6.929..143.639 rows=3616
  • Filter: ((verified_at)::date >= '2019-05-09'::date)
  • Rows Removed by Filter: 1104
39. 7.970 351.925 ↑ 1.0 23,771 1

Hash (cost=20,423.96..20,423.96 rows=24,126 width=83) (actual time=351.925..351.925 rows=23,771 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 2478kB
40. 120.784 343.955 ↑ 1.0 23,772 1

HashAggregate (cost=19,941.44..20,182.70 rows=24,126 width=83) (actual time=333.955..343.955 rows=23,772 loops=1)

  • Group Key: mp.customerorderid
41. 60.044 223.171 ↓ 1.0 170,432 1

Hash Left Join (cost=1,232.66..12,397.01 rows=167,654 width=44) (actual time=72.261..223.171 rows=170,432 loops=1)

  • Hash Cond: (pk_1.order_id = mp._id)
42. 90.963 90.963 ↓ 1.0 170,432 1

Seq Scan on payment pk_1 (cost=0.00..10,266.58 rows=167,654 width=48) (actual time=0.034..90.963 rows=170,432 loops=1)

  • Filter: (creation_date >= '2019-05-10 00:00:00'::timestamp without time zone)
  • Rows Removed by Filter: 3581
43. 8.077 72.164 ↑ 1.0 27,496 1

Hash (cost=888.96..888.96 rows=27,496 width=44) (actual time=72.164..72.164 rows=27,496 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 2297kB
44. 64.087 64.087 ↑ 1.0 27,496 1

Seq Scan on mongo_payment mp (cost=0.00..888.96 rows=27,496 width=44) (actual time=4.913..64.087 rows=27,496 loops=1)

45. 31.695 74,047.725 ↓ 29.4 36,043 1

Merge Left Join (cost=659,953.17..687,105.41 rows=1,228 width=851) (actual time=72,960.176..74,047.725 rows=36,043 loops=1)

  • Merge Cond: (orders_commercial.dopuserid = all_onf.dop_user_id)
46. 25.861 73,672.184 ↓ 29.4 36,043 1

Nested Loop (cost=409,628.61..436,769.57 rows=1,228 width=281) (actual time=72,685.551..73,672.184 rows=36,043 loops=1)

47. 63.888 72,948.586 ↓ 29.9 36,723 1

Subquery Scan on orders_commercial (cost=409,628.19..426,822.18 rows=1,228 width=275) (actual time=72,685.442..72,948.586 rows=36,723 loops=1)

  • Filter: ((NOT (hashed SubPlan 2)) AND (orders_commercial.rn = 1))
  • Rows Removed by Filter: 117767
48. 78.954 12,108.566 ↑ 3.2 154,490 1

WindowAgg (cost=408,347.11..418,172.25 rows=491,257 width=347) (actual time=11,893.234..12,108.566 rows=154,490 loops=1)

49. 581.568 12,029.612 ↑ 3.2 154,490 1

Sort (cost=408,347.11..409,575.25 rows=491,257 width=275) (actual time=11,893.207..12,029.612 rows=154,490 loops=1)

  • Sort Key: a.dopuserid, a.createddate DESC
  • Sort Method: external merge Disk: 29704kB
50. 26.397 11,448.044 ↑ 3.2 154,490 1

Subquery Scan on a (cost=267,396.23..298,099.79 rows=491,257 width=275) (actual time=7,757.608..11,448.044 rows=154,490 loops=1)

51. 2,715.095 11,421.647 ↑ 3.2 154,490 1

GroupAggregate (cost=267,396.23..293,187.22 rows=491,257 width=349) (actual time=7,757.607..11,421.647 rows=154,490 loops=1)

  • Group Key: o.account_id, o.owner_id, o.order_id, o.orderstatus, o.ordertype, u_1.dopuserid, u_1.email, u_1.createddate
52. 4,966.517 8,706.552 ↓ 1.4 710,570 1

Sort (cost=267,396.23..268,624.37 rows=491,257 width=399) (actual time=7,757.275..8,706.552 rows=710,570 loops=1)

  • Sort Key: o.account_id, o.owner_id, o.order_id, o.orderstatus, o.ordertype, u_1.dopuserid, u_1.email, u_1.createddate
  • Sort Method: external merge Disk: 281168kB
53. 1,022.881 3,740.035 ↓ 1.4 710,570 1

Hash Join (cost=5,720.55..131,962.91 rows=491,257 width=399) (actual time=184.077..3,740.035 rows=710,570 loops=1)

  • Hash Cond: (o.owner_id = u_1.id)
  • Join Filter: ((o.ordertype IS NULL) OR ((o.ordertype = ANY ('{Migration,MNP_IN}'::text[])) AND ((o.createddate)::date >= '2019-05-09'::date) AND ((
  • Rows Removed by Join Filter: 15
54. 2,537.970 2,537.970 ↓ 1.4 710,585 1

Seq Scan on orderitems o (cost=0.00..66,579.91 rows=511,065 width=332) (actual time=4.154..2,537.970 rows=710,585 loops=1)

  • Filter: ((ordertype IS NULL) OR ((ordertype = ANY ('{Migration,MNP_IN}'::text[])) AND ((createddate)::date >= '2019-05-09'::date)))
  • Rows Removed by Filter: 82198
55. 50.658 179.184 ↑ 1.0 97,180 1

Hash (cost=3,176.80..3,176.80 rows=97,180 width=86) (actual time=179.183..179.184 rows=97,180 loops=1)

  • Buckets: 65536 Batches: 2 Memory Usage: 6326kB
56. 128.526 128.526 ↑ 1.0 97,180 1

Seq Scan on users u_1 (cost=0.00..3,176.80 rows=97,180 width=86) (actual time=4.349..128.526 rows=97,180 loops=1)

57.          

SubPlan (for Subquery Scan)

58. 60,776.132 60,776.132 ↑ 1.9 30,151 1

CTE Scan on right_side right_side_1 (cost=0.00..1,138.74 rows=56,937 width=32) (actual time=60,330.822..60,776.132 rows=30,151 loops=1)

59. 697.737 697.737 ↑ 1.0 1 36,723

Index Scan using users_pkey on users u (cost=0.42..8.10 rows=1 width=43) (actual time=0.019..0.019 rows=1 loops=36,723)

  • Index Cond: (dop_user_id = orders_commercial.dopuserid)
  • Filter: ((NOT closed_record) AND (createddate >= '2019-05-09 00:00:00'::timestamp without time zone))
  • Rows Removed by Filter: 0
60. 3.467 343.846 ↓ 15,109.0 15,109 1

Materialize (cost=250,324.56..250,326.61 rows=1 width=95) (actual time=274.578..343.846 rows=15,109 loops=1)

61. 5.253 340.379 ↓ 15,109.0 15,109 1

Subquery Scan on all_onf (cost=250,324.56..250,326.61 rows=1 width=95) (actual time=274.563..340.379 rows=15,109 loops=1)

  • Filter: (all_onf.onfido_rn = 1)
  • Rows Removed by Filter: 22159
62. 24.898 335.126 ↓ 955.6 37,268 1

WindowAgg (cost=250,324.56..250,326.12 rows=39 width=179) (actual time=274.533..335.126 rows=37,268 loops=1)

63. 26.939 310.228 ↓ 955.6 37,268 1

WindowAgg (cost=250,324.56..250,325.44 rows=39 width=171) (actual time=274.512..310.228 rows=37,268 loops=1)

64. 130.927 283.289 ↓ 955.6 37,268 1

Sort (cost=250,324.56..250,324.66 rows=39 width=131) (actual time=274.486..283.289 rows=37,268 loops=1)

  • Sort Key: se.dop_user_id, onfido.verified_at DESC
  • Sort Method: external merge Disk: 5512kB
65. 138.127 152.362 ↓ 955.6 37,268 1

Hash Join (cost=1,504.61..250,323.53 rows=39 width=131) (actual time=15.515..152.362 rows=37,268 loops=1)

  • Hash Cond: (se.event_details_id = onfido.event_details_id)
  • -> Index Scan using idx7asaen4a22lv0lsr88m955nw3 on subscriber_event se (cost=0.57..248300.35 rows=197770 width=44) (actual time=0.072..113.105 rows=37268 lo
  • Index Cond: (event_type = 'CUSTOMER_VERIFICATION_STATUS_CHANGE'::text)
66. 6.498 14.235 ↑ 1.0 37,268 1

Hash (cost=1,037.35..1,037.35 rows=37,335 width=51) (actual time=14.235..14.235 rows=37,268 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 3751kB
67. 7.737 7.737 ↑ 1.0 37,268 1

Seq Scan on kong_customer_verification_status_event onfido (cost=0.00..1,037.35 rows=37,335 width=51) (actual time=0.018..7.737 rows=37,268 loops=1)

68. 35.957 35.957 ↑ 1.9 30,151 1

CTE Scan on right_side (cost=0.00..1,281.08 rows=56,937 width=982) (actual time=0.108..35.957 rows=30,151 loops=1)

Planning time : 320.515 ms
Execution time : 74,324.173 ms