explain.depesz.com

PostgreSQL's explain analyze made readable

Result: q97h

Settings
# exclusive inclusive rows x rows loops node
1. 0.088 149.746 ↓ 21.0 21 1

Sort (cost=324.52..324.52 rows=1 width=694) (actual time=149.742..149.746 rows=21 loops=1)

  • Sort Key: co.creationdate
  • Sort Method: quicksort Memory: 35kB
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. 0.112 149.641 ↓ 21.0 21 1

Nested Loop Anti Join (cost=17.30..322.21 rows=1 width=694) (actual time=0.513..149.641 rows=21 loops=1)

5. 0.080 138.945 ↓ 21.0 21 1

Nested Loop Left Join (cost=17.02..314.15 rows=1 width=594) (actual time=0.446..138.945 rows=21 loops=1)

6. 0.060 138.781 ↓ 21.0 21 1

Nested Loop (cost=16.74..312.38 rows=1 width=489) (actual time=0.423..138.781 rows=21 loops=1)

7. 0.328 122.737 ↓ 37.0 37 1

Nested Loop Left Join (cost=12.83..308.44 rows=1 width=497) (actual time=0.357..122.737 rows=37 loops=1)

  • Join Filter: ((ac.externalid)::text = (deliverypoint.regulator)::text)
  • Rows Removed by Join Filter: 407
8. 0.092 122.261 ↓ 37.0 37 1

Nested Loop (cost=12.83..307.22 rows=1 width=501) (actual time=0.332..122.261 rows=37 loops=1)

9. 0.088 122.021 ↓ 37.0 37 1

Nested Loop (cost=12.55..306.91 rows=1 width=483) (actual time=0.311..122.021 rows=37 loops=1)

10. 0.092 121.785 ↓ 37.0 37 1

Nested Loop (cost=12.27..306.61 rows=1 width=479) (actual time=0.293..121.785 rows=37 loops=1)

11. 2.123 121.545 ↓ 37.0 37 1

Nested Loop Left Join (cost=11.99..304.79 rows=1 width=330) (actual time=0.275..121.545 rows=37 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: 2,773
12. 3.226 119.422 ↓ 2,810.0 2,810 1

Nested Loop Left Join (cost=11.71..302.45 rows=1 width=343) (actual time=0.252..119.422 rows=2,810 loops=1)

13. 3.210 113.386 ↓ 2,810.0 2,810 1

Nested Loop Left Join (cost=11.57..301.72 rows=1 width=343) (actual time=0.237..113.386 rows=2,810 loops=1)

14. 1.507 110.176 ↓ 2,810.0 2,810 1

Nested Loop (cost=11.29..298.84 rows=1 width=339) (actual time=0.223..110.176 rows=2,810 loops=1)

15. 5.724 94.155 ↓ 219.9 4,838 1

Nested Loop Left Join (cost=11.01..291.85 rows=22 width=322) (actual time=0.204..94.155 rows=4,838 loops=1)

16. 5.157 73.929 ↓ 230.2 4,834 1

Nested Loop Left Join (cost=10.72..285.01 rows=21 width=286) (actual time=0.187..73.929 rows=4,834 loops=1)

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

Nested Loop Left Join (cost=10.58..282.53 rows=21 width=282) (actual time=0.162..68.772 rows=4,834 loops=1)

18. 5.338 50.552 ↓ 230.2 4,834 1

Nested Loop Left Join (cost=10.29..275.76 rows=21 width=264) (actual time=0.144..50.552 rows=4,834 loops=1)

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

Nested Loop Left Join (cost=10.29..274.20 rows=21 width=232) (actual time=0.113..45.214 rows=4,834 loops=1)

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

Nested Loop Left Join (cost=10.01..266.50 rows=24 width=232) (actual time=0.097..32.036 rows=6,328 loops=1)

21. 6.097 7.555 ↓ 263.7 6,328 1

Hash Join (cost=9.73..258.43 rows=24 width=85) (actual time=0.074..7.555 rows=6,328 loops=1)

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

Seq Scan on contract co (cost=0.00..230.44 rows=6,944 width=80) (actual time=0.014..1.413 rows=6,944 loops=1)

23. 0.006 0.045 ↑ 1.0 9 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
24. 0.039 0.039 ↑ 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.027..0.039 rows=9 loops=1)

  • Index Cond: ((internalparametervaluecode)::text = 'inactive'::text)
25. 18.984 18.984 ↑ 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.003..0.003 rows=1 loops=6,328)

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

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

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

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

28. 0.012 0.012 ↑ 1.0 2 1

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

29. 14.502 14.502 ↑ 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.003..0.003 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. 14.502 14.502 ↑ 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.003..0.003 rows=1 loops=4,834)

  • Index Cond: (contractid = co.contractid)
33. 14.514 14.514 ↑ 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.002..0.003 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. 2.810 2.810 ↓ 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.001..0.001 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. 0.148 0.148 ↑ 1.0 1 37

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=37)

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

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=37)

  • Index Cond: (deliverypointcomponentid = deliveryperiod.deliverypointcomponentid)
  • Filter: (deleted IS FALSE)
39. 0.148 0.148 ↑ 1.0 1 37

