explain.depesz.com

PostgreSQL's explain analyze made readable

Result: dv1t

Settings
# exclusive inclusive rows x rows loops node
1. 23.437 100,669.515 ↓ 2,586.0 2,586 1

Sort (cost=272.50..272.51 rows=1 width=682) (actual time=100,669.118..100,669.515 rows=2,586 loops=1)

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

Initplan (for Sort)

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

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

Nested Loop Anti Join (cost=17.30..270.19 rows=1 width=682) (actual time=46.530..100,646.061 rows=2,586 loops=1)

5. 16.293 9,339.803 ↓ 2,586.0 2,586 1

Nested Loop Left Join (cost=17.02..262.50 rows=1 width=586) (actual time=4.381..9,339.803 rows=2,586 loops=1)

6. 10.142 9,305.408 ↓ 2,586.0 2,586 1

Nested Loop (cost=16.74..260.73 rows=1 width=481) (actual time=4.357..9,305.408 rows=2,586 loops=1)

7. 42.267 366.976 ↓ 2,603.0 2,603 1

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

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

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

9. 9.246 290.818 ↓ 2,603.0 2,603 1

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

10. 10.976 271.160 ↓ 2,603.0 2,603 1

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

11. 10.124 249.772 ↓ 2,603.0 2,603 1

Nested Loop Left Join (cost=11.99..253.13 rows=1 width=322) (actual time=0.260..249.772 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. 8.181 239.648 ↓ 2,810.0 2,810 1

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

13. 9.672 223.037 ↓ 2,810.0 2,810 1

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

14. 8.964 213.365 ↓ 2,810.0 2,810 1

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

15. 13.811 185.049 ↓ 219.9 4,838 1

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

16. 10.110 147.068 ↓ 230.2 4,834 1

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

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

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

18. 8.459 100.943 ↓ 230.2 4,834 1

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

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

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

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

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

21. 16.576 18.727 ↓ 263.7 6,328 1

Hash Join (cost=9.73..206.78 rows=24 width=77) (actual time=0.075..18.727 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.012..2.105 rows=6,944 loops=1)

23. 0.006 0.046 ↑ 1.0 9 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
24. 0.040 0.040 ↑ 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.040 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.827 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.007 0.007 ↑ 1.0 2 1

Seq Scan on supplier su (cost=0.00..1.02 rows=2 width=36) (actual time=0.007..0.007 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.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.011..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.005..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. 10.412 10.412 ↑ 1.0 1 2,603

Index Scan using deliverypointcomponent_pk on deliverypointcomponent (cost=0.28..0.30 rows=1 width=4) (actual time=0.004..0.004 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,928.290 ↑ 1.0 1 2,603

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

42. 3,027.289 8,910.069 ↑ 1.0 1 2,603

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

  • Sort Key: generalconfig.todate DESC
  • Sort Method: quicksort Memory: 25kB
43. 5,065.438 5,882.780 ↓ 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.335..2.260 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. 20.653 817.342 ↓ 0.0 0 2,603

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

45. 403.465 403.465 ↓ 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.155..0.155 rows=2,203 loops=2,603)

  • Index Cond: (deliverypointcomponentid = deliverypointcomponent.deliverypointcomponentid)
46. 393.224 393.224 ↓ 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.152..0.152 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. 20.688 20.688 ↓ 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.008..0.008 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. 848.208 91,262.526 ↑ 1.0 1 2,586

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

51. 4,458.334 90,414.318 ↓ 1,875.0 1,875 2,586

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

52. 3,811.834 75,862.896 ↓ 1,951.0 1,951 2,586

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

53. 1,046.142 61,957.974 ↓ 1,951.0 1,951 2,586

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

54. 5,674.828 45,772.200 ↓ 1,951.0 1,951 2,586

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

55. 6,282.528 28,833.900 ↓ 2,178.0 2,178 2,586

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

56. 7,664.904 7,664.904 ↓ 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.964 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. 15,139.632 15,139.632 ↑ 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.003..0.003 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 : 18.613 ms
Execution time : 100,670.366 ms