explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 0wZ6

Settings
# exclusive inclusive rows x rows loops node
1. 19.917 93,232.709 ↓ 2,586.0 2,586 1

Sort (cost=271.52..271.53 rows=1 width=682) (actual time=93,232.317..93,232.709 rows=2,586 loops=1)

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

Initplan (for Sort)

3. 0.021 0.021 ↓ 0.0 0 1

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

  • Index Cond: (((valuecode)::text = 'excluded from automatic switch'::text) AND ((parametercategorycode)::text = 'contractUserItemCode'::text))
4. 22.052 93,212.771 ↓ 2,586.0 2,586 1

Nested Loop Anti Join (cost=17.30..269.21 rows=1 width=682) (actual time=48.981..93,212.771 rows=2,586 loops=1)

5. 14.368 8,881.947 ↓ 2,586.0 2,586 1

Nested Loop Left Join (cost=17.02..262.50 rows=1 width=586) (actual time=5.425..8,881.947 rows=2,586 loops=1)

6. 9.919 8,849.477 ↓ 2,586.0 2,586 1

Nested Loop (cost=16.74..260.73 rows=1 width=481) (actual time=5.399..8,849.477 rows=2,586 loops=1)

7. 38.863 348.572 ↓ 2,603.0 2,603 1

Nested Loop Left Join (cost=12.83..256.79 rows=1 width=489) (actual time=0.393..348.572 rows=2,603 loops=1)

  • Join Filter: ((ac.externalid)::text = (deliverypoint.regulator)::text)
  • Rows Removed by Join Filter: 23,428
8. 9.224 294.091 ↓ 2,603.0 2,603 1

Nested Loop (cost=12.83..255.56 rows=1 width=493) (actual time=0.370..294.091 rows=2,603 loops=1)

9. 10.014 277.058 ↓ 2,603.0 2,603 1

Nested Loop (cost=12.55..255.26 rows=1 width=475) (actual time=0.343..277.058 rows=2,603 loops=1)

10. 9.980 259.235 ↓ 2,603.0 2,603 1

Nested Loop (cost=12.27..254.96 rows=1 width=471) (actual time=0.320..259.235 rows=2,603 loops=1)

11. 9.813 238.843 ↓ 2,603.0 2,603 1

