explain.depesz.com

PostgreSQL's explain analyze made readable

Result: sKJy

Settings
# exclusive inclusive rows x rows loops node
1. 0.521 315.408 ↓ 223.0 223 1

Sort (cost=4,015.22..4,015.23 rows=1 width=1,017) (actual time=315.371..315.408 rows=223 loops=1)

  • Sort Key: co.contractid, ch.contracthistentry
  • Sort Method: quicksort Memory: 139kB
2.          

Initplan (for Sort)

3. 0.044 0.044 ↓ 0.0 0 1

Index Scan using parametervalue_valuecode_idx on parametervalue pp (cost=0.28..2.30 rows=1 width=4) (actual time=0.044..0.044 rows=0 loops=1)

  • Index Cond: ((valuecode)::text = 'excluded from automatic switch'::text)
  • Filter: ((parametercategorycode)::text = 'contractUserItemCode'::text)
4. 0.741 314.843 ↓ 223.0 223 1

Nested Loop Anti Join (cost=9.55..4,012.91 rows=1 width=1,017) (actual time=31.545..314.843 rows=223 loops=1)

5. 0.326 302.283 ↓ 223.0 223 1

Nested Loop Left Join (cost=9.12..4,005.54 rows=1 width=812) (actual time=31.351..302.283 rows=223 loops=1)

6. 0.303 300.842 ↓ 223.0 223 1

Nested Loop (cost=8.70..4,003.63 rows=1 width=598) (actual time=31.337..300.842 rows=223 loops=1)

7. 1.269 290.058 ↓ 223.0 223 1

Nested Loop Left Join (cost=4.51..3,999.42 rows=1 width=606) (actual time=31.054..290.058 rows=223 loops=1)

  • Join Filter: ((ac.externalid)::text = (deliverypoint.regulator)::text)
  • Rows Removed by Join Filter: 2,007
8. 0.321 288.343 ↓ 223.0 223 1

Nested Loop (cost=4.51..3,998.19 rows=1 width=610) (actual time=30.998..288.343 rows=223 loops=1)

9. 0.273 285.792 ↓ 223.0 223 1

Nested Loop (cost=4.09..3,997.71 rows=1 width=592) (actual time=30.959..285.792 rows=223 loops=1)

10. 0.303 283.289 ↓ 223.0 223 1

Nested Loop (cost=3.67..3,997.23 rows=1 width=588) (actual time=30.935..283.289 rows=223 loops=1)

11. 4.501 280.979 ↓ 223.0 223 1

Nested Loop Left Join (cost=3.25..3,995.25 rows=1 width=439) (actual time=30.925..280.979 rows=223 loops=1)

  • Filter: (contractstatus.defaultvaluebyinternalparameter OR ((datastatusdaynumber.valuecode IS NOT NULL) AND (contractstatus.defaultvaluebyinternalparameter IS FALSE) AND ((co.subscriptiondate + (datastatusdaynumber.valuecode)::integer) <= (now())::date)) OR ((datastatusdaynumber2.valuecode IS NOT NULL) AND (contractstatus.defaultvaluebyinternalparameter IS FALSE) AND ((cdp.fromdate - (datastatusdaynumber2.valuecode)::integer) <= (now())::date)))
  • Rows Removed by Filter: 6,802
12. 3.277 276.478 ↓ 7,025.0 7,025 1

Nested Loop Left Join (cost=2.98..3,992.91 rows=1 width=452) (actual time=0.234..276.478 rows=7,025 loops=1)

  • Join Filter: (contractstatus.parametervalueid = pvrelation2.parametervalueid)
13. 5.402 245.101 ↓ 7,025.0 7,025 1

Nested Loop Left Join (cost=2.83..3,990.74 rows=1 width=452) (actual time=0.228..245.101 rows=7,025 loops=1)

14. 2.474 239.699 ↓ 7,025.0 7,025 1

Nested Loop (cost=2.55..3,988.43 rows=1 width=448) (actual time=0.226..239.699 rows=7,025 loops=1)

15. 3.956 180.969 ↓ 50.2 7,032 1

Nested Loop Left Join (cost=2.13..3,922.97 rows=140 width=431) (actual time=0.211..180.969 rows=7,032 loops=1)

16. 5.421 120.821 ↓ 57.1 7,024 1