Index Scan using deliverypoint_pkey on deliverypoint (cost=0.28..0.30 rows=1 width=22) (actual time=0.004..0.004 rows=1 loops=37)

  • Index Cond: (deliverypointid = deliveryperiod.deliverypointid)
  • Filter: (deleted IS FALSE)
40. 0.148 0.148 ↓ 1.2 12 37

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

41. 0.074 15.984 ↑ 1.0 1 37

Limit (cost=3.92..3.92 rows=1 width=1,313) (actual time=0.431..0.432 rows=1 loops=37)

42. 5.476 15.910 ↑ 1.0 1 37

Sort (cost=3.92..3.92 rows=1 width=1,313) (actual time=0.429..0.430 rows=1 loops=37)

  • Sort Key: generalconfig.todate DESC
  • Sort Method: quicksort Memory: 25kB
43. 8.843 10.434 ↓ 266.0 266 37

Bitmap Heap Scan on generalconfig (cost=2.89..3.91 rows=1 width=1,313) (actual time=0.048..0.282 rows=266 loops=37)

  • 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: 0
  • Heap Blocks: exact=371
44. 0.051 1.591 ↓ 0.0 0 37

BitmapAnd (cost=2.89..2.89 rows=1 width=0) (actual time=0.043..0.043 rows=0 loops=37)

45. 0.814 0.814 ↓ 53.2 266 37

Bitmap Index Scan on generalconfig_deliverypointcomponentid_idx (cost=0.00..1.32 rows=5 width=0) (actual time=0.022..0.022 rows=266 loops=37)

  • Index Cond: (deliverypointcomponentid = deliverypointcomponent.deliverypointcomponentid)
46. 0.726 0.726 ↓ 89.4 447 22

Bitmap Index Scan on "IX_generalconfig_deliverypointid_generalconfig" (cost=0.00..1.32 rows=5 width=0) (actual time=0.033..0.033 rows=447 loops=22)

  • Index Cond: (deliverypointid = deliverypoint.deliverypointid)
47. 0.084 0.084 ↑ 1.0 1 21

Index Scan using subscription_pkey on subscription sub (cost=0.28..1.77 rows=1 width=105) (actual time=0.004..0.004 rows=1 loops=21)

  • Index Cond: (contractdeliveryid = cdp.contractdeliveryid)
48. 0.105 0.105 ↓ 0.0 0 21

Index Scan using idx_contractuserfields_contractid_pk on contractuserfields cuf (cost=0.28..0.31 rows=1 width=4) (actual time=0.005..0.005 rows=0 loops=21)

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

SubPlan (for Nested Loop Anti Join)

50. 0.021 10.479 ↑ 1.0 1 21

Aggregate (cost=7.71..7.72 rows=1 width=8) (actual time=0.499..0.499 rows=1 loops=21)

51. 0.021 10.458 ↓ 0.0 0 21

Nested Loop (cost=1.68..7.71 rows=1 width=0) (actual time=0.498..0.498 rows=0 loops=21)

52. 0.021 10.437 ↓ 0.0 0 21

Nested Loop (cost=1.41..7.38 rows=1 width=4) (actual time=0.497..0.497 rows=0 loops=21)

53. 0.000 10.416 ↓ 0.0 0 21

Nested Loop (cost=1.13..7.08 rows=1 width=8) (actual time=0.496..0.496 rows=0 loops=21)

54. 0.021 10.416 ↓ 0.0 0 21

Nested Loop (cost=0.85..6.76 rows=1 width=4) (actual time=0.496..0.496 rows=0 loops=21)

55. 0.021 10.395 ↓ 0.0 0 21

Nested Loop (cost=0.57..6.44 rows=1 width=4) (actual time=0.495..0.495 rows=0 loops=21)

56. 10.374 10.374 ↓ 0.0 0 21

Index Scan using idx_customer_name on customer cu2 (cost=0.29..3.29 rows=1 width=4) (actual time=0.494..0.494 rows=0 loops=21)

  • 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: 839
57. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_contract_customerid_pk on contract co2 (cost=0.28..3.13 rows=2 width=8) (never executed)

  • Index Cond: (customerid = cu2.customerid)
58. 0.000 0.000 ↓ 0.0 0

Index Scan using contractdelively_contract_id_idx on contractdelivery cdp2 (cost=0.28..0.30 rows=1 width=8) (never executed)

  • Index Cond: (contractid = co2.contractid)
59. 0.000 0.000 ↓ 0.0 0

Index Scan using deliveryperiod_pkey on deliveryperiod deliveryperiod_1 (cost=0.28..0.32 rows=1 width=12) (never executed)

  • Index Cond: (deliveryperiodid = cdp2.deliveryperiodid)
  • Filter: (deleted IS FALSE)
60. 0.000 0.000 ↓ 0.0 0

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

  • Index Cond: (deliverypointcomponentid = deliveryperiod_1.deliverypointcomponentid)
  • Filter: (deleted IS FALSE)
61. 0.000 0.000 ↓ 0.0 0

Index Scan using deliverypoint_pkey on deliverypoint deliverypoint_1 (cost=0.28..0.30 rows=1 width=4) (never executed)

  • Index Cond: (deliverypointid = deliveryperiod_1.deliverypointid)
  • Filter: ((deleted IS FALSE) AND ((gsrn)::text = (deliverypoint.gsrn)::text))
Planning time : 18.340 ms
Execution time : 150.471 ms