explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 8Njf

Settings
# exclusive inclusive rows x rows loops node
1. 20.013 95,712.085 ↓ 2,586.0 2,586 1

Sort (cost=272.50..272.51 rows=1 width=682) (actual time=95,711.682..95,712.085 rows=2,586 loops=1)

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

Initplan (for Sort)

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

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

Nested Loop Anti Join (cost=17.30..270.19 rows=1 width=682) (actual time=55.435..95,692.045 rows=2,586 loops=1)

5. 14.597 8,948.318 ↓ 2,586.0 2,586 1

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

6. 10.840 8,915.619 ↓ 2,586.0 2,586 1

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

7. 39.791 351.321 ↓ 2,603.0 2,603 1

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

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

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

9. 9.581 278.623 ↓ 2,603.0 2,603 1

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

10. 10.253 261.233 ↓ 2,603.0 2,603 1

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

11. 10.424 240.568 ↓ 2,603.0 2,603 1

Nested Loop Left Join (cost=11.99..253.13 rows=1 width=322) (actual time=0.271..240.568 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.612 230.144 ↓ 2,810.0 2,810 1

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

13. 9.681 214.102 ↓ 2,810.0 2,810 1

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

14. 7.560 204.421 ↓ 2,810.0 2,810 1

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

15. 12.996 177.509 ↓ 219.9 4,838 1

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

16. 4.122 140.343 ↓ 230.2 4,834 1

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

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

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

18. 7.331 96.562 ↓ 230.2 4,834 1

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

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

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

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

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

21. 15.977 17.982 ↓ 263.7 6,328 1

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

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

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

23. 0.008 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.041 0.041 ↑ 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.028..0.041 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. 4.822 4.834 ↑ 1.0 1 4,834

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

31. 0.012 0.012 ↑ 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.012 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.002..0.006 rows=10 loops=2,603)

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

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

42. 2,876.315 8,537.840 ↑ 1.0 1 2,603

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

  • Sort Key: generalconfig.todate DESC
  • Sort Method: quicksort Memory: 25kB
43. 4,878.022 5,661.525 ↓ 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.321..2.175 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 783.503 ↓ 0.0 0 2,603

BitmapAnd (cost=2.89..2.89 rows=1 width=0) (actual time=0.301..0.301 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. 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. 809.418 86,703.408 ↑ 1.0 1 2,586

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

51. 3,758.158 85,893.990 ↓ 1,875.0 1,875 2,586

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

52. 3,155.620 72,048.546 ↓ 1,950.0 1,950 2,586

Nested Loop (cost=1.41..7.02 rows=1 width=4) (actual time=0.071..27.861 rows=1,950 loops=2,586)

53. 5,302.000 58,805.640 ↓ 1,950.0 1,950 2,586

Nested Loop (cost=1.13..6.72 rows=1 width=8) (actual time=0.067..22.740 rows=1,950 loops=2,586)

54. 4,980.720 43,416.354 ↓ 1,950.0 1,950 2,586

Nested Loop (cost=0.85..6.40 rows=1 width=4) (actual time=0.062..16.789 rows=1,950 loops=2,586)

55. 5,223.814 27,181.446 ↓ 2,176.0 2,176 2,586

Nested Loop (cost=0.57..6.08 rows=1 width=4) (actual time=0.057..10.511 rows=2,176 loops=2,586)

56. 7,145.118 7,145.118 ↓ 2,864.0 2,864 2,586

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

  • Index Cond: ((name)::text = (cu.name)::text)
  • Filter: ((customerid <> cu.customerid) AND ((firstname)::text = (cu.firstname)::text) AND ((companyname)::text = (cu.companyname)::text))
  • Rows Removed by Filter: 999
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 : 20.725 ms
Execution time : 95,713.197 ms