Nested Loop Left Join (cost=1.71..3,865.39 rows=123 width=395) (actual time=0.168..120.821 rows=7,024 loops=1)

  • Filter: ((datastatus.internalparametervaluecode IS NULL) OR ((datastatus.internalparametervaluecode)::text <> 'notValidated'::text))
17. 5.886 115.400 ↓ 57.1 7,024 1

Nested Loop Left Join (cost=1.43..3,827.15 rows=123 width=395) (actual time=0.165..115.400 rows=7,024 loops=1)

18. 9.006 81.418 ↓ 57.1 7,024 1

Nested Loop Left Join (cost=1.14..3,785.67 rows=123 width=377) (actual time=0.161..81.418 rows=7,024 loops=1)

19. 6.950 23.244 ↓ 57.1 7,024 1

Nested Loop Left Join (cost=0.84..3,743.04 rows=123 width=138) (actual time=0.154..23.244 rows=7,024 loops=1)

  • Join Filter: (co.supplierid = su.supplierid)
  • Rows Removed by Join Filter: 7,024
20. 3.414 16.294 ↓ 57.1 7,024 1

Nested Loop (cost=0.84..3,738.33 rows=123 width=106) (actual time=0.111..16.294 rows=7,024 loops=1)

21. 0.012 0.096 ↓ 4.0 8 1

Nested Loop Left Join (cost=0.43..5.41 rows=2 width=9) (actual time=0.065..0.096 rows=8 loops=1)

  • Join Filter: (contractstatus.parametervalueid = pvrelation.parametervalueid)
22. 0.068 0.068 ↓ 4.0 8 1

Index Scan using parametervalue_internalparametervaluecode_idx on parametervalue contractstatus (cost=0.28..3.22 rows=2 width=5) (actual time=0.045..0.068 rows=8 loops=1)

  • Index Cond: ((internalparametervaluecode)::text = 'inactive'::text)
23. 0.000 0.016 ↓ 0.0 0 8

Materialize (cost=0.14..2.17 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=8)

24. 0.016 0.016 ↓ 0.0 0 1

Index Scan using parametervaluerelation_usage_idx on parametervaluerelation pvrelation (cost=0.14..2.16 rows=1 width=8) (actual time=0.016..0.016 rows=0 loops=1)

  • Index Cond: ((usage)::text = 'AUTO_SW_NB_DAYS_TO_WAIT_AFTER_SUBSCRIPTION_DATE'::text)
25. 12.784 12.784 ↑ 19.7 878 8

Index Scan using idx_contract_contractstatusparamid on contract co (cost=0.42..1,693.31 rows=17,315 width=97) (actual time=0.020..1.598 rows=878 loops=8)

  • Index Cond: (contractstatusparamid = contractstatus.parametervalueid)
26. 0.000 0.000 ↑ 1.0 2 7,024

Materialize (cost=0.00..1.03 rows=2 width=36) (actual time=0.000..0.000 rows=2 loops=7,024)

27. 0.028 0.028 ↑ 1.0 2 1

Seq Scan on supplier su (cost=0.00..1.02 rows=2 width=36) (actual time=0.026..0.028 rows=2 loops=1)

28. 49.168 49.168 ↑ 1.0 1 7,024

Index Scan using customer_pkey on customer cu (cost=0.29..0.34 rows=1 width=239) (actual time=0.007..0.007 rows=1 loops=7,024)

  • Index Cond: (co.customerid = customerid)
29. 28.096 28.096 ↑ 1.0 1 7,024

Index Scan using idx_crmdata_customerid_pk on crmdata crm (cost=0.29..0.33 rows=1 width=22) (actual time=0.004..0.004 rows=1 loops=7,024)

  • Index Cond: (co.customerid = customerid)
30. 0.000 0.000 ↓ 0.0 0 7,024

Index Scan using parametervalueid on parametervalue datastatus (cost=0.28..0.30 rows=1 width=10) (actual time=0.000..0.000 rows=0 loops=7,024)

  • Index Cond: (parametervalueid = cu.customerdatastatusparamid)
31. 56.192 56.192 ↑ 1.0 1 7,024

Index Scan using "Key4" on contracthistory ch (cost=0.42..0.46 rows=1 width=36) (actual time=0.008..0.008 rows=1 loops=7,024)

  • Index Cond: (co.contractid = contractid)
32. 56.256 56.256 ↑ 1.0 1 7,032

