explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 9AYE

Settings
# exclusive inclusive rows x rows loops node
1. 0.084 30,294.137 ↓ 4.6 1,001 1

Limit (cost=1,145,417.47..1,145,418.01 rows=217 width=232) (actual time=30,293.996..30,294.137 rows=1,001 loops=1)

2. 7.352 30,294.053 ↓ 4.6 1,001 1

Sort (cost=1,145,417.47..1,145,418.01 rows=217 width=232) (actual time=30,293.995..30,294.053 rows=1,001 loops=1)

  • Sort Key: deliverypoint.gsrn
  • Sort Method: quicksort Memory: 203kB
3. 4.822 30,286.701 ↓ 4.8 1,034 1

HashAggregate (cost=1,145,404.70..1,145,406.87 rows=217 width=82) (actual time=30,286.365..30,286.701 rows=1,034 loops=1)

  • Group Key: deliverypoint.gsrn, co.contractid, co.customerid, co.supplierref, cdp.contractdeliveryid, m.smartmeterstatus, m.metertype, (CASE WHEN (m.metertype IS NULL) THEN NULL::text WHEN (((m.metertype)::text = 'SMART_METER'::text) AND ((m.smartmeterstatus)::text = 'ACTIVE_READABLE'::text)) THEN 'SMART_METER'::text ELSE 'METERING_COMPANY'::text END), (to_date(((('2019-'::text || (date_part('month'::text, (co_1.supplierindexcollectiondate)::timestamp without time zone))::text) || '-'::text) || (date_part('day'::text, (co_1.supplierindexcollectiondate)::timestamp without time zone))::text), 'YYYY-MM-DD'::text)), (((to_date(((('2019-'::text || (date_part('month'::text, (co_1.supplierindexcollectiondate)::timestamp without time zone))::text) || '-'::text) || (date_part('day'::text, (co_1.supplierindexcollectiondate)::timestamp without time zone))::text), 'YYYY-MM-DD'::text)) + 6)), (((to_date(((('2019-'::text || (date_part('month'::text, (co_1.supplierindexcollectiondate)::timestamp without time zone))::text) || '-'::text) || (date_part('day'::text, (co_1.supplierindexcollectiondate)::timestamp without time zone))::text), 'YYYY-MM-DD'::text)) + 14)), ((SubPlan 8)), ((SubPlan 9)), ((SubPlan 10)), ((SubPlan 11))
4. 0.481 30,281.879 ↓ 4.8 1,034 1

Append (cost=53,440.45..1,145,396.57 rows=217 width=82) (actual time=1,653.120..30,281.879 rows=1,034 loops=1)

5. 0.349 6,542.421 ↓ 25.0 1,001 1

Limit (cost=53,440.45..75,180.86 rows=40 width=82) (actual time=1,653.119..6,542.421 rows=1,001 loops=1)

6.          

Initplan (for Limit)

7. 0.099 0.099 ↑ 1.0 1 1

Index Scan using parametervalue_valuecode_idx on parametervalue parametervalue_1 (cost=0.28..8.29 rows=1 width=4) (actual time=0.098..0.099 rows=1 loops=1)

  • Index Cond: ((valuecode)::text = 'NL.excludedFromConventionalMeteringRequest'::text)
  • Filter: ((parametercategorycode)::text = 'contractUserItemCode'::text)
8. 10.525 6,541.973 ↓ 25.0 1,001 1

Nested Loop Anti Join (cost=53,432.16..75,172.57 rows=40 width=82) (actual time=1,653.119..6,541.973 rows=1,001 loops=1)

  • Join Filter: (cw.effectivedate = (to_date(((('2019-'::text || (date_part('month'::text, (co_1.supplierindexcollectiondate)::timestamp without time zone))::text) || '-'::text) || (date_part('day'::text, (co_1.supplierindexcollectiondate)::timestamp without time zone))::text), 'YYYY-MM-DD'::text)))
  • Rows Removed by Join Filter: 1,343
9. 46.610 4,988.189 ↓ 25.6 1,024 1

Hash Join (cost=53,429.92..69,624.64 rows=40 width=82) (actual time=1,637.861..4,988.189 rows=1,024 loops=1)

  • Hash Cond: ((SubPlan 12) = m.meterid)
10. 17.476 3,995.846 ↓ 82.0 10,413 1

Nested Loop Anti Join (cost=28,796.95..39,116.20 rows=127 width=58) (actual time=972.969..3,995.846 rows=10,413 loops=1)

11. 20.718 3,103.678 ↓ 45.3 10,413 1

Nested Loop Left Join (cost=28,796.52..38,792.60 rows=230 width=58) (actual time=972.523..3,103.678 rows=10,413 loops=1)

  • Join Filter: ((physicalconfig.fromdate <= (to_date(((('2019-'::text || (date_part('month'::text, (co_1.supplierindexcollectiondate)::timestamp without time zone))::text) || '-'::text) || (date_part('day'::text, (co_1.supplierindexcollectiondate)::timestamp without time zone))::text), 'YYYY-MM-DD'::text))) AND ((physicalconfig.todate IS NULL) OR (physicalconfig.todate > (to_date(((('2019-'::text || (date_part('month'::text, (co_1.supplierindexcollectiondate)::timestamp without time zone))::text) || '-'::text) || (date_part('day'::text, (co_1.supplierindexcollectiondate)::timestamp without time zone))::text), 'YYYY-MM-DD'::text)))))
  • Rows Removed by Join Filter: 10,887
12. 16.442 2,853.874 ↓ 45.3 10,413 1

Nested Loop (cost=28,796.10..38,644.16 rows=230 width=54) (actual time=972.131..2,853.874 rows=10,413 loops=1)

  • Join Filter: (co_1.contractid = co.contractid)
13. 43.694 2,660.411 ↓ 44.3 10,413 1

Nested Loop Left Join (cost=28,795.68..38,514.49 rows=235 width=43) (actual time=972.010..2,660.411 rows=10,413 loops=1)

  • Join Filter: ((generalconfig.fromdate <= (to_date(((('2019-'::text || (date_part('month'::text, (co_1.supplierindexcollectiondate)::timestamp without time zone))::text) || '-'::text) || (date_part('day'::text, (co_1.supplierindexcollectiondate)::timestamp without time zone))::text), 'YYYY-MM-DD'::text))) AND ((generalconfig.todate IS NULL) OR (generalconfig.todate > (to_date(((('2019-'::text || (date_part('month'::text, (co_1.supplierindexcollectiondate)::timestamp without time zone))::text) || '-'::text) || (date_part('day'::text, (co_1.supplierindexcollectiondate)::timestamp without time zone))::text), 'YYYY-MM-DD'::text)))) AND (generalconfig.deliverypointid = deliverypoint.deliverypointid))
  • Rows Removed by Join Filter: 15,376
  • Filter: (((generalconfig.category IS NULL) OR ((generalconfig.category)::text = 'SMALL_CAPACITY'::text)) AND ((generalconfig.readingfrequency IS NULL) OR ((generalconfig.readingfrequency)::text <> 'NOT_MEASURED'::text)) AND ((generalconfig.state IS NULL) OR ((generalconfig.state)::text = 'IN_USE'::text)))
  • Rows Removed by Filter: 1,849
14. 7.823 1,966.831 ↓ 38.3 12,262 1

Nested Loop (cost=28,795.26..38,162.05 rows=320 width=43) (actual time=971.733..1,966.831 rows=12,262 loops=1)

15. 17.402 1,787.340 ↓ 38.3 12,262 1

Nested Loop (cost=28,794.84..37,993.98 rows=320 width=43) (actual time=971.507..1,787.340 rows=12,262 loops=1)

16. 11.772 1,573.746 ↓ 38.3 12,262 1

Nested Loop (cost=28,794.42..37,819.07 rows=320 width=24) (actual time=971.348..1,573.746 rows=12,262 loops=1)

17. 11.320 1,365.782 ↓ 38.3 12,262 1

Nested Loop (cost=28,794.00..37,641.10 rows=320 width=20) (actual time=970.959..1,365.782 rows=12,262 loops=1)

18. 31.210 987.946 ↓ 20.0 30,543 1

HashAggregate (cost=28,793.58..28,808.82 rows=1,524 width=8) (actual time=969.890..987.946 rows=30,543 loops=1)

  • Group Key: co_1.contractid, (to_date(((('2019-'::text || (date_part('month'::text, (co_1.supplierindexcollectiondate)::timestamp without time zone))::text) || '-'::text) || (date_part('day'::text, (co_1.supplierindexcollectiondate)::timestamp without time zone))::text), 'YYYY-MM-DD'::text))
19. 3.429 956.736 ↓ 20.0 30,543 1

Append (cost=0.00..28,785.96 rows=1,524 width=8) (actual time=1.243..956.736 rows=30,543 loops=1)

20. 362.921 362.921 ↓ 1.0 782 1

Seq Scan on contract co_1 (cost=0.00..14,385.36 rows=762 width=8) (actual time=1.243..362.921 rows=782 loops=1)

  • Filter: ((supplierindexcollectiondate IS NOT NULL) AND (to_date(((('2019-'::text || (date_part('month'::text, (supplierindexcollectiondate)::timestamp without time zone))::text) || '-'::text) || (date_part('day'::text, (supplierindexcollectiondate)::timestamp without time zone))::text), 'YYYY-MM-DD'::text) >= '2019-12-24'::date) AND (to_date(((('2019-'::text || (date_part('month'::text, (supplierindexcollectiondate)::timestamp without time zone))::text) || '-'::text) || (date_part('day'::text, (supplierindexcollectiondate)::timestamp without time zone))::text), 'YYYY-MM-DD'::text) < '2020-01-13'::date))
  • Rows Removed by Filter: 152,278
21. 590.386 590.386 ↓ 39.1 29,761 1