Nested Loop Left Join (cost=11.99..253.13 rows=1 width=322) (actual time=0.295..238.843 rows=2,603 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: 207
12. 7.659 229.030 ↓ 2,810.0 2,810 1

Nested Loop Left Join (cost=11.71..250.79 rows=1 width=335) (actual time=0.267..229.030 rows=2,810 loops=1)

13. 9.343 212.941 ↓ 2,810.0 2,810 1

Nested Loop Left Join (cost=11.57..250.06 rows=1 width=335) (actual time=0.252..212.941 rows=2,810 loops=1)

14. 8.226 203.598 ↓ 2,810.0 2,810 1

Nested Loop (cost=11.29..247.19 rows=1 width=331) (actual time=0.235..203.598 rows=2,810 loops=1)

15. 12.996 176.020 ↓ 219.9 4,838 1

Nested Loop Left Join (cost=11.01..240.20 rows=22 width=314) (actual time=0.210..176.020 rows=4,838 loops=1)

16. 9.835 138.854 ↓ 230.2 4,834 1

Nested Loop Left Join (cost=10.72..233.36 rows=21 width=278) (actual time=0.191..138.854 rows=4,834 loops=1)

  • Join Filter: (contractstatus.parametervalueid = pvrelation.parametervalueid)
  • Rows Removed by Join Filter: 4,827
17. 15.384 129.019 ↓ 230.2 4,834 1

Nested Loop Left Join (cost=10.58..230.88 rows=21 width=274) (actual time=0.166..129.019 rows=4,834 loops=1)

18. 7.183 94.299 ↓ 230.2 4,834 1

Nested Loop Left Join (cost=10.29..224.11 rows=21 width=256) (actual time=0.145..94.299 rows=4,834 loops=1)

  • Join Filter: (co.supplierid = su.supplierid)
  • Rows Removed by Join Filter: 4,703
19. 10.864 82.282 ↓ 230.2 4,834 1

Nested Loop Left Join (cost=10.29..222.55 rows=21 width=224) (actual time=0.116..82.282 rows=4,834 loops=1)

  • Filter: ((datastatus.internalparametervaluecode IS NULL) OR ((datastatus.internalparametervaluecode)::text <> 'notValidated'::text))
  • Rows Removed by Filter: 1,494
20. 15.909 58.762 ↓ 263.7 6,328 1

Nested Loop Left Join (cost=10.01..214.85 rows=24 width=224) (actual time=0.101..58.762 rows=6,328 loops=1)

21. 15.314 17.541 ↓ 263.7 6,328 1

Hash Join (cost=9.73..206.78 rows=24 width=77) (actual time=0.080..17.541 rows=6,328 loops=1)

  • Hash Cond: (co.contractstatusparamid = contractstatus.parametervalueid)
22. 2.178 2.178 ↑ 1.0 6,944 1

Seq Scan on contract co (cost=0.00..178.72 rows=6,972 width=72) (actual time=0.012..2.178 rows=6,944 loops=1)

23. 0.006 0.049 ↑ 1.0 9 1

Hash (cost=9.62..9.62 rows=9 width=5) (actual time=0.049..0.049 rows=9 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
24. 0.043 0.043 ↑ 1.0 9 1

Index Scan using parametervalue_internalparametervaluecode_idx on parametervalue contractstatus (cost=0.28..9.62 rows=9 width=5) (actual time=0.030..0.043 rows=9 loops=1)

  • Index Cond: ((internalparametervaluecode)::text = 'inactive'::text)
25. 25.312 25.312 ↑ 1.0 1 6,328

Index Scan using customer_pkey on customer cu (cost=0.29..0.34 rows=1 width=147) (actual time=0.004..0.004 rows=1 loops=6,328)

  • Index Cond: (customerid = co.customerid)
26. 12.656 12.656 ↓ 0.0 0 6,328

Index Scan using parametervalueid on parametervalue datastatus (cost=0.28..0.31 rows=1 width=10) (actual time=0.002..0.002 rows=0 loops=6,328)

  • Index Cond: (parametervalueid = cu.customerdatastatusparamid)
27. 4.824 4.834 ↑ 1.0 2 4,834

Materialize (cost=0.00..1.03 rows=2 width=36) (actual time=0.001..0.001 rows=2 loops=4,834)

28. 0.010 0.010 ↑ 1.0 2 1

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

29. 19.336 19.336 ↑ 1.0 1 4,834

Index Scan using idx_crmdata_customerid_pk on crmdata crm (cost=0.29..0.32 rows=1 width=22) (actual time=0.004..0.004 rows=1 loops=4,834)

  • Index Cond: (customerid = co.customerid)
30. 0.000 0.000 ↑ 1.0 1 4,834

Materialize (cost=0.14..2.17 rows=1 width=8) (actual time=0.000..0.000 rows=1 loops=4,834)

31. 0.013 0.013 ↑ 1.0 1 1

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

  • Index Cond: ((usage)::text = 'AUTO_SW_NB_DAYS_TO_WAIT_AFTER_SUBSCRIPTION_DATE'::text)
32. 24.170 24.170 ↑ 1.0 1 4,834

Index Scan using idx_contracthistory_contractid_pk on contracthistory ch (cost=0.28..0.32 rows=1 width=36) (actual time=0.004..0.005 rows=1 loops=4,834)

  • Index Cond: (contractid = co.contractid)
33. 19.352 19.352 ↑ 1.0 1 4,838

Index Scan using contractdelively_contract_id_idx on contractdelivery cdp (cost=0.28..0.31 rows=1 width=21) (actual time=0.003..0.004 rows=1 loops=4,838)

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

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=2,810)

  • Index Cond: (parametervalueid = pvrelation.proposedparametervalueid)
  • Filter: ((parametercategorycode)::text = 'numberOfDays'::text)
35. 8.430 8.430 ↓ 0.0 0 2,810

Index Scan using parametervaluerelation_parametervalueid_idx on parametervaluerelation pvrelation2 (cost=0.14..0.72 rows=1 width=8) (actual time=0.003..0.003 rows=0 loops=2,810)

  • Index Cond: (parametervalueid = contractstatus.parametervalueid)
  • Filter: ((usage)::text = 'AUTO_SW_NB_DAYS_TO_WAIT_BEFORE_EFFECTIVE_DATE'::text)
  • Rows Removed by Filter: 0
36. 0.000 0.000 ↓ 0.0 0 2,810

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=2,810)

  • Index Cond: (parametervalueid = pvrelation2.proposedparametervalueid)
  • Filter: ((parametercategorycode)::text = 'numberOfDays'::text)