Index Scan using contractdelively_contract_id_idx on contractdelivery cdp (cost=0.42..0.46 rows=1 width=21) (actual time=0.007..0.008 rows=1 loops=7,032)

  • Index Cond: (contractid = co.contractid)
  • Filter: ((NOT notallocatedtocustomer) AND (fromdate = todate))
  • Rows Removed by Filter: 0
33. 0.000 0.000 ↓ 0.0 0 7,025

Index Scan using parametervalueid on parametervalue datastatusdaynumber (cost=0.28..2.30 rows=1 width=12) (actual time=0.000..0.000 rows=0 loops=7,025)

  • Index Cond: (parametervalueid = pvrelation.proposedparametervalueid)
  • Filter: ((parametercategorycode)::text = 'numberOfDays'::text)
34. 28.100 28.100 ↓ 0.0 0 7,025

Index Scan using parametervaluerelation_usage_idx on parametervaluerelation pvrelation2 (cost=0.14..2.16 rows=1 width=8) (actual time=0.004..0.004 rows=0 loops=7,025)

  • Index Cond: ((usage)::text = 'AUTO_SW_NB_DAYS_TO_WAIT_BEFORE_EFFECTIVE_DATE'::text)
35. 0.000 0.000 ↓ 0.0 0 7,025

Index Scan using parametervalueid on parametervalue datastatusdaynumber2 (cost=0.28..2.30 rows=1 width=12) (actual time=0.000..0.000 rows=0 loops=7,025)

  • Index Cond: (parametervalueid = pvrelation2.proposedparametervalueid)
  • Filter: ((parametercategorycode)::text = 'numberOfDays'::text)
36. 2.007 2.007 ↑ 1.0 1 223

Index Scan using deliveryperiod_pkey on deliveryperiod (cost=0.42..1.97 rows=1 width=153) (actual time=0.009..0.009 rows=1 loops=223)

  • Index Cond: (deliveryperiodid = cdp.deliveryperiodid)
  • Filter: (deleted IS FALSE)
37. 2.230 2.230 ↑ 1.0 1 223

Index Scan using deliverypointcomponent_pk on deliverypointcomponent (cost=0.42..0.47 rows=1 width=4) (actual time=0.010..0.010 rows=1 loops=223)

  • Index Cond: (deliverypointcomponentid = deliveryperiod.deliverypointcomponentid)
  • Filter: (deleted IS FALSE)
38. 2.230 2.230 ↑ 1.0 1 223

Index Scan using deliverypoint_pkey on deliverypoint (cost=0.42..0.47 rows=1 width=22) (actual time=0.010..0.010 rows=1 loops=223)

  • Index Cond: (deliverypointid = deliveryperiod.deliverypointid)
  • Filter: (deleted IS FALSE)
39. 0.446 0.446 ↑ 1.0 10 223

Seq Scan on actor ac (cost=0.00..1.10 rows=10 width=32) (actual time=0.001..0.002 rows=10 loops=223)

40. 0.446 10.481 ↑ 1.0 1 223

Limit (cost=4.19..4.19 rows=1 width=1,313) (actual time=0.047..0.047 rows=1 loops=223)

41. 1.338 10.035 ↑ 1.0 1 223

Sort (cost=4.19..4.19 rows=1 width=1,313) (actual time=0.045..0.045 rows=1 loops=223)

  • Sort Key: generalconfig.todate DESC
  • Sort Method: quicksort Memory: 25kB
42. 5.352 8.697 ↓ 2.0 2 223

Bitmap Heap Scan on generalconfig (cost=3.16..4.18 rows=1 width=1,313) (actual time=0.033..0.039 rows=2 loops=223)

  • Recheck Cond: ((deliverypointcomponentid = deliverypointcomponent.deliverypointcomponentid) AND (deliverypointid = deliverypoint.deliverypointid))
  • Filter: ((deleted IS FALSE) AND ((todate IS NULL) OR ((sourceid)::text = 'USER'::text)))
  • Rows Removed by Filter: 2
  • Heap Blocks: exact=638
43. 0.223 3.345 ↓ 0.0 0 223

BitmapAnd (cost=3.16..3.16 rows=1 width=0) (actual time=0.015..0.015 rows=0 loops=223)

44. 1.784 1.784 ↑ 1.3 3 223

Bitmap Index Scan on generalconfig_deliverypointcomponentid_idx (cost=0.00..1.46 rows=4 width=0) (actual time=0.008..0.008 rows=3 loops=223)

  • Index Cond: (deliverypointcomponentid = deliverypointcomponent.deliverypointcomponentid)