Seq Scan on contract co_2 (cost=0.00..14,385.36 rows=762 width=8) (actual time=0.054..590.386 rows=29,761 loops=1)

  • Filter: ((supplierindexcollectiondate IS NOT NULL) AND (to_date(((('2020-'::text || (date_part('month'::text, (supplierindexcollectiondate)::timestamp without time zone))::text) || '-'::text) || (date_part('day'::text, (supplierindexcollectiondate)::timestamp without time zone))::text), 'YYYY-MM-DD'::text) >= '2019-12-24'::date) AND (to_date(((('2020-'::text || (date_part('month'::text, (supplierindexcollectiondate)::timestamp without time zone))::text) || '-'::text) || (date_part('day'::text, (supplierindexcollectiondate)::timestamp without time zone))::text), 'YYYY-MM-DD'::text) < '2020-01-13'::date))
  • Rows Removed by Filter: 123,299
22. 366.516 366.516 ↓ 0.0 0 30,543

Index Scan using contractdelively_contract_id_idx on contractdelivery cdp (cost=0.42..5.78 rows=1 width=20) (actual time=0.011..0.012 rows=0 loops=30,543)

  • Index Cond: (contractid = co_1.contractid)
  • Filter: ((fromdate <= (to_date(((('2019-'::text || (date_part('month'::text, (co_1.supplierindexcollectiondate)::timestamp without time zone))::text) || '-'::text) || (date_part('day'::text, (co_1.supplierindexcollectiondate)::timestamp without time zone))::text), 'YYYY-MM-DD'::text))) AND ((todate IS NULL) OR (todate > (to_date(((('2019-'::text || (date_part('month'::text, (co_1.supplierindexcollectiondate)::timestamp without time zone))::text) || '-'::text) || (date_part('day'::text, (co_1.supplierindexcollectiondate)::timestamp without time zone))::text), 'YYYY-MM-DD'::text)))) AND (fromdate <> (to_date(((('2019-'::text || (date_part('month'::text, (co_1.supplierindexcollectiondate)::timestamp without time zone))::text) || '-'::text) || (date_part('day'::text, (co_1.supplierindexcollectiondate)::timestamp without time zone))::text), 'YYYY-MM-DD'::text))) AND ((todate IS NULL) OR (todate <> (to_date(((('2019-'::text || (date_part('month'::text, (co_1.supplierindexcollectiondate)::timestamp without time zone))::text) || '-'::text) || (date_part('day'::text, (co_1.supplierindexcollectiondate)::timestamp without time zone))::text), 'YYYY-MM-DD'::text)))))
  • Rows Removed by Filter: 1
23. 196.192 196.192 ↑ 1.0 1 12,262

Index Scan using deliveryperiod_pkey on deliveryperiod deliveryperiod_1 (cost=0.42..0.55 rows=1 width=12) (actual time=0.015..0.016 rows=1 loops=12,262)

  • Index Cond: (deliveryperiodid = cdp.deliveryperiodid)
  • Filter: (deleted IS FALSE)
24. 196.192 196.192 ↑ 1.0 1 12,262

Index Scan using deliverypoint_pkey on deliverypoint (cost=0.42..0.54 rows=1 width=23) (actual time=0.016..0.016 rows=1 loops=12,262)

  • Index Cond: (deliverypointid = deliveryperiod_1.deliverypointid)
  • Filter: (deleted IS FALSE)
25. 171.668 171.668 ↑ 1.0 1 12,262

Index Scan using deliverypointcomponent_pk on deliverypointcomponent (cost=0.42..0.52 rows=1 width=4) (actual time=0.013..0.014 rows=1 loops=12,262)

  • Index Cond: (deliverypointcomponentid = deliveryperiod_1.deliverypointcomponentid)
  • Filter: (deleted IS FALSE)
26. 649.886 649.886 ↑ 1.0 2 12,262

Index Scan using generalconfig_deliverypointcomponentid_idx on generalconfig (cost=0.42..1.05 rows=2 width=42) (actual time=0.026..0.053 rows=2 loops=12,262)

  • Index Cond: (deliverypointcomponentid = deliverypointcomponent.deliverypointcomponentid)
  • Filter: ((deleted IS FALSE) AND ((sourceid)::text = 'DGO'::text))
  • Rows Removed by Filter: 2
27. 177.021 177.021 ↑ 1.0 1 10,413

Index Scan using contract_pkey on contract co (cost=0.42..0.54 rows=1 width=19) (actual time=0.016..0.017 rows=1 loops=10,413)

  • Index Cond: (contractid = cdp.contractid)
28. 229.086 229.086 ↓ 2.0 2 10,413

Index Scan using physicalconfigid on physicalconfig (cost=0.42..0.63 rows=1 width=20) (actual time=0.014..0.022 rows=2 loops=10,413)

  • Index Cond: ((deliverypointid = deliverypoint.deliverypointid) AND (deliverypointcomponentid = deliverypointcomponent.deliverypointcomponentid))
  • Filter: ((deleted IS FALSE) AND ((sourceid)::text = 'DGO'::text))
29. 874.692 874.692 ↓ 0.0 0 10,413

Index Scan using idx_contractuserfields_contractid_pk on contractuserfields cuf (cost=0.43..1.40 rows=1 width=4) (actual time=0.084..0.084 rows=0 loops=10,413)

  • Index Cond: (contractid = co.contractid)
  • Filter: (useritemcodeparamid = $72)
  • Rows Removed by Filter: 14
30. 51.256 625.497 ↓ 2.0 147,654 1

Hash (cost=23,205.04..23,205.04 rows=73,834 width=32) (actual time=625.497..625.497 rows=147,654 loops=1)

  • Buckets: 2,048 Batches: 16 (originally 8) Memory Usage: 1,025kB
31. 196.181 574.241 ↓ 2.0 147,654 1

Hash Join (cost=9,552.00..23,205.04 rows=73,834 width=32) (actual time=226.939..574.241 rows=147,654 loops=1)

  • Hash Cond: (physicalconfig_1.physicalconfigid = m.physicalconfigid)
32. 152.454 152.454 ↑ 1.0 259,196 1

Seq Scan on physicalconfig physicalconfig_1 (cost=0.00..8,039.04 rows=259,504 width=4) (actual time=0.007..152.454 rows=259,196 loops=1)

  • Filter: (deleted IS FALSE)
  • Rows Removed by Filter: 1
33. 63.268 225.606 ↓ 2.0 147,654 1

Hash (cost=8,052.08..8,052.08 rows=73,834 width=36) (actual time=225.606..225.606 rows=147,654 loops=1)

  • Buckets: 2,048 Batches: 16 (originally 8) Memory Usage: 1,025kB
34. 162.338 162.338 ↓ 2.0 147,654 1

Seq Scan on meter m (cost=0.00..8,052.08 rows=73,834 width=36) (actual time=0.006..162.338 rows=147,654 loops=1)

  • Filter: (((metertype)::text = 'SMART_METER'::text) AND ((smartmeterstatus)::text = 'ACTIVE_READABLE'::text))
  • Rows Removed by Filter: 111,543
35.          

SubPlan (for Hash Join)

36. 22.874 320.236 ↑ 1.0 1 11,437

Aggregate (cost=16.89..16.90 rows=1 width=4) (actual time=0.028..0.028 rows=1 loops=11,437)

37. 22.883 297.362 ↑ 1.0 1 11,437

Nested Loop (cost=0.84..16.89 rows=1 width=4) (actual time=0.025..0.026 rows=1 loops=11,437)

38. 171.555 171.555 ↑ 1.0 1 11,437

Index Scan using meter_physicalconfigid_idx on meter m_3 (cost=0.42..8.44 rows=1 width=8) (actual time=0.015..0.015 rows=1 loops=11,437)

  • Index Cond: (physicalconfigid = physicalconfig.physicalconfigid)
39. 102.924 102.924 ↑ 1.0 1 11,436

Index Scan using physicalconfig_pk on physicalconfig physicalconfig_5 (cost=0.42..8.44 rows=1 width=4) (actual time=0.009..0.009 rows=1 loops=11,436)

  • Index Cond: (physicalconfigid = physicalconfig.physicalconfigid)
  • Filter: (deleted IS FALSE)
40. 2.348 611.328 ↑ 1.0 1 1,024

Nested Loop (cost=2.24..6.01 rows=1 width=23) (actual time=0.475..0.597 rows=1 loops=1,024)

41. 15.439 600.064 ↓ 2.0 2 1,024

Nested Loop (cost=1.96..5.70 rows=1 width=27) (actual time=0.402..0.586 rows=2 loops=1,024)

42. 9.559 531.456 ↓ 2.1 17 1,024

Nested Loop (cost=1.69..3.24 rows=8 width=31) (actual time=0.174..0.519 rows=17 loops=1,024)

43. 1.932 106.496 ↑ 1.0 1 1,024

Nested Loop (cost=1.26..1.67 rows=1 width=23) (actual time=0.099..0.104 rows=1 loops=1,024)

44. 3.072 83.968 ↑ 1.0 1 1,024

Nested Loop (cost=0.84..1.11 rows=1 width=23) (actual time=0.079..0.082 rows=1 loops=1,024)

45. 58.368 58.368 ↑ 1.0 1 1,024

Index Scan using deliverypoint_gsrn_idx on deliverypoint dp_w (cost=0.42..0.55 rows=1 width=23) (actual time=0.056..0.057 rows=1 loops=1,024)

  • Index Cond: ((gsrn)::text = (deliverypoint.gsrn)::text)
46. 22.528 22.528 ↑ 1.0 1 1,024

Index Scan using "IX_deliveryperiod_deliverypointid_deliveryperiod" on deliveryperiod (cost=0.42..0.55 rows=1 width=8) (actual time=0.020..0.022 rows=1 loops=1,024)

  • Index Cond: (deliverypointid = dp_w.deliverypointid)
47. 20.596 20.596 ↑ 1.0 1 1,084

Index Scan using contractdelivery_deliveryperiodid_idx on contractdelivery cdp_1 (cost=0.42..0.55 rows=1 width=8) (actual time=0.019..0.019 rows=1 loops=1,084)

  • Index Cond: (deliveryperiodid = deliveryperiod.deliveryperiodid)
48. 415.401 415.401 ↓ 1.1 17 1,057

Index Scan using crmworkflow_contractdeliveryid_idx on crmworkflow cw (cost=0.43..1.42 rows=15 width=16) (actual time=0.074..0.393 rows=17 loops=1,057)

  • Index Cond: (contractdeliveryid = cdp_1.contractdeliveryid)