37. 10.412 10.412 ↑ 1.0 1 2,603

Index Scan using deliveryperiod_pkey on deliveryperiod (cost=0.28..1.83 rows=1 width=153) (actual time=0.004..0.004 rows=1 loops=2,603)

  • Index Cond: (deliveryperiodid = cdp.deliveryperiodid)
  • Filter: (deleted IS FALSE)
38. 7.809 7.809 ↑ 1.0 1 2,603

Index Scan using deliverypointcomponent_pk on deliverypointcomponent (cost=0.28..0.30 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=2,603)

  • Index Cond: (deliverypointcomponentid = deliveryperiod.deliverypointcomponentid)
  • Filter: (deleted IS FALSE)
39. 7.809 7.809 ↑ 1.0 1 2,603

Index Scan using deliverypoint_pkey on deliverypoint (cost=0.28..0.30 rows=1 width=22) (actual time=0.003..0.003 rows=1 loops=2,603)

  • Index Cond: (deliverypointid = deliveryperiod.deliverypointid)
  • Filter: (deleted IS FALSE)
40. 15.618 15.618 ↑ 1.0 10 2,603

Seq Scan on actor ac (cost=0.00..1.10 rows=10 width=32) (actual time=0.003..0.006 rows=10 loops=2,603)

41. 15.618 8,490.986 ↑ 1.0 1 2,603

Limit (cost=3.92..3.92 rows=1 width=1,313) (actual time=3.259..3.262 rows=1 loops=2,603)

42. 2,850.285 8,475.368 ↑ 1.0 1 2,603

Sort (cost=3.92..3.92 rows=1 width=1,313) (actual time=3.255..3.256 rows=1 loops=2,603)

  • Sort Key: generalconfig.todate DESC
  • Sort Method: quicksort Memory: 25kB
43. 4,846.786 5,625.083 ↓ 2,202.0 2,202 2,603

Bitmap Heap Scan on generalconfig (cost=2.89..3.91 rows=1 width=1,313) (actual time=0.319..2.161 rows=2,202 loops=2,603)

  • 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: 1
  • Heap Blocks: exact=206,234
44. 17.938 778.297 ↓ 0.0 0 2,603

BitmapAnd (cost=2.89..2.89 rows=1 width=0) (actual time=0.299..0.299 rows=0 loops=2,603)

45. 385.244 385.244 ↓ 440.6 2,203 2,603

Bitmap Index Scan on generalconfig_deliverypointcomponentid_idx (cost=0.00..1.32 rows=5 width=0) (actual time=0.148..0.148 rows=2,203 loops=2,603)

  • Index Cond: (deliverypointcomponentid = deliverypointcomponent.deliverypointcomponentid)
46. 375.115 375.115 ↓ 443.4 2,217 2,587

Bitmap Index Scan on "IX_generalconfig_deliverypointid_generalconfig" (cost=0.00..1.32 rows=5 width=0) (actual time=0.145..0.145 rows=2,217 loops=2,587)

  • Index Cond: (deliverypointid = deliverypoint.deliverypointid)
47. 18.102 18.102 ↑ 1.0 1 2,586

Index Scan using subscription_pkey on subscription sub (cost=0.28..1.77 rows=1 width=105) (actual time=0.007..0.007 rows=1 loops=2,586)

  • Index Cond: (contractdeliveryid = cdp.contractdeliveryid)