45. 1.338 1.338 ↑ 1.3 3 223

Bitmap Index Scan on "IX_generalconfig_deliverypointid_generalconfig" (cost=0.00..1.46 rows=4 width=0) (actual time=0.006..0.006 rows=3 loops=223)

  • Index Cond: (deliverypointid = deliverypoint.deliverypointid)
46. 1.115 1.115 ↑ 1.0 1 223

Index Scan using idx_subscription_contractdpid_pk on subscription sub (cost=0.42..1.89 rows=1 width=214) (actual time=0.005..0.005 rows=1 loops=223)

  • Index Cond: (contractdeliveryid = cdp.contractdeliveryid)
47. 1.784 1.784 ↓ 0.0 0 223

Index Scan using idx_contractuserfields_contractid_pk on contractuserfields cuf (cost=0.43..0.69 rows=1 width=4) (actual time=0.008..0.008 rows=0 loops=223)

  • Index Cond: (contractid = co.contractid)
  • Filter: (useritemcodeparamid = $10)
  • Rows Removed by Filter: 7
48.          

SubPlan (for Nested Loop Anti Join)

49. 0.223 10.035 ↑ 1.0 1 223

Aggregate (cost=6.66..6.67 rows=1 width=8) (actual time=0.045..0.045 rows=1 loops=223)

50. 0.000 9.812 ↓ 0.0 0 223

Nested Loop (cost=2.39..6.66 rows=1 width=0) (actual time=0.044..0.044 rows=0 loops=223)

51. 0.202 9.812 ↓ 0.0 0 223

Nested Loop (cost=1.97..6.18 rows=1 width=4) (actual time=0.044..0.044 rows=0 loops=223)

52. 0.202 8.920 ↑ 1.0 1 223

Nested Loop (cost=1.68..5.82 rows=1 width=8) (actual time=0.039..0.040 rows=1 loops=223)

53. 0.195 8.028 ↑ 1.0 1 223

Nested Loop (cost=1.26..5.35 rows=1 width=8) (actual time=0.035..0.036 rows=1 loops=223)

54. 0.446 6.913 ↑ 1.0 1 223

Nested Loop (cost=0.84..4.88 rows=1 width=8) (actual time=0.030..0.031 rows=1 loops=223)

55. 5.129 5.129 ↑ 1.0 1 223

Index Scan using deliverypoint_gsrn_idx on deliverypoint deliverypoint_1 (cost=0.42..2.44 rows=1 width=4) (actual time=0.023..0.023 rows=1 loops=223)

  • Index Cond: ((gsrn)::text = (deliverypoint.gsrn)::text)
  • Filter: (deleted IS FALSE)
56. 1.338 1.338 ↑ 1.0 1 223

Index Scan using deliveryperiod_deliverypointid_supplier_sourceid_idx on deliveryperiod deliveryperiod_1 (cost=0.42..2.44 rows=1 width=12) (actual time=0.006..0.006 rows=1 loops=223)

  • Index Cond: (deliverypointid = deliverypoint_1.deliverypointid)
  • Filter: (deleted IS FALSE)
57. 0.920 0.920 ↑ 1.0 1 230

Index Scan using contractdelivery_deliveryperiodid_idx on contractdelivery cdp2 (cost=0.42..0.46 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=230)

  • Index Cond: (deliveryperiodid = deliveryperiod_1.deliveryperiodid)
58. 0.690 0.690 ↑ 1.0 1 230

Index Scan using contract_pkey on contract co2 (cost=0.42..0.46 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=230)

  • Index Cond: (contractid = cdp2.contractid)
59. 0.690 0.690 ↓ 0.0 0 230

Index Scan using customer_pkey on customer cu2 (cost=0.29..0.35 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=230)

  • Index Cond: (customerid = co2.customerid)
  • Filter: ((customerid <> cu.customerid) AND ((firstname)::text = (cu.firstname)::text) AND ((name)::text = (cu.name)::text) AND ((companyname)::text = (cu.companyname)::text))
  • Rows Removed by Filter: 1
60. 0.000 0.000 ↓ 0.0 0

Index Scan using deliverypointcomponent_pk on deliverypointcomponent deliverypointcomponent_1 (cost=0.42..0.47 rows=1 width=4) (never executed)

  • Index Cond: (deliverypointcomponentid = deliveryperiod_1.deliverypointcomponentid)
  • Filter: (deleted IS FALSE)
Planning time : 12.292 ms
Execution time : 317.619 ms