49. 53.169 53.169 ↓ 0.0 0 17,723

Index Scan using parametervalueid on parametervalue pv_t (cost=0.28..0.30 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=17,723)

  • Index Cond: (parametervalueid = cw.crmworkflowtypeparamid)
  • Filter: ((valuecode)::text = 'NL_indexCollectionKV'::text)
  • Rows Removed by Filter: 1
50. 8.916 8.916 ↑ 1.0 1 2,229

Index Scan using parametervalueid on parametervalue pv_s (cost=0.28..0.30 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=2,229)

  • Index Cond: (parametervalueid = cw.crmworkflowstatusparamid)
  • Filter: ((internalparametervaluecode)::text = ANY ('{RUNNING,SUCCESS}'::text[]))
  • Rows Removed by Filter: 0
51.          

SubPlan (for Nested Loop Anti Join)

52. 2.002 74.074 ↑ 1.0 1 1,001

Aggregate (cost=19.72..19.73 rows=1 width=0) (actual time=0.074..0.074 rows=1 loops=1,001)

53. 1.001 72.072 ↓ 0.0 0 1,001

Nested Loop (cost=2.24..19.72 rows=1 width=0) (actual time=0.072..0.072 rows=0 loops=1,001)

54. 0.648 71.071 ↓ 0.0 0 1,001

Nested Loop (cost=1.96..19.32 rows=1 width=4) (actual time=0.071..0.071 rows=0 loops=1,001)

55. 2.277 68.068 ↑ 1.0 1 1,001

Nested Loop (cost=1.69..18.91 rows=1 width=8) (actual time=0.051..0.068 rows=1 loops=1,001)

56. 1.762 43.043 ↑ 1.0 1 1,001

Nested Loop (cost=1.26..17.45 rows=1 width=4) (actual time=0.042..0.043 rows=1 loops=1,001)

57. 2.002 37.037 ↑ 1.0 1 1,001

Nested Loop (cost=0.84..16.89 rows=1 width=4) (actual time=0.036..0.037 rows=1 loops=1,001)

58. 30.030 30.030 ↑ 1.0 1 1,001

Index Scan using deliverypoint_gsrn_idx on deliverypoint dp_w_6 (cost=0.42..8.44 rows=1 width=4) (actual time=0.029..0.030 rows=1 loops=1,001)

  • Index Cond: ((gsrn)::text = (deliverypoint.gsrn)::text)
59. 5.005 5.005 ↑ 1.0 1 1,001

Index Scan using "IX_deliveryperiod_deliverypointid_deliveryperiod" on deliveryperiod deliveryperiod_8 (cost=0.42..8.44 rows=1 width=8) (actual time=0.004..0.005 rows=1 loops=1,001)

  • Index Cond: (deliverypointid = dp_w_6.deliverypointid)
60. 4.244 4.244 ↑ 1.0 1 1,061

Index Scan using contractdelivery_deliveryperiodid_idx on contractdelivery cdp_8 (cost=0.42..0.55 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=1,061)

  • Index Cond: (deliveryperiodid = deliveryperiod_8.deliveryperiodid)
61. 22.748 22.748 ↑ 1.0 1 1,034

Index Scan using crmworkflow_contractdeliveryid_idx on crmworkflow cw_6 (cost=0.43..1.46 rows=1 width=12) (actual time=0.007..0.022 rows=1 loops=1,034)

  • Index Cond: (contractdeliveryid = cdp_8.contractdeliveryid)
  • Filter: (effectivedate = (to_date(((('2019-'::text || (date_part('month'::text, (co_1.supplierindexcollectiondate)::timestamp without time zone))::text) || '-'::text) || (date_part('day'::text, (co_1.supplierindexcollectiondate)::timestamp without time zone))::text), 'YYYY-MM-DD'::text)))
  • Rows Removed by Filter: 16
62. 2.355 2.355 ↓ 0.0 0 785

Index Scan using parametervalueid on parametervalue pv_t_6 (cost=0.28..0.39 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=785)

  • Index Cond: (parametervalueid = cw_6.crmworkflowtypeparamid)
  • Filter: ((valuecode)::text = 'NL_indexCollectionKV'::text)
  • Rows Removed by Filter: 1
63. 0.000 0.000 ↓ 0.0 0

Index Scan using parametervalueid on parametervalue pv_s_6 (cost=0.28..0.39 rows=1 width=4) (never executed)

  • Index Cond: (parametervalueid = cw_6.crmworkflowstatusparamid)
  • Filter: ((internalparametervaluecode)::text = 'ERROR'::text)
64. 1.001 68.068 ↓ 0.0 0 1,001

Limit (cost=2.24..19.70 rows=1 width=4) (actual time=0.068..0.068 rows=0 loops=1,001)

65. 1.001 67.067 ↓ 0.0 0 1,001

Nested Loop (cost=2.24..19.70 rows=1 width=4) (actual time=0.067..0.067 rows=0 loops=1,001)

66. 0.768 66.066 ↓ 0.0 0 1,001

Nested Loop (cost=1.96..19.36 rows=1 width=8) (actual time=0.066..0.066 rows=0 loops=1,001)

67. 1.309 62.062 ↑ 1.0 1 1,001

Nested Loop (cost=1.69..19.03 rows=1 width=12) (actual time=0.046..0.062 rows=1 loops=1,001)

68. 1.762 39.039 ↑ 1.0 1 1,001

Nested Loop (cost=1.26..17.45 rows=1 width=4) (actual time=0.037..0.039 rows=1 loops=1,001)

69. 2.002 33.033 ↑ 1.0 1 1,001

Nested Loop (cost=0.84..16.89 rows=1 width=4) (actual time=0.032..0.033 rows=1 loops=1,001)

70. 27.027 27.027 ↑ 1.0 1 1,001

Index Scan using deliverypoint_gsrn_idx on deliverypoint dp_w_7 (cost=0.42..8.44 rows=1 width=4) (actual time=0.027..0.027 rows=1 loops=1,001)

  • Index Cond: ((gsrn)::text = (deliverypoint.gsrn)::text)
71. 4.004 4.004 ↑ 1.0 1 1,001

Index Scan using "IX_deliveryperiod_deliverypointid_deliveryperiod" on deliveryperiod deliveryperiod_9 (cost=0.42..8.44 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=1,001)

  • Index Cond: (deliverypointid = dp_w_7.deliverypointid)
72. 4.244 4.244 ↑ 1.0 1 1,061

Index Scan using contractdelivery_deliveryperiodid_idx on contractdelivery cdp_9 (cost=0.42..0.55 rows=1 width=8) (actual time=0.003..0.004 rows=1 loops=1,061)

  • Index Cond: (deliveryperiodid = deliveryperiod_9.deliveryperiodid)
73. 21.714 21.714 ↑ 1.0 1 1,034

Index Scan using crmworkflow_contractdeliveryid_idx on crmworkflow cw_7 (cost=0.43..1.57 rows=1 width=16) (actual time=0.007..0.021 rows=1 loops=1,034)

  • Index Cond: (contractdeliveryid = cdp_9.contractdeliveryid)
  • Filter: ((effectivedate >= ((to_date(((('2019-'::text || (date_part('month'::text, (co_1.supplierindexcollectiondate)::timestamp without time zone))::text) || '-'::text) || (date_part('day'::text, (co_1.supplierindexcollectiondate)::timestamp without time zone))::text), 'YYYY-MM-DD'::text)) - 5)) AND (effectivedate <= ((to_date(((('2019-'::text || (date_part('month'::text, (co_1.supplierindexcollectiondate)::timestamp without time zone))::text) || '-'::text) || (date_part('day'::text, (co_1.supplierindexcollectiondate)::timestamp without time zone))::text), 'YYYY-MM-DD'::text)) + 5)))
  • Rows Removed by Filter: 16
74. 3.236 3.236 ↓ 0.0 0 809

Index Scan using parametervalueid on parametervalue pv_t_7 (cost=0.28..0.32 rows=1 width=4) (actual time=0.004..0.004 rows=0 loops=809)

  • Index Cond: (parametervalueid = cw_7.crmworkflowtypeparamid)
  • Filter: ((valuecode)::text = ANY ('{NL_indexCollectionKV,NL_VKOindex}'::text[]))
  • Rows Removed by Filter: 1
75. 0.000 0.000 ↓ 0.0 0

Index Scan using parametervalueid on parametervalue pv_s_7 (cost=0.28..0.32 rows=1 width=4) (never executed)

  • Index Cond: (parametervalueid = cw_7.crmworkflowstatusparamid)
  • Filter: ((internalparametervaluecode)::text = 'RUNNING'::text)
76. 0.000 709.709 ↓ 0.0 0 1,001

Limit (cost=27.36..66.48 rows=1 width=4) (actual time=0.709..0.709 rows=0 loops=1,001)

77. 1.652 709.709 ↓ 0.0 0 1,001

Nested Loop (cost=27.36..66.48 rows=1 width=4) (actual time=0.709..0.709 rows=0 loops=1,001)

78. 10.239 685.685 ↑ 1.0 1 1,001

Nested Loop Left Join (cost=26.93..30.99 rows=1 width=4) (actual time=0.684..0.685 rows=1 loops=1,001)

  • Filter: ((((index_1.sourceid)::text = 'USER'::text) AND ((i2_1.marketstatus)::text = ANY ('{SUBMITTED,ACCEPTED}'::text[]))) OR ((index_1.sourceid)::text <> 'USER'::text))
79. 492.492 662.662 ↑ 1.0 1 1,001

Bitmap Heap Scan on index index_1 (cost=10.01..14.04 rows=1 width=11) (actual time=0.662..0.662 rows=1 loops=1,001)

  • Recheck Cond: ((deliverypointcomponentid = deliverypointcomponent.deliverypointcomponentid) AND (deliverypointid = deliverypoint.deliverypointid))
  • Filter: ((deleted IS FALSE) AND ((status)::text = 'ACTUAL'::text) AND (todate >= ((to_date(((('2019-'::text || (date_part('month'::text, (co_1.supplierindexcollectiondate)::timestamp without time zone))::text) || '-'::text) || (date_part('day'::text, (co_1.supplierindexcollectiondate)::timestamp without time zone))::text), 'YYYY-MM-DD'::text)) - 5)) AND (todate <= ((to_date(((('2019-'::text || (date_part('month'::text, (co_1.supplierindexcollectiondate)::timestamp without time zone))::text) || '-'::text) || (date_part('day'::text, (co_1.supplierindexcollectiondate)::timestamp without time zone))::text), 'YYYY-MM-DD'::text)) + 5)))
  • Rows Removed by Filter: 21
  • Heap Blocks: exact=20,095
80. 5.005 170.170 ↓ 0.0 0 1,001

BitmapAnd (cost=10.01..10.01 rows=1 width=0) (actual time=0.170..0.170 rows=0 loops=1,001)

81. 86.086 86.086 ↑ 2.7 22 1,001

Bitmap Index Scan on index_dpcomp_idx (cost=0.00..4.88 rows=60 width=0) (actual time=0.086..0.086 rows=22 loops=1,001)

  • Index Cond: (deliverypointcomponentid = deliverypointcomponent.deliverypointcomponentid)
82. 79.079 79.079 ↑ 2.7 22 1,001

Bitmap Index Scan on index_deliverypointid_idx (cost=0.00..4.88 rows=60 width=0) (actual time=0.079..0.079 rows=22 loops=1,001)

  • Index Cond: (deliverypointid = deliverypoint.deliverypointid)
83. 1.598 12.784 ↓ 0.0 0 799

Limit (cost=16.92..16.92 rows=1 width=156) (actual time=0.016..0.016 rows=0 loops=799)

84. 4.794 11.186 ↓ 0.0 0 799

Sort (cost=16.92..16.92 rows=1 width=156) (actual time=0.014..0.014 rows=0 loops=799)

  • Sort Key: (CASE WHEN ((i2_1.marketstatus)::text = 'ACCEPTED'::text) THEN 1 WHEN ((i2_1.marketstatus)::text = 'SUMBITTED'::text) THEN 2 WHEN ((i2_1.marketstatus)::text = 'REJECTED'::text) THEN 3 ELSE NULL::integer END)
  • Sort Method: quicksort Memory: 25kB
85. 0.799 6.392 ↓ 0.0 0 799

Nested Loop (cost=0.85..16.91 rows=1 width=156) (actual time=0.008..0.008 rows=0 loops=799)

86. 5.593 5.593 ↓ 0.0 0 799

Index Only Scan using indexrelation_pk on indexrelation ir_1 (cost=0.42..8.44 rows=1 width=4) (actual time=0.007..0.007 rows=0 loops=799)

  • Index Cond: (sourceindexid = index_1.indexid)
  • Heap Fetches: 0
87. 0.000 0.000 ↓ 0.0 0

Index Scan using index_pk on index i2_1 (cost=0.43..8.45 rows=1 width=156) (never executed)

  • Index Cond: (indexid = ir_1.targetindexid)
  • Filter: ((sourceid)::text = 'SUPPLIER'::text)
88. 22.372 22.372 ↓ 0.0 0 799

Index Scan using consumption_indexid_idx on consumption consumption_1 (cost=0.43..35.25 rows=25 width=4) (actual time=0.028..0.028 rows=0 loops=799)

  • Index Cond: (endindexid = index_1.indexid)
  • Filter: ((deleted IS FALSE) AND ((sourceid)::text = 'DGO'::text))
  • Rows Removed by Filter: 1
89. 2.002 80.080 ↓ 0.0 0 1,001

Limit (cost=26.66..26.67 rows=1 width=41) (actual time=0.080..0.080 rows=0 loops=1,001)

90. 3.003 78.078 ↓ 0.0 0 1,001

Sort (cost=26.66..26.67 rows=1 width=41) (actual time=0.078..0.078 rows=0 loops=1,001)

  • Sort Key: cw_8.crmworkflowid
  • Sort Method: quicksort Memory: 25kB
91. 1.001 75.075 ↓ 0.0 0 1,001

Nested Loop (cost=2.92..26.65 rows=1 width=41) (actual time=0.075..0.075 rows=0 loops=1,001)

92. 0.000 74.074 ↓ 0.0 0 1,001

Nested Loop (cost=2.24..19.72 rows=1 width=4) (actual time=0.074..0.074 rows=0 loops=1,001)

93. 1.865 74.074 ↓ 0.0 0 1,001

Nested Loop (cost=1.96..19.32 rows=1 width=8) (actual time=0.074..0.074 rows=0 loops=1,001)

94. 2.277 69.069 ↑ 1.0 1 1,001

Nested Loop (cost=1.69..18.91 rows=1 width=12) (actual time=0.052..0.069 rows=1 loops=1,001)

95. 0.701 44.044 ↑ 1.0 1 1,001

Nested Loop (cost=1.26..17.45 rows=1 width=4) (actual time=0.042..0.044 rows=1 loops=1,001)

96. 3.003 38.038 ↑ 1.0 1 1,001

Nested Loop (cost=0.84..16.89 rows=1 width=4) (actual time=0.037..0.038 rows=1 loops=1,001)

97. 30.030 30.030 ↑ 1.0 1 1,001

Index Scan using deliverypoint_gsrn_idx on deliverypoint dp_w_8 (cost=0.42..8.44 rows=1 width=4) (actual time=0.030..0.030 rows=1 loops=1,001)

  • Index Cond: ((gsrn)::text = (deliverypoint.gsrn)::text)
98. 5.005 5.005 ↑ 1.0 1 1,001

Index Scan using "IX_deliveryperiod_deliverypointid_deliveryperiod" on deliveryperiod deliveryperiod_10 (cost=0.42..8.44 rows=1 width=8) (actual time=0.004..0.005 rows=1 loops=1,001)

  • Index Cond: (deliverypointid = dp_w_8.deliverypointid)
99. 5.305 5.305 ↑ 1.0 1 1,061

Index Scan using contractdelivery_deliveryperiodid_idx on contractdelivery cdp_10 (cost=0.42..0.55 rows=1 width=8) (actual time=0.004..0.005 rows=1 loops=1,061)

  • Index Cond: (deliveryperiodid = deliveryperiod_10.deliveryperiodid)
100. 22.748 22.748 ↑ 1.0 1 1,034

Index Scan using crmworkflow_contractdeliveryid_idx on crmworkflow cw_8 (cost=0.43..1.46 rows=1 width=16) (actual time=0.008..0.022 rows=1 loops=1,034)

  • Index Cond: (contractdeliveryid = cdp_10.contractdeliveryid)
  • Filter: (effectivedate = (to_date(((('2019-'::text || (date_part('month'::text, (co_1.supplierindexcollectiondate)::timestamp without time zone))::text) || '-'::text) || (date_part('day'::text, (co_1.supplierindexcollectiondate)::timestamp without time zone))::text), 'YYYY-MM-DD'::text)))
  • Rows Removed by Filter: 16
101. 3.140 3.140 ↓ 0.0 0 785

Index Scan using parametervalueid on parametervalue pv_t_8 (cost=0.28..0.39 rows=1 width=4) (actual time=0.004..0.004 rows=0 loops=785)

  • Index Cond: (parametervalueid = cw_8.crmworkflowtypeparamid)
  • Filter: ((valuecode)::text = 'NL_indexCollectionKV'::text)
  • Rows Removed by Filter: 1
102. 0.000 0.000 ↓ 0.0 0

Index Scan using parametervalueid on parametervalue pv_s_8 (cost=0.28..0.39 rows=1 width=4) (never executed)

  • Index Cond: (parametervalueid = cw_8.crmworkflowstatusparamid)
  • Filter: ((internalparametervaluecode)::text = 'ERROR'::text)
103. 0.000 0.000 ↓ 0.0 0

Index Only Scan using crmworkflowdata_crmworkflowid_key_value_idx on crmworkflowdata cwd_err_1 (cost=0.68..6.92 rows=1 width=41) (never executed)

  • Index Cond: ((crmworkflowid = cw_8.crmworkflowid) AND (key = 'ERROR_REASON'::text))
  • Heap Fetches: 0
104. 0.000 23,738.977 ↑ 5.4 33 1

Limit (cost=224,687.13..1,070,213.53 rows=177 width=82) (actual time=23,644.305..23,738.977 rows=33 loops=1)

105.          

Initplan (for Limit)

106. 0.053 0.053 ↑ 1.0 1 1

Index Scan using parametervalue_valuecode_idx on parametervalue (cost=0.28..8.29 rows=1 width=4) (actual time=0.052..0.053 rows=1 loops=1)

  • Index Cond: ((valuecode)::text = 'NL.excludedFromConventionalMeteringRequest'::text)
  • Filter: ((parametercategorycode)::text = 'contractUserItemCode'::text)
107. 0.369 23,738.962 ↑ 5.4 33 1

Nested Loop Anti Join (cost=224,678.83..1,070,205.24 rows=177 width=82) (actual time=23,644.305..23,738.962 rows=33 loops=1)

  • Join Filter: (cw_1.effectivedate = (to_date(((('2019-'::text || (date_part('month'::text, (co_4.supplierindexcollectiondate)::timestamp without time zone))::text) || '-'::text) || (date_part('day'::text, (co_4.supplierindexcollectiondate)::timestamp without time zone))::text), 'YYYY-MM-DD'::text)))
  • Rows Removed by Join Filter: 55
108. 36.298 23,680.414 ↑ 5.4 33 1

Hash Join (cost=224,676.59..1,045,668.54 rows=177 width=82) (actual time=23,635.876..23,680.414 rows=33 loops=1)

  • Hash Cond: ((SubPlan 5) = m_1.meterid)
109. 147.147 23,067.565 ↑ 11.1 51 1

Hash Join (cost=200,043.62..996,515.38 rows=568 width=58) (actual time=5,612.237..23,067.565 rows=51 loops=1)

  • Hash Cond: ((SubPlan 7) = cw3.crmworkflowid)
110. 124.725 4,212.729 ↓ 4.4 111,376 1

Hash Anti Join (cost=160,117.26..202,602.25 rows=25,445 width=58) (actual time=2,867.777..4,212.729 rows=111,376 loops=1)

  • Hash Cond: (co_3.contractid = cuf_1.contractid)
111. 188.920 4,087.699 ↓ 2.4 111,376 1

Hash Join (cost=134,502.31..169,044.02 rows=45,953 width=58) (actual time=2,867.457..4,087.699 rows=111,376 loops=1)

  • Hash Cond: (co_4.contractid = co_3.contractid)
112. 575.568 3,772.859 ↓ 2.4 111,376 1

Hash Right Join (cost=127,276.36..159,214.42 rows=46,968 width=47) (actual time=2,737.890..3,772.859 rows=111,376 loops=1)

  • Hash Cond: ((generalconfig_1.deliverypointid = deliverypoint_1.deliverypointid) AND (generalconfig_1.deliverypointcomponentid = deliverypointcomponent_1.deliverypointcomponentid))
  • Join Filter: ((generalconfig_1.fromdate <= (to_date(((('2019-'::text || (date_part('month'::text, (co_4.supplierindexcollectiondate)::timestamp without time zone))::text) || '-'::text) || (date_part('day'::text, (co_4.supplierindexcollectiondate)::timestamp without time zone))::text), 'YYYY-MM-DD'::text))) AND ((generalconfig_1.todate IS NULL) OR (generalconfig_1.todate > (to_date(((('2019-'::text || (date_part('month'::text, (co_4.supplierindexcollectiondate)::timestamp without time zone))::text) || '-'::text) || (date_part('day'::text, (co_4.supplierindexcollectiondate)::timestamp without time zone))::text), 'YYYY-MM-DD'::text)))))
  • Rows Removed by Join Filter: 182,477
  • Filter: (((generalconfig_1.category IS NULL) OR ((generalconfig_1.category)::text = 'SMALL_CAPACITY'::text)) AND ((generalconfig_1.readingfrequency IS NULL) OR ((generalconfig_1.readingfrequency)::text <> 'NOT_MEASURED'::text)) AND ((generalconfig_1.state IS NULL) OR ((generalconfig_1.state)::text = 'IN_USE'::text)))
  • Rows Removed by Filter: 4,745
113. 459.842 459.842 ↓ 1.1 312,143 1

Seq Scan on generalconfig generalconfig_1 (cost=0.00..24,096.80 rows=290,166 width=42) (actual time=0.032..459.842 rows=312,143 loops=1)

  • Filter: ((deleted IS FALSE) AND ((sourceid)::text = 'DGO'::text))
  • Rows Removed by Filter: 190,015
114. 43.986 2,737.449 ↓ 1.8 116,121 1

Hash (cost=125,753.96..125,753.96 rows=63,960 width=47) (actual time=2,737.449..2,737.449 rows=116,121 loops=1)

  • Buckets: 2,048 Batches: 16 (originally 8) Memory Usage: 1,025kB
115. 227.062 2,693.463 ↓ 1.8 116,121 1

Hash Right Join (cost=111,514.93..125,753.96 rows=63,960 width=47) (actual time=2,349.734..2,693.463 rows=116,121 loops=1)

  • Hash Cond: ((physicalconfig_2.deliverypointid = deliverypoint_1.deliverypointid) AND (physicalconfig_2.deliverypointcomponentid = deliverypointcomponent_1.deliverypointcomponentid))
  • Join Filter: ((physicalconfig_2.fromdate <= (to_date(((('2019-'::text || (date_part('month'::text, (co_4.supplierindexcollectiondate)::timestamp without time zone))::text) || '-'::text) || (date_part('day'::text, (co_4.supplierindexcollectiondate)::timestamp without time zone))::text), 'YYYY-MM-DD'::text))) AND ((physicalconfig_2.todate IS NULL) OR (physicalconfig_2.todate > (to_date(((('2019-'::text || (date_part('month'::text, (co_4.supplierindexcollectiondate)::timestamp without time zone))::text) || '-'::text) || (date_part('day'::text, (co_4.supplierindexcollectiondate)::timestamp without time zone))::text), 'YYYY-MM-DD'::text)))))
  • Rows Removed by Join Filter: 130,058
116. 117.560 117.560 ↑ 1.0 259,194 1

Seq Scan on physicalconfig physicalconfig_2 (cost=0.00..8,687.80 rows=259,495 width=20) (actual time=0.034..117.560 rows=259,194 loops=1)

  • Filter: ((deleted IS FALSE) AND ((sourceid)::text = 'DGO'::text))
  • Rows Removed by Filter: 3
117. 42.888 2,348.841 ↓ 1.8 116,121 1

Hash (cost=109,992.53..109,992.53 rows=63,960 width=43) (actual time=2,348.841..2,348.841 rows=116,121 loops=1)

  • Buckets: 2,048 Batches: 16 (originally 8) Memory Usage: 1,025kB
118. 105.480 2,305.953 ↓ 1.8 116,121 1

Hash Join (cost=73,471.00..109,992.53 rows=63,960 width=43) (actual time=1,365.154..2,305.953 rows=116,121 loops=1)

  • Hash Cond: (deliveryperiod_3.deliverypointid = deliverypoint_1.deliverypointid)
119. 93.881 2,077.767 ↓ 1.8 116,121 1

Hash Join (cost=66,407.44..99,817.92 rows=63,960 width=24) (actual time=1,240.465..2,077.767 rows=116,121 loops=1)

  • Hash Cond: (deliveryperiod_3.deliverypointcomponentid = deliverypointcomponent_1.deliverypointcomponentid)
120. 114.886 1,875.665 ↓ 1.8 116,121 1

Hash Join (cost=60,231.29..91,102.52 rows=63,960 width=24) (actual time=1,132.100..1,875.665 rows=116,121 loops=1)

  • Hash Cond: (cdp_2.deliveryperiodid = deliveryperiod_3.deliveryperiodid)
121. 205.647 1,576.953 ↓ 1.8 116,121 1

Merge Join (cost=52,982.15..81,151.12 rows=63,960 width=20) (actual time=947.848..1,576.953 rows=116,121 loops=1)

  • Merge Cond: (co_4.contractid = cdp_2.contractid)
  • Join Filter: ((cdp_2.fromdate <= (to_date(((('2019-'::text || (date_part('month'::text, (co_4.supplierindexcollectiondate)::timestamp without time zone))::text) || '-'::text) || (date_part('day'::text, (co_4.supplierindexcollectiondate)::timestamp without time zone))::text), 'YYYY-MM-DD'::text))) AND ((cdp_2.todate IS NULL) OR (cdp_2.todate > (to_date(((('2019-'::text || (date_part('month'::text, (co_4.supplierindexcollectiondate)::timestamp without time zone))::text) || '-'::text) || (date_part('day'::text, (co_4.supplierindexcollectiondate)::timestamp without time zone))::text), 'YYYY-MM-DD'::text)))) AND (cdp_2.fromdate <> (to_date(((('2019-'::text || (date_part('month'::text, (co_4.supplierindexcollectiondate)::timestamp without time zone))::text) || '-'::text) || (date_part('day'::text, (co_4.supplierindexcollectiondate)::timestamp without time zone))::text), 'YYYY-MM-DD'::text))) AND ((cdp_2.todate IS NULL) OR (cdp_2.todate <> (to_date(((('2019-'::text || (date_part('month'::text, (co_4.supplierindexcollectiondate)::timestamp without time zone))::text) || '-'::text) || (date_part('day'::text, (co_4.supplierindexcollectiondate)::timestamp without time zone))::text), 'YYYY-MM-DD'::text)))))
  • Rows Removed by Join Filter: 192,481
122. 66.779 1,123.322 ↓ 1.0 305,044 1

Unique (cost=52,981.73..55,268.91 rows=304,958 width=8) (actual time=947.742..1,123.322 rows=305,044 loops=1)

123. 366.249 1,056.543 ↓ 1.0 305,044 1

Sort (cost=52,981.73..53,744.12 rows=304,958 width=8) (actual time=947.741..1,056.543 rows=305,044 loops=1)

  • Sort Key: co_4.contractid, (to_date(((('2019-'::text || (date_part('month'::text, (co_4.supplierindexcollectiondate)::timestamp without time zone))::text) || '-'::text) || (date_part('day'::text, (co_4.supplierindexcollectiondate)::timestamp without time zone))::text), 'YYYY-MM-DD'::text))
  • Sort Method: external merge Disk: 5,360kB
124. 38.165 690.294 ↓ 1.0 305,044 1

Append (cost=0.00..21,030.72 rows=304,958 width=8) (actual time=0.051..690.294 rows=305,044 loops=1)

125. 334.430 334.430 ↓ 1.0 152,522 1

Seq Scan on contract co_4 (cost=0.00..8,990.57 rows=152,479 width=8) (actual time=0.051..334.430 rows=152,522 loops=1)

  • Filter: (supplierindexcollectiondate IS NOT NULL)
  • Rows Removed by Filter: 538
126. 317.699 317.699 ↓ 1.0 152,522 1

Seq Scan on contract co_5 (cost=0.00..8,990.57 rows=152,479 width=8) (actual time=0.028..317.699 rows=152,522 loops=1)

  • Filter: (supplierindexcollectiondate IS NOT NULL)
  • Rows Removed by Filter: 538
127. 51.805 247.984 ↓ 2.0 308,803 1

Materialize (cost=0.42..14,659.83 rows=153,030 width=20) (actual time=0.080..247.984 rows=308,803 loops=1)

128. 196.179 196.179 ↓ 1.0 154,503 1

Index Scan using contractdelively_contract_id_idx on contractdelivery cdp_2 (cost=0.42..14,277.26 rows=153,030 width=20) (actual time=0.053..196.179 rows=154,503 loops=1)

129. 63.189 183.826 ↓ 1.0 155,528 1

Hash (cost=4,570.51..4,570.51 rows=154,051 width=12) (actual time=183.826..183.826 rows=155,528 loops=1)

  • Buckets: 4,096 Batches: 8 Memory Usage: 848kB
130. 120.637 120.637 ↓ 1.0 155,528 1