48. 18.102 18.102 ↓ 0.0 0 2,586

Index Scan using idx_contractuserfields_contractid_pk on contractuserfields cuf (cost=0.28..0.31 rows=1 width=4) (actual time=0.007..0.007 rows=0 loops=2,586)

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

SubPlan (for Nested Loop Anti Join)

50. 806.832 84,290.670 ↑ 1.0 1 2,586

Aggregate (cost=6.37..6.38 rows=1 width=8) (actual time=32.595..32.595 rows=1 loops=2,586)

51. 3,654.718 83,483.838 ↓ 1,875.0 1,875 2,586

Nested Loop (cost=1.68..6.37 rows=1 width=0) (actual time=0.433..32.283 rows=1,875 loops=2,586)

52. 3,209.926 69,741.834 ↓ 1,950.0 1,950 2,586

Nested Loop (cost=1.41..6.04 rows=1 width=4) (actual time=0.043..26.969 rows=1,950 loops=2,586)

53. 5,289.070 56,444.622 ↓ 1,950.0 1,950 2,586

Nested Loop (cost=1.13..5.74 rows=1 width=8) (actual time=0.039..21.827 rows=1,950 loops=2,586)

54. 5,009.166 41,068.266 ↓ 1,950.0 1,950 2,586

Nested Loop (cost=0.85..5.42 rows=1 width=4) (actual time=0.034..15.881 rows=1,950 loops=2,586)

55. 4,952.284 24,804.912 ↓ 2,176.0 2,176 2,586

Nested Loop (cost=0.57..5.10 rows=1 width=4) (actual time=0.030..9.592 rows=2,176 loops=2,586)

56. 5,040.114 5,040.114 ↓ 2,864.0 2,864 2,586

Index Scan using idx_customer_name_first_company on customer cu2 (cost=0.29..2.31 rows=1 width=4) (actual time=0.024..1.949 rows=2,864 loops=2,586)

  • Index Cond: (((name)::text = (cu.name)::text) AND ((firstname)::text = (cu.firstname)::text) AND ((companyname)::text = (cu.companyname)::text))
  • Filter: (customerid <> cu.customerid)
  • Rows Removed by Filter: 1
57. 14,812.514 14,812.514 ↑ 2.0 1 7,406,257

Index Scan using idx_contract_customerid_pk on contract co2 (cost=0.28..2.77 rows=2 width=8) (actual time=0.002..0.002 rows=1 loops=7,406,257)

  • Index Cond: (customerid = cu2.customerid)
58. 11,254.188 11,254.188 ↑ 1.0 1 5,627,094

Index Scan using contractdelively_contract_id_idx on contractdelivery cdp2 (cost=0.28..0.30 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=5,627,094)

  • Index Cond: (contractid = co2.contractid)
59. 10,087.286 10,087.286 ↑ 1.0 1 5,043,643

Index Scan using deliveryperiod_pkey on deliveryperiod deliveryperiod_1 (cost=0.28..0.32 rows=1 width=12) (actual time=0.002..0.002 rows=1 loops=5,043,643)

  • Index Cond: (deliveryperiodid = cdp2.deliveryperiodid)
  • Filter: (deleted IS FALSE)
60. 10,087.286 10,087.286 ↑ 1.0 1 5,043,643

Index Scan using deliverypointcomponent_pk on deliverypointcomponent deliverypointcomponent_1 (cost=0.28..0.30 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=5,043,643)

  • Index Cond: (deliverypointcomponentid = deliveryperiod_1.deliverypointcomponentid)
  • Filter: (deleted IS FALSE)
61. 10,087.286 10,087.286 ↑ 1.0 1 5,043,643

Index Scan using deliverypoint_pkey on deliverypoint deliverypoint_1 (cost=0.28..0.30 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=5,043,643)

  • Index Cond: (deliverypointid = deliveryperiod_1.deliverypointid)
  • Filter: ((deleted IS FALSE) AND ((gsrn)::text = (deliverypoint.gsrn)::text))
  • Rows Removed by Filter: 0
Planning time : 24.353 ms
Execution time : 93,233.652 ms