explain.depesz.com

PostgreSQL's explain analyze made readable

Result: HMAyb

Settings
# exclusive inclusive rows x rows loops node
1. 26.748 97,804.988 ↓ 2,586.0 2,586 1

Sort (cost=272.50..272.51 rows=1 width=682) (actual time=97,804.549..97,804.988 rows=2,586 loops=1)

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

Initplan (for Sort)

3. 0.029 0.029 ↓ 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.029..0.029 rows=0 loops=1)

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

Nested Loop Anti Join (cost=17.30..270.19 rows=1 width=682) (actual time=40.470..97,778.211 rows=2,586 loops=1)

5. 15.488 8,987.840 ↓ 2,586.0 2,586 1

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

6. 10.107 8,954.250 ↓ 2,586.0 2,586 1

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

7. 39.190 354.243 ↓ 2,603.0 2,603 1

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

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

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

9. 10.617 281.429 ↓ 2,603.0 2,603 1

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

10. 10.267 263.003 ↓ 2,603.0 2,603 1

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

11. 9.994 242.324 ↓ 2,603.0 2,603 1

Nested Loop Left Join (cost=11.99..253.13 rows=1 width=322) (actual time=0.436..242.324 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.943 232.330 ↓ 2,810.0 2,810 1

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

13. 9.929 215.957 ↓ 2,810.0 2,810 1

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

14. 7.815 206.028 ↓ 2,810.0 2,810 1

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

15. 12.769 178.861 ↓ 219.9 4,838 1

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

16. 9.952 141.922 ↓ 230.2 4,834 1

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

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

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

18. 7.915 96.491 ↓ 230.2 4,834 1

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

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

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

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

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

21. 16.120 18.274 ↓ 263.7 6,328 1

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

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

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

23. 0.007 0.049 ↑ 1.0 9 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
24. 0.042 0.042 ↑ 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.029..0.042 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.825 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.009 0.009 ↑ 1.0 2 1

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

29. 24.170 24.170 ↑ 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.005..0.005 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.027 0.027 ↑ 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.027..0.027 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. 18.221 8,589.900 ↑ 1.0 1 2,603

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

42. 2,897.139 8,571.679 ↑ 1.0 1 2,603

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

  • Sort Key: generalconfig.todate DESC
  • Sort Method: quicksort Memory: 25kB
43. 4,885.831 5,674.540 ↓ 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.324..2.180 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.970 788.709 ↓ 0.0 0 2,603

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

45. 390.450 390.450 ↓ 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.150..0.150 rows=2,203 loops=2,603)

  • Index Cond: (deliverypointcomponentid = deliverypointcomponent.deliverypointcomponentid)
46. 380.289 380.289 ↓ 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.147..0.147 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. 840.450 88,748.934 ↑ 1.0 1 2,586

Aggregate (cost=7.35..7.36 rows=1 width=8) (actual time=34.319..34.319 rows=1 loops=2,586)

51. 4,070.434 87,908.484 ↓ 1,875.0 1,875 2,586

Nested Loop (cost=1.68..7.35 rows=1 width=0) (actual time=0.551..33.994 rows=1,875 loops=2,586)

52. 3,550.648 73,744.962 ↓ 1,951.0 1,951 2,586

Nested Loop (cost=1.41..7.02 rows=1 width=4) (actual time=0.056..28.517 rows=1,951 loops=2,586)

53. 5,567.728 60,101.226 ↓ 1,951.0 1,951 2,586

Nested Loop (cost=1.13..6.72 rows=1 width=8) (actual time=0.052..23.241 rows=1,951 loops=2,586)

54. 5,263.654 44,440.410 ↓ 1,951.0 1,951 2,586

Nested Loop (cost=0.85..6.40 rows=1 width=4) (actual time=0.047..17.185 rows=1,951 loops=2,586)

55. 5,532.588 27,913.284 ↓ 2,178.0 2,178 2,586

Nested Loop (cost=0.57..6.08 rows=1 width=4) (actual time=0.031..10.794 rows=2,178 loops=2,586)

56. 7,494.228 7,494.228 ↓ 2,878.0 2,878 2,586

Index Scan using idx_customer_name on customer cu2 (cost=0.29..3.29 rows=1 width=4) (actual time=0.022..2.898 rows=2,878 loops=2,586)

  • Index Cond: ((name)::text = (cu.name)::text)
  • Filter: ((customerid <> cu.customerid) AND ((COALESCE(firstname, ''::character varying))::text = (COALESCE(cu.firstname, ''::character varying))::text) AND ((COALESCE(companyname, ''::character varying))::text = (COALESCE(cu.companyname, ''::character varying))::text))
  • Rows Removed by Filter: 985
57. 14,886.468 14,886.468 ↑ 2.0 1 7,443,234

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,443,234)

  • Index Cond: (customerid = cu2.customerid)
58. 11,263.472 11,263.472 ↑ 1.0 1 5,631,736

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,631,736)

  • Index Cond: (contractid = co2.contractid)
59. 10,093.088 10,093.088 ↑ 1.0 1 5,046,544

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,046,544)

  • Index Cond: (deliveryperiodid = cdp2.deliveryperiodid)
  • Filter: (deleted IS FALSE)
60. 10,093.088 10,093.088 ↑ 1.0 1 5,046,544

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,046,544)

  • Index Cond: (deliverypointcomponentid = deliveryperiod_1.deliverypointcomponentid)
  • Filter: (deleted IS FALSE)
61. 10,093.088 10,093.088 ↑ 1.0 1 5,046,544

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,046,544)

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