Seq Scan on deliveryperiod deliveryperiod_3 (cost=0.00..4,570.51 rows=154,051 width=12) (actual time=0.022..120.637 rows=155,528 loops=1)

  • Filter: (deleted IS FALSE)
131. 41.152 108.221 ↑ 1.0 147,242 1

Hash (cost=3,700.51..3,700.51 rows=150,851 width=4) (actual time=108.221..108.221 rows=147,242 loops=1)

  • Buckets: 4,096 Batches: 8 Memory Usage: 654kB
132. 67.069 67.069 ↑ 1.0 147,242 1

Seq Scan on deliverypointcomponent deliverypointcomponent_1 (cost=0.00..3,700.51 rows=150,851 width=4) (actual time=0.021..67.069 rows=147,242 loops=1)

  • Filter: (deleted IS FALSE)
133. 46.763 122.706 ↓ 1.0 147,242 1

Hash (cost=4,426.25..4,426.25 rows=143,625 width=23) (actual time=122.706..122.706 rows=147,242 loops=1)

  • Buckets: 2,048 Batches: 8 Memory Usage: 1,015kB
134. 75.943 75.943 ↓ 1.0 147,242 1

Seq Scan on deliverypoint deliverypoint_1 (cost=0.00..4,426.25 rows=143,625 width=23) (actual time=0.015..75.943 rows=147,242 loops=1)

  • Filter: (deleted IS FALSE)
135. 59.516 125.920 ↓ 1.0 153,060 1

Hash (cost=4,416.20..4,416.20 rows=153,020 width=19) (actual time=125.920..125.920 rows=153,060 loops=1)

  • Buckets: 2,048 Batches: 16 Memory Usage: 489kB
136. 66.404 66.404 ↓ 1.0 153,060 1

Seq Scan on contract co_3 (cost=0.00..4,416.20 rows=153,020 width=19) (actual time=0.017..66.404 rows=153,060 loops=1)

137. 0.000 0.305 ↓ 0.0 0 1

Hash (cost=24,494.32..24,494.32 rows=68,291 width=4) (actual time=0.305..0.305 rows=0 loops=1)

  • Buckets: 4,096 Batches: 4 Memory Usage: 0kB
138. 0.002 0.305 ↓ 0.0 0 1

Bitmap Heap Scan on contractuserfields cuf_1 (cost=1,277.68..24,494.32 rows=68,291 width=4) (actual time=0.305..0.305 rows=0 loops=1)

  • Recheck Cond: (useritemcodeparamid = $29)
139. 0.303 0.303 ↓ 0.0 0 1

Bitmap Index Scan on idx_contractuserfields_useritemcodeparamid (cost=0.00..1,260.61 rows=68,291 width=0) (actual time=0.303..0.303 rows=0 loops=1)

  • Index Cond: (useritemcodeparamid = $29)
140. 14.297 656.515 ↓ 1.6 43,937 1

Hash (cost=39,482.56..39,482.56 rows=27,024 width=4) (actual time=656.515..656.515 rows=43,937 loops=1)

  • Buckets: 4,096 Batches: 2 Memory Usage: 773kB
141. 642.218 642.218 ↓ 1.6 43,937 1

Seq Scan on crmworkflow cw3 (cost=0.00..39,482.56 rows=27,024 width=4) (actual time=0.058..642.218 rows=43,937 loops=1)

  • Filter: ((creationdate >= '2019-12-30'::date) AND (creationdate < '2020-01-13'::date))
  • Rows Removed by Filter: 1,136,199
142.          

SubPlan (for Hash Join)

143. 111.427 18,051.174 ↓ 0.0 0 111,427

Limit (cost=19.73..19.73 rows=1 width=12) (actual time=0.162..0.162 rows=0 loops=111,427)

144. 334.281 17,939.747 ↓ 0.0 0 111,427

Sort (cost=19.73..19.73 rows=1 width=12) (actual time=0.161..0.161 rows=0 loops=111,427)

  • Sort Key: cw_5.creationdate
  • Sort Method: top-N heapsort Memory: 25kB
145. 183.838 17,605.466 ↓ 0.0 0 111,427

Nested Loop (cost=2.24..19.72 rows=1 width=12) (actual time=0.142..0.158 rows=0 loops=111,427)

146. 96.370 17,159.758 ↑ 1.0 1 111,427

Nested Loop (cost=1.96..19.32 rows=1 width=16) (actual time=0.124..0.154 rows=1 loops=111,427)

147. 131.394 16,714.050 ↑ 1.0 1 111,427

Nested Loop (cost=1.69..18.91 rows=1 width=20) (actual time=0.115..0.150 rows=1 loops=111,427)

148. 151.250 7,577.036 ↑ 1.0 1 111,427

Nested Loop (cost=1.26..17.45 rows=1 width=4) (actual time=0.065..0.068 rows=1 loops=111,427)

149. 111.427 6,017.058 ↑ 1.0 1 111,427

Nested Loop (cost=0.84..16.89 rows=1 width=4) (actual time=0.053..0.054 rows=1 loops=111,427)

150. 4,345.653 4,345.653 ↑ 1.0 1 111,427

Index Scan using deliverypoint_gsrn_idx on deliverypoint dp_w_5 (cost=0.42..8.44 rows=1 width=4) (actual time=0.038..0.039 rows=1 loops=111,427)

  • Index Cond: ((gsrn)::text = (deliverypoint_1.gsrn)::text)
151. 1,559.978 1,559.978 ↑ 1.0 1 111,427

Index Scan using "IX_deliveryperiod_deliverypointid_deliveryperiod" on deliveryperiod deliveryperiod_7 (cost=0.42..8.44 rows=1 width=8) (actual time=0.013..0.014 rows=1 loops=111,427)

  • Index Cond: (deliverypointid = dp_w_5.deliverypointid)
152. 1,408.728 1,408.728 ↑ 1.0 1 117,394

Index Scan using contractdelivery_deliveryperiodid_idx on contractdelivery cdp_7 (cost=0.42..0.55 rows=1 width=8) (actual time=0.011..0.012 rows=1 loops=117,394)

  • Index Cond: (deliveryperiodid = deliveryperiod_7.deliveryperiodid)
153. 9,005.620 9,005.620 ↑ 1.0 1 118,495

Index Scan using crmworkflow_contractdeliveryid_idx on crmworkflow cw_5 (cost=0.43..1.46 rows=1 width=24) (actual time=0.045..0.076 rows=1 loops=118,495)

  • Index Cond: (contractdeliveryid = cdp_7.contractdeliveryid)
  • Filter: (effectivedate = (to_date(((('2019-'::text || (date_part('month'::text, (co_4.supplierindexcollectiondate)::timestamp without time zone))::text) || '-'::text) || (date_part('day'::text, (co_4.supplierindexcollectiondate)::timestamp without time zone))::text), 'YYYY-MM-DD'::text)))
  • Rows Removed by Filter: 12
154. 349.338 349.338 ↑ 1.0 1 116,446

Index Scan using parametervalueid on parametervalue pv_t_5 (cost=0.28..0.39 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=116,446)

  • Index Cond: (parametervalueid = cw_5.crmworkflowtypeparamid)
  • Filter: ((valuecode)::text = 'NL_indexCollectionKV'::text)
  • Rows Removed by Filter: 0
155. 261.870 261.870 ↓ 0.0 0 87,290

Index Scan using parametervalueid on parametervalue pv_s_5 (cost=0.28..0.39 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=87,290)

  • Index Cond: (parametervalueid = cw_5.crmworkflowstatusparamid)
  • Filter: ((internalparametervaluecode)::text = 'ERROR'::text)
  • Rows Removed by Filter: 1
156. 52.403 570.419 ↓ 2.0 147,654 1

Hash (cost=23,205.04..23,205.04 rows=73,834 width=32) (actual time=570.419..570.419 rows=147,654 loops=1)

  • Buckets: 2,048 Batches: 16 (originally 8) Memory Usage: 1,025kB
157. 207.809 518.016 ↓ 2.0 147,654 1

Hash Join (cost=9,552.00..23,205.04 rows=73,834 width=32) (actual time=167.502..518.016 rows=147,654 loops=1)

  • Hash Cond: (physicalconfig_3.physicalconfigid = m_1.physicalconfigid)
158. 143.509 143.509 ↑ 1.0 259,196 1

Seq Scan on physicalconfig physicalconfig_3 (cost=0.00..8,039.04 rows=259,504 width=4) (actual time=0.016..143.509 rows=259,196 loops=1)

  • Filter: (deleted IS FALSE)
  • Rows Removed by Filter: 1
159. 55.014 166.698 ↓ 2.0 147,654 1

Hash (cost=8,052.08..8,052.08 rows=73,834 width=36) (actual time=166.698..166.698 rows=147,654 loops=1)

  • Buckets: 2,048 Batches: 16 (originally 8) Memory Usage: 1,025kB
160. 111.684 111.684 ↓ 2.0 147,654 1

Seq Scan on meter m_1 (cost=0.00..8,052.08 rows=73,834 width=36) (actual time=0.019..111.684 rows=147,654 loops=1)

  • Filter: (((metertype)::text = 'SMART_METER'::text) AND ((smartmeterstatus)::text = 'ACTIVE_READABLE'::text))
  • Rows Removed by Filter: 111,543
161.          

SubPlan (for Hash Join)

162. 0.336 6.132 ↑ 1.0 1 84

Aggregate (cost=16.89..16.90 rows=1 width=4) (actual time=0.073..0.073 rows=1 loops=84)

163. 0.168 5.796 ↑ 1.0 1 84

Nested Loop (cost=0.84..16.89 rows=1 width=4) (actual time=0.068..0.069 rows=1 loops=84)

164. 2.856 2.856 ↑ 1.0 1 84

Index Scan using meter_physicalconfigid_idx on meter m_2 (cost=0.42..8.44 rows=1 width=8) (actual time=0.033..0.034 rows=1 loops=84)

  • Index Cond: (physicalconfigid = physicalconfig_2.physicalconfigid)
165. 2.772 2.772 ↑ 1.0 1 84

Index Scan using physicalconfig_pk on physicalconfig physicalconfig_4 (cost=0.42..8.44 rows=1 width=4) (actual time=0.033..0.033 rows=1 loops=84)

  • Index Cond: (physicalconfigid = physicalconfig_2.physicalconfigid)
  • Filter: (deleted IS FALSE)
166. 0.060 9.735 ↓ 2.0 2 33

Nested Loop (cost=2.24..6.01 rows=1 width=23) (actual time=0.229..0.295 rows=2 loops=33)

167. 0.749 9.141 ↓ 5.0 5 33

Nested Loop (cost=1.96..5.70 rows=1 width=27) (actual time=0.092..0.277 rows=5 loops=33)

168. 0.297 6.732 ↓ 3.1 25 33

Nested Loop (cost=1.69..3.24 rows=8 width=31) (actual time=0.082..0.204 rows=25 loops=33)

169. 0.066 2.145 ↑ 1.0 1 33

Nested Loop (cost=1.26..1.67 rows=1 width=23) (actual time=0.063..0.065 rows=1 loops=33)

170. 0.066 1.683 ↑ 1.0 1 33

Nested Loop (cost=0.84..1.11 rows=1 width=23) (actual time=0.050..0.051 rows=1 loops=33)

171. 1.188 1.188 ↑ 1.0 1 33

Index Scan using deliverypoint_gsrn_idx on deliverypoint dp_w_1 (cost=0.42..0.55 rows=1 width=23) (actual time=0.035..0.036 rows=1 loops=33)

  • Index Cond: ((gsrn)::text = (deliverypoint_1.gsrn)::text)
172. 0.429 0.429 ↑ 1.0 1 33

Index Scan using "IX_deliveryperiod_deliverypointid_deliveryperiod" on deliveryperiod deliveryperiod_2 (cost=0.42..0.55 rows=1 width=8) (actual time=0.013..0.013 rows=1 loops=33)

  • Index Cond: (deliverypointid = dp_w_1.deliverypointid)
173. 0.396 0.396 ↑ 1.0 1 33

Index Scan using contractdelivery_deliveryperiodid_idx on contractdelivery cdp_3 (cost=0.42..0.55 rows=1 width=8) (actual time=0.012..0.012 rows=1 loops=33)

  • Index Cond: (deliveryperiodid = deliveryperiod_2.deliveryperiodid)
174. 4.290 4.290 ↓ 1.7 25 33

Index Scan using crmworkflow_contractdeliveryid_idx on crmworkflow cw_1 (cost=0.43..1.42 rows=15 width=16) (actual time=0.017..0.130 rows=25 loops=33)

  • Index Cond: (contractdeliveryid = cdp_3.contractdeliveryid)
175. 1.660 1.660 ↓ 0.0 0 830

Index Scan using parametervalueid on parametervalue pv_t_1 (cost=0.28..0.30 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=830)

  • Index Cond: (parametervalueid = cw_1.crmworkflowtypeparamid)
  • Filter: ((valuecode)::text = 'NL_indexCollectionKV'::text)
  • Rows Removed by Filter: 1
176. 0.534 0.534 ↓ 0.0 0 178

Index Scan using parametervalueid on parametervalue pv_s_1 (cost=0.28..0.30 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=178)

  • Index Cond: (parametervalueid = cw_1.crmworkflowstatusparamid)
  • Filter: ((internalparametervaluecode)::text = ANY ('{RUNNING,SUCCESS}'::text[]))
  • Rows Removed by Filter: 1
177.          

SubPlan (for Nested Loop Anti Join)

178. 0.066 3.135 ↑ 1.0 1 33

Aggregate (cost=19.72..19.73 rows=1 width=0) (actual time=0.095..0.095 rows=1 loops=33)

179. 0.075 3.069 ↓ 3.0 3 33

Nested Loop (cost=2.24..19.72 rows=1 width=0) (actual time=0.053..0.093 rows=3 loops=33)

180. 0.102 2.706 ↓ 3.0 3 33

Nested Loop (cost=1.96..19.32 rows=1 width=4) (actual time=0.049..0.082 rows=3 loops=33)

181. 0.033 2.244 ↓ 4.0 4 33

Nested Loop (cost=1.69..18.91 rows=1 width=8) (actual time=0.045..0.068 rows=4 loops=33)

182. 0.033 1.287 ↑ 1.0 1 33

Nested Loop (cost=1.26..17.45 rows=1 width=4) (actual time=0.037..0.039 rows=1 loops=33)

183. 0.099 1.089 ↑ 1.0 1 33

Nested Loop (cost=0.84..16.89 rows=1 width=4) (actual time=0.032..0.033 rows=1 loops=33)

184. 0.858 0.858 ↑ 1.0 1 33

Index Scan using deliverypoint_gsrn_idx on deliverypoint dp_w_2 (cost=0.42..8.44 rows=1 width=4) (actual time=0.026..0.026 rows=1 loops=33)

  • Index Cond: ((gsrn)::text = (deliverypoint_1.gsrn)::text)
185. 0.132 0.132 ↑ 1.0 1 33

Index Scan using "IX_deliveryperiod_deliverypointid_deliveryperiod" on deliveryperiod deliveryperiod_4 (cost=0.42..8.44 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=33)

  • Index Cond: (deliverypointid = dp_w_2.deliverypointid)
186. 0.165 0.165 ↑ 1.0 1 33

Index Scan using contractdelivery_deliveryperiodid_idx on contractdelivery cdp_4 (cost=0.42..0.55 rows=1 width=8) (actual time=0.004..0.005 rows=1 loops=33)

  • Index Cond: (deliveryperiodid = deliveryperiod_4.deliveryperiodid)
187. 0.924 0.924 ↓ 4.0 4 33

Index Scan using crmworkflow_contractdeliveryid_idx on crmworkflow cw_2 (cost=0.43..1.46 rows=1 width=12) (actual time=0.006..0.028 rows=4 loops=33)

  • Index Cond: (contractdeliveryid = cdp_4.contractdeliveryid)
  • Filter: (effectivedate = (to_date(((('2019-'::text || (date_part('month'::text, (co_4.supplierindexcollectiondate)::timestamp without time zone))::text) || '-'::text) || (date_part('day'::text, (co_4.supplierindexcollectiondate)::timestamp without time zone))::text), 'YYYY-MM-DD'::text)))
  • Rows Removed by Filter: 22
188. 0.360 0.360 ↑ 1.0 1 120

Index Scan using parametervalueid on parametervalue pv_t_2 (cost=0.28..0.39 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=120)

  • Index Cond: (parametervalueid = cw_2.crmworkflowtypeparamid)
  • Filter: ((valuecode)::text = 'NL_indexCollectionKV'::text)
  • Rows Removed by Filter: 0
189. 0.288 0.288 ↑ 1.0 1 96

Index Scan using parametervalueid on parametervalue pv_s_2 (cost=0.28..0.39 rows=1 width=4) (actual time=0.002..0.003 rows=1 loops=96)

  • Index Cond: (parametervalueid = cw_2.crmworkflowstatusparamid)
  • Filter: ((internalparametervaluecode)::text = 'ERROR'::text)
190. 0.033 2.739 ↓ 0.0 0 33

Limit (cost=2.24..19.70 rows=1 width=4) (actual time=0.083..0.083 rows=0 loops=33)

191. 0.105 2.706 ↓ 0.0 0 33

Nested Loop (cost=2.24..19.70 rows=1 width=4) (actual time=0.082..0.082 rows=0 loops=33)

192. 0.123 2.409 ↓ 3.0 3 33

Nested Loop (cost=1.96..19.36 rows=1 width=8) (actual time=0.045..0.073 rows=3 loops=33)

193. 0.099 2.046 ↓ 4.0 4 33

Nested Loop (cost=1.69..19.03 rows=1 width=12) (actual time=0.041..0.062 rows=4 loops=33)

194. 0.033 1.089 ↑ 1.0 1 33

Nested Loop (cost=1.26..17.45 rows=1 width=4) (actual time=0.032..0.033 rows=1 loops=33)

195. 0.066 0.924 ↑ 1.0 1 33

Nested Loop (cost=0.84..16.89 rows=1 width=4) (actual time=0.027..0.028 rows=1 loops=33)

196. 0.759 0.759 ↑ 1.0 1 33

Index Scan using deliverypoint_gsrn_idx on deliverypoint dp_w_3 (cost=0.42..8.44 rows=1 width=4) (actual time=0.023..0.023 rows=1 loops=33)

  • Index Cond: ((gsrn)::text = (deliverypoint_1.gsrn)::text)
197. 0.099 0.099 ↑ 1.0 1 33

Index Scan using "IX_deliveryperiod_deliverypointid_deliveryperiod" on deliveryperiod deliveryperiod_5 (cost=0.42..8.44 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=33)

  • Index Cond: (deliverypointid = dp_w_3.deliverypointid)
198. 0.132 0.132 ↑ 1.0 1 33

Index Scan using contractdelivery_deliveryperiodid_idx on contractdelivery cdp_5 (cost=0.42..0.55 rows=1 width=8) (actual time=0.003..0.004 rows=1 loops=33)

  • Index Cond: (deliveryperiodid = deliveryperiod_5.deliveryperiodid)
199. 0.858 0.858 ↓ 4.0 4 33

Index Scan using crmworkflow_contractdeliveryid_idx on crmworkflow cw_3 (cost=0.43..1.57 rows=1 width=16) (actual time=0.007..0.026 rows=4 loops=33)

  • Index Cond: (contractdeliveryid = cdp_5.contractdeliveryid)
  • Filter: ((effectivedate >= ((to_date(((('2019-'::text || (date_part('month'::text, (co_4.supplierindexcollectiondate)::timestamp without time zone))::text) || '-'::text) || (date_part('day'::text, (co_4.supplierindexcollectiondate)::timestamp without time zone))::text), 'YYYY-MM-DD'::text)) - 5)) AND (effectivedate <= ((to_date(((('2019-'::text || (date_part('month'::text, (co_4.supplierindexcollectiondate)::timestamp without time zone))::text) || '-'::text) || (date_part('day'::text, (co_4.supplierindexcollectiondate)::timestamp without time zone))::text), 'YYYY-MM-DD'::text)) + 5)))
  • Rows Removed by Filter: 22
200. 0.240 0.240 ↑ 1.0 1 120

Index Scan using parametervalueid on parametervalue pv_t_3 (cost=0.28..0.32 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=120)

  • Index Cond: (parametervalueid = cw_3.crmworkflowtypeparamid)
  • Filter: ((valuecode)::text = ANY ('{NL_indexCollectionKV,NL_VKOindex}'::text[]))
  • Rows Removed by Filter: 0
201. 0.192 0.192 ↓ 0.0 0 96

Index Scan using parametervalueid on parametervalue pv_s_3 (cost=0.28..0.32 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=96)

  • Index Cond: (parametervalueid = cw_3.crmworkflowstatusparamid)
  • Filter: ((internalparametervaluecode)::text = 'RUNNING'::text)
  • Rows Removed by Filter: 1
202. 0.000 31.086 ↓ 0.0 0 33

Limit (cost=27.36..66.48 rows=1 width=4) (actual time=0.942..0.942 rows=0 loops=33)

203. 0.084 31.086 ↓ 0.0 0 33

Nested Loop (cost=27.36..66.48 rows=1 width=4) (actual time=0.942..0.942 rows=0 loops=33)

204. 0.300 28.974 ↑ 1.0 1 33

Nested Loop Left Join (cost=26.93..30.99 rows=1 width=4) (actual time=0.845..0.878 rows=1 loops=33)

  • Filter: ((((index.sourceid)::text = 'USER'::text) AND ((i2.marketstatus)::text = ANY ('{SUBMITTED,ACCEPTED}'::text[]))) OR ((index.sourceid)::text <> 'USER'::text))
205. 18.843 22.902 ↑ 1.0 1 33

Bitmap Heap Scan on index (cost=10.01..14.04 rows=1 width=11) (actual time=0.668..0.694 rows=1 loops=33)

  • Recheck Cond: ((deliverypointcomponentid = deliverypointcomponent_1.deliverypointcomponentid) AND (deliverypointid = deliverypoint_1.deliverypointid))
  • Filter: ((deleted IS FALSE) AND ((status)::text = 'ACTUAL'::text) AND (todate >= ((to_date(((('2019-'::text || (date_part('month'::text, (co_4.supplierindexcollectiondate)::timestamp without time zone))::text) || '-'::text) || (date_part('day'::text, (co_4.supplierindexcollectiondate)::timestamp without time zone))::text), 'YYYY-MM-DD'::text)) - 5)) AND (todate <= ((to_date(((('2019-'::text || (date_part('month'::text, (co_4.supplierindexcollectiondate)::timestamp without time zone))::text) || '-'::text) || (date_part('day'::text, (co_4.supplierindexcollectiondate)::timestamp without time zone))::text), 'YYYY-MM-DD'::text)) + 5)))
  • Rows Removed by Filter: 24
  • Heap Blocks: exact=776
206. 0.198 4.059 ↓ 0.0 0 33

BitmapAnd (cost=10.01..10.01 rows=1 width=0) (actual time=0.123..0.123 rows=0 loops=33)

207. 2.145 2.145 ↑ 2.3 26 33

Bitmap Index Scan on index_dpcomp_idx (cost=0.00..4.88 rows=60 width=0) (actual time=0.065..0.065 rows=26 loops=33)

  • Index Cond: (deliverypointcomponentid = deliverypointcomponent_1.deliverypointcomponentid)
208. 1.716 1.716 ↑ 2.4 25 33

Bitmap Index Scan on index_deliverypointid_idx (cost=0.00..4.88 rows=60 width=0) (actual time=0.052..0.052 rows=25 loops=33)

  • Index Cond: (deliverypointid = deliverypoint_1.deliverypointid)
209. 0.078 5.772 ↓ 0.0 0 39

Limit (cost=16.92..16.92 rows=1 width=156) (actual time=0.148..0.148 rows=0 loops=39)

210. 0.351 5.694 ↓ 0.0 0 39

Sort (cost=16.92..16.92 rows=1 width=156) (actual time=0.146..0.146 rows=0 loops=39)

  • Sort Key: (CASE WHEN ((i2.marketstatus)::text = 'ACCEPTED'::text) THEN 1 WHEN ((i2.marketstatus)::text = 'SUMBITTED'::text) THEN 2 WHEN ((i2.marketstatus)::text = 'REJECTED'::text) THEN 3 ELSE NULL::integer END)
  • Sort Method: quicksort Memory: 25kB
211. 0.167 5.343 ↑ 1.0 1 39

Nested Loop (cost=0.85..16.91 rows=1 width=156) (actual time=0.095..0.137 rows=1 loops=39)

212. 1.599 1.599 ↑ 1.0 1 39

Index Only Scan using indexrelation_pk on indexrelation ir (cost=0.42..8.44 rows=1 width=4) (actual time=0.032..0.041 rows=1 loops=39)

  • Index Cond: (sourceindexid = index.indexid)
  • Heap Fetches: 49
213. 3.577 3.577 ↑ 1.0 1 49

Index Scan using index_pk on index i2 (cost=0.43..8.45 rows=1 width=156) (actual time=0.072..0.073 rows=1 loops=49)

  • Index Cond: (indexid = ir.targetindexid)
  • Filter: ((sourceid)::text = 'SUPPLIER'::text)
214. 2.028 2.028 ↓ 0.0 0 39

Index Scan using consumption_indexid_idx on consumption (cost=0.43..35.25 rows=25 width=4) (actual time=0.052..0.052 rows=0 loops=39)

  • Index Cond: (endindexid = index.indexid)
  • Filter: ((deleted IS FALSE) AND ((sourceid)::text = 'DGO'::text))
  • Rows Removed by Filter: 0
215. 0.066 11.484 ↑ 1.0 1 33

Limit (cost=26.66..26.67 rows=1 width=41) (actual time=0.348..0.348 rows=1 loops=33)

216. 0.198 11.418 ↑ 1.0 1 33

Sort (cost=26.66..26.67 rows=1 width=41) (actual time=0.346..0.346 rows=1 loops=33)

  • Sort Key: cw_4.crmworkflowid
  • Sort Method: top-N heapsort Memory: 25kB
217. 0.114 11.220 ↓ 3.0 3 33

Nested Loop (cost=2.92..26.65 rows=1 width=41) (actual time=0.139..0.340 rows=3 loops=33)

218. 0.108 3.234 ↓ 3.0 3 33

Nested Loop (cost=2.24..19.72 rows=1 width=4) (actual time=0.053..0.098 rows=3 loops=33)

219. 0.102 2.838 ↓ 3.0 3 33

Nested Loop (cost=1.96..19.32 rows=1 width=8) (actual time=0.048..0.086 rows=3 loops=33)

220. 0.066 2.376 ↓ 4.0 4 33

Nested Loop (cost=1.69..18.91 rows=1 width=12) (actual time=0.044..0.072 rows=4 loops=33)

221. 0.066 1.287 ↑ 1.0 1 33

Nested Loop (cost=1.26..17.45 rows=1 width=4) (actual time=0.037..0.039 rows=1 loops=33)

222. 0.066 1.056 ↑ 1.0 1 33

Nested Loop (cost=0.84..16.89 rows=1 width=4) (actual time=0.031..0.032 rows=1 loops=33)

223. 0.792 0.792 ↑ 1.0 1 33

Index Scan using deliverypoint_gsrn_idx on deliverypoint dp_w_4 (cost=0.42..8.44 rows=1 width=4) (actual time=0.023..0.024 rows=1 loops=33)

  • Index Cond: ((gsrn)::text = (deliverypoint_1.gsrn)::text)
224. 0.198 0.198 ↑ 1.0 1 33

Index Scan using "IX_deliveryperiod_deliverypointid_deliveryperiod" on deliveryperiod deliveryperiod_6 (cost=0.42..8.44 rows=1 width=8) (actual time=0.005..0.006 rows=1 loops=33)

  • Index Cond: (deliverypointid = dp_w_4.deliverypointid)
225. 0.165 0.165 ↑ 1.0 1 33

Index Scan using contractdelivery_deliveryperiodid_idx on contractdelivery cdp_6 (cost=0.42..0.55 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=33)

  • Index Cond: (deliveryperiodid = deliveryperiod_6.deliveryperiodid)
226. 1.023 1.023 ↓ 4.0 4 33

Index Scan using crmworkflow_contractdeliveryid_idx on crmworkflow cw_4 (cost=0.43..1.46 rows=1 width=16) (actual time=0.006..0.031 rows=4 loops=33)

  • Index Cond: (contractdeliveryid = cdp_6.contractdeliveryid)
  • Filter: (effectivedate = (to_date(((('2019-'::text || (date_part('month'::text, (co_4.supplierindexcollectiondate)::timestamp without time zone))::text) || '-'::text) || (date_part('day'::text, (co_4.supplierindexcollectiondate)::timestamp without time zone))::text), 'YYYY-MM-DD'::text)))
  • Rows Removed by Filter: 22
227. 0.360 0.360 ↑ 1.0 1 120

Index Scan using parametervalueid on parametervalue pv_t_4 (cost=0.28..0.39 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=120)

  • Index Cond: (parametervalueid = cw_4.crmworkflowtypeparamid)
  • Filter: ((valuecode)::text = 'NL_indexCollectionKV'::text)
  • Rows Removed by Filter: 0
228. 0.288 0.288 ↑ 1.0 1 96

Index Scan using parametervalueid on parametervalue pv_s_4 (cost=0.28..0.39 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=96)

  • Index Cond: (parametervalueid = cw_4.crmworkflowstatusparamid)
  • Filter: ((internalparametervaluecode)::text = 'ERROR'::text)
229. 7.872 7.872 ↑ 1.0 1 96

Index Only Scan using crmworkflowdata_crmworkflowid_key_value_idx on crmworkflowdata cwd_err (cost=0.68..6.92 rows=1 width=41) (actual time=0.080..0.082 rows=1 loops=96)

  • Index Cond: ((crmworkflowid = cw_4.crmworkflowid) AND (key = 'ERROR_REASON'::text))
  • Heap Fetches: 95
Planning time : 75.380 ms
Execution time : 30,298.776 ms