explain.depesz.com

PostgreSQL's explain analyze made readable

Result: y2ar

Settings
# exclusive inclusive rows x rows loops node
1. 0.074 29,903.699 ↓ 4.6 1,001 1

Limit (cost=1,135,348.11..1,135,348.65 rows=217 width=192) (actual time=29,903.562..29,903.699 rows=1,001 loops=1)

2. 14.287 29,903.625 ↓ 4.6 1,001 1

Sort (cost=1,135,348.11..1,135,348.65 rows=217 width=192) (actual time=29,903.561..29,903.625 rows=1,001 loops=1)

  • Sort Key: deliverypoint.gsrn
  • Sort Method: quicksort Memory: 194kB
3. 5.059 29,889.338 ↓ 4.8 1,034 1

HashAggregate (cost=1,135,335.34..1,135,337.51 rows=217 width=82) (actual time=29,888.756..29,889.338 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 6)), ((SubPlan 7))
4. 0.530 29,884.279 ↓ 4.8 1,034 1

Append (cost=53,440.45..1,135,328.29 rows=217 width=82) (actual time=1,609.544..29,884.279 rows=1,034 loops=1)

5. 0.301 6,428.109 ↓ 25.0 1,001 1

Limit (cost=53,440.45..73,324.96 rows=40 width=82) (actual time=1,609.543..6,428.109 rows=1,001 loops=1)

6.          

Initplan (for Limit)

7. 0.109 0.109 ↑ 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.108..0.109 rows=1 loops=1)

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

Nested Loop Anti Join (cost=53,432.16..73,316.67 rows=40 width=82) (actual time=1,609.542..6,427.699 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: 1343
9. 45.293 5,028.449 ↓ 25.6 1,024 1

Hash Join (cost=53,429.92..69,624.64 rows=40 width=82) (actual time=1,597.876..5,028.449 rows=1,024 loops=1)

  • Hash Cond: ((SubPlan 8) = m.meterid)
10. 9.126 4,035.814 ↓ 82.0 10,413 1

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

11. 25.999 3,110.344 ↓ 45.3 10,413 1

Nested Loop Left Join (cost=28,796.52..38,792.60 rows=230 width=58) (actual time=945.644..3,110.344 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: 10887
12. 11.449 2,844.846 ↓ 45.3 10,413 1

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

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

Nested Loop Left Join (cost=28,795.68..38,514.49 rows=235 width=43) (actual time=945.125..2,677.202 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: 15376
  • 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: 1849
14. 13.311 1,958.432 ↓ 38.3 12,262 1

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

15. 14.197 1,773.453 ↓ 38.3 12,262 1

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

16. 18.336 1,550.802 ↓ 38.3 12,262 1

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

17. 38.169 1,336.274 ↓ 38.3 12,262 1

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

18. 31.354 962.132 ↓ 20.0 30,543 1

HashAggregate (cost=28,793.58..28,808.82 rows=1,524 width=8) (actual time=943.733..962.132 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.823 930.778 ↓ 20.0 30,543 1

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

20. 314.977 314.977 ↓ 1.0 782 1

Seq Scan on contract co_1 (cost=0.00..14,385.36 rows=762 width=8) (actual time=1.250..314.977 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: 152278
21. 611.978 611.978 ↓ 39.1 29,761 1

Seq Scan on contract co_2 (cost=0.00..14,385.36 rows=762 width=8) (actual time=0.052..611.978 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: 123299
22. 335.973 335.973 ↓ 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.011 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.016..0.016 rows=1 loops=12,262)

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

Index Scan using deliverypoint_pkey on deliverypoint (cost=0.42..0.54 rows=1 width=23) (actual time=0.017..0.017 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.014..0.014 rows=1 loops=12,262)

  • Index Cond: (deliverypointcomponentid = deliveryperiod_1.deliverypointcomponentid)
  • Filter: (deleted IS FALSE)
26. 686.672 686.672 ↑ 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.027..0.056 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. 156.195 156.195 ↑ 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.014..0.015 rows=1 loops=10,413)

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

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

  • Index Cond: ((deliverypointid = deliverypoint.deliverypointid) AND (deliverypointcomponentid = deliverypointcomponent.deliverypointcomponentid))
  • Filter: ((deleted IS FALSE) AND ((sourceid)::text = 'DGO'::text))
29. 916.344 916.344 ↓ 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.088..0.088 rows=0 loops=10,413)

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

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

  • Buckets: 2048 Batches: 16 (originally 8) Memory Usage: 1025kB
31. 171.784 569.541 ↓ 2.0 147,654 1

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

  • Hash Cond: (physicalconfig_1.physicalconfigid = m.physicalconfigid)
32. 125.043 125.043 ↑ 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..125.043 rows=259,196 loops=1)

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

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

  • Buckets: 2048 Batches: 16 (originally 8) Memory Usage: 1025kB
34. 196.919 196.919 ↓ 2.0 147,654 1

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

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

SubPlan (for Hash Join)

36. 22.874 331.673 ↑ 1.0 1 11,437

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

37. 11.447 308.799 ↑ 1.0 1 11,437

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

38. 182.992 182.992 ↑ 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.016..0.016 rows=1 loops=11,437)

  • Index Cond: (physicalconfigid = physicalconfig.physicalconfigid)
39. 114.360 114.360 ↑ 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.010 rows=1 loops=11,436)

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

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

41. 20.559 619.520 ↓ 2.0 2 1,024

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

42. 9.427 545.792 ↓ 2.1 17 1,024

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

43. 2.836 116.736 ↑ 1.0 1 1,024

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

44. 3.072 91.136 ↑ 1.0 1 1,024

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

45. 62.464 62.464 ↑ 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.061..0.061 rows=1 loops=1,024)

  • Index Cond: ((gsrn)::text = (deliverypoint.gsrn)::text)
46. 25.600 25.600 ↑ 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.023..0.025 rows=1 loops=1,024)

  • Index Cond: (deliverypointid = dp_w.deliverypointid)
47. 22.764 22.764 ↑ 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.021..0.021 rows=1 loops=1,084)

  • Index Cond: (deliveryperiodid = deliveryperiod.deliveryperiodid)
48. 419.629 419.629 ↓ 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.073..0.397 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. 1.001 79.079 ↓ 0.0 0 1,001

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

53. 0.000 78.078 ↓ 0.0 0 1,001

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

54. 1.769 78.078 ↓ 0.0 0 1,001

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

55. 1.177 73.073 ↑ 1.0 1 1,001

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

56. 1.702 46.046 ↑ 1.0 1 1,001

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

57. 3.003 39.039 ↑ 1.0 1 1,001

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

58. 31.031 31.031 ↑ 1.0 1 1,001

Index Scan using deliverypoint_gsrn_idx on deliverypoint dp_w_4 (cost=0.42..8.44 rows=1 width=4) (actual time=0.030..0.031 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_6 (cost=0.42..8.44 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=1,001)

  • Index Cond: (deliverypointid = dp_w_4.deliverypointid)
60. 5.305 5.305 ↑ 1.0 1 1,061

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

  • Index Cond: (deliveryperiodid = deliveryperiod_6.deliveryperiodid)
61. 25.850 25.850 ↑ 1.0 1 1,034

Index Scan using crmworkflow_contractdeliveryid_idx on crmworkflow cw_4 (cost=0.43..1.57 rows=1 width=16) (actual time=0.008..0.025 rows=1 loops=1,034)

  • Index Cond: (contractdeliveryid = cdp_6.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
62. 3.236 3.236 ↓ 0.0 0 809

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

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

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

  • Index Cond: (parametervalueid = cw_4.crmworkflowstatusparamid)
  • Filter: ((internalparametervaluecode)::text = 'RUNNING'::text)
64. 1.001 680.680 ↓ 0.0 0 1,001

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

65. 0.853 679.679 ↓ 0.0 0 1,001

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

66. 9.440 655.655 ↑ 1.0 1 1,001

Nested Loop Left Join (cost=26.93..30.99 rows=1 width=4) (actual time=0.654..0.655 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))
67. 475.475 632.632 ↑ 1.0 1 1,001

Bitmap Heap Scan on index index_1 (cost=10.01..14.04 rows=1 width=11) (actual time=0.631..0.632 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=20095
68. 6.006 157.157 ↓ 0.0 0 1,001

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

69. 78.078 78.078 ↑ 2.7 22 1,001

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

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

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

  • Index Cond: (deliverypointid = deliverypoint.deliverypointid)
71. 1.598 13.583 ↓ 0.0 0 799

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

72. 5.593 11.985 ↓ 0.0 0 799

Sort (cost=16.92..16.92 rows=1 width=156) (actual time=0.015..0.015 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
73. 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)

74. 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
75. 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)
76. 23.171 23.171 ↓ 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.029..0.029 rows=0 loops=799)

  • Index Cond: (endindexid = index_1.indexid)
  • Filter: ((deleted IS FALSE) AND ((sourceid)::text = 'DGO'::text))
  • Rows Removed by Filter: 1
77. 0.000 23,455.640 ↑ 5.4 33 1

Limit (cost=224,687.13..1,062,001.16 rows=177 width=82) (actual time=23,354.589..23,455.640 rows=33 loops=1)

78.          

Initplan (for Limit)

79. 0.080 0.080 ↑ 1.0 1 1

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

  • Index Cond: ((valuecode)::text = 'NL.excludedFromConventionalMeteringRequest'::text)
  • Filter: ((parametercategorycode)::text = 'contractUserItemCode'::text)
80. 0.273 23,455.630 ↑ 5.4 33 1

Nested Loop Anti Join (cost=224,678.83..1,061,992.86 rows=177 width=82) (actual time=23,354.588..23,455.630 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
81. 48.980 23,408.926 ↑ 5.4 33 1

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

  • Hash Cond: ((SubPlan 3) = m_1.meterid)
82. 154.327 22,800.888 ↑ 11.1 51 1

Hash Join (cost=200,043.62..996,515.38 rows=568 width=58) (actual time=5,140.450..22,800.888 rows=51 loops=1)

  • Hash Cond: ((SubPlan 5) = cw3.crmworkflowid)
83. 121.879 4,061.173 ↓ 4.4 111,389 1

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

  • Hash Cond: (co_3.contractid = cuf_1.contractid)
84. 173.514 3,938.756 ↓ 2.4 111,389 1

Hash Join (cost=134,502.31..169,044.02 rows=45,953 width=58) (actual time=2,910.817..3,938.756 rows=111,389 loops=1)

  • Hash Cond: (co_4.contractid = co_3.contractid)
85. 486.386 3,650.499 ↓ 2.4 111,389 1

Hash Right Join (cost=127,276.36..159,214.42 rows=46,968 width=47) (actual time=2,790.082..3,650.499 rows=111,389 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: 182498
  • 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: 4745
86. 374.511 374.511 ↓ 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.029..374.511 rows=312,143 loops=1)

  • Filter: ((deleted IS FALSE) AND ((sourceid)::text = 'DGO'::text))
  • Rows Removed by Filter: 190015
87. 45.834 2,789.602 ↓ 1.8 116,134 1

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

  • Buckets: 2048 Batches: 16 (originally 8) Memory Usage: 1025kB
88. 251.736 2,743.768 ↓ 1.8 116,134 1

Hash Right Join (cost=111,514.93..125,753.96 rows=63,960 width=47) (actual time=2,347.648..2,743.768 rows=116,134 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: 130078
89. 145.232 145.232 ↑ 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.035..145.232 rows=259,194 loops=1)

  • Filter: ((deleted IS FALSE) AND ((sourceid)::text = 'DGO'::text))
  • Rows Removed by Filter: 3
90. 43.886 2,346.800 ↓ 1.8 116,134 1

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

  • Buckets: 2048 Batches: 16 (originally 8) Memory Usage: 1025kB
91. 111.072 2,302.914 ↓ 1.8 116,134 1

Hash Join (cost=73,471.00..109,992.53 rows=63,960 width=43) (actual time=1,323.921..2,302.914 rows=116,134 loops=1)

  • Hash Cond: (deliveryperiod_3.deliverypointid = deliverypoint_1.deliverypointid)
92. 99.484 2,055.207 ↓ 1.8 116,134 1

Hash Join (cost=66,407.44..99,817.92 rows=63,960 width=24) (actual time=1,185.394..2,055.207 rows=116,134 loops=1)

  • Hash Cond: (deliveryperiod_3.deliverypointcomponentid = deliverypointcomponent_1.deliverypointcomponentid)
93. 118.311 1,862.526 ↓ 1.8 116,134 1

Hash Join (cost=60,231.29..91,102.52 rows=63,960 width=24) (actual time=1,092.075..1,862.526 rows=116,134 loops=1)

  • Hash Cond: (cdp_2.deliveryperiodid = deliveryperiod_3.deliveryperiodid)
94. 223.092 1,640.717 ↓ 1.8 116,134 1

Merge Join (cost=52,982.15..81,151.12 rows=63,960 width=20) (actual time=988.186..1,640.717 rows=116,134 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: 192468
95. 71.780 1,179.205 ↓ 1.0 305,044 1

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

96. 391.707 1,107.425 ↓ 1.0 305,044 1

Sort (cost=52,981.73..53,744.12 rows=304,958 width=8) (actual time=988.082..1,107.425 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: 5360kB
97. 39.029 715.718 ↓ 1.0 305,044 1

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

98. 359.075 359.075 ↓ 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.037..359.075 rows=152,522 loops=1)

  • Filter: (supplierindexcollectiondate IS NOT NULL)
  • Rows Removed by Filter: 538
99. 317.614 317.614 ↓ 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.029..317.614 rows=152,522 loops=1)

  • Filter: (supplierindexcollectiondate IS NOT NULL)
  • Rows Removed by Filter: 538
100. 54.082 238.420 ↓ 2.0 308,803 1

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

101. 184.338 184.338 ↓ 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.051..184.338 rows=154,503 loops=1)

102. 35.005 103.498 ↓ 1.0 155,528 1

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

  • Buckets: 4096 Batches: 8 Memory Usage: 848kB
103. 68.493 68.493 ↓ 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.016..68.493 rows=155,528 loops=1)

  • Filter: (deleted IS FALSE)
104. 35.199 93.197 ↑ 1.0 147,242 1

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

  • Buckets: 4096 Batches: 8 Memory Usage: 654kB
105. 57.998 57.998 ↑ 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.015..57.998 rows=147,242 loops=1)

  • Filter: (deleted IS FALSE)
106. 53.052 136.635 ↓ 1.0 147,242 1

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

  • Buckets: 2048 Batches: 8 Memory Usage: 1015kB
107. 83.583 83.583 ↓ 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.023..83.583 rows=147,242 loops=1)

  • Filter: (deleted IS FALSE)
108. 52.995 114.743 ↓ 1.0 153,060 1

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

  • Buckets: 2048 Batches: 16 Memory Usage: 489kB
109. 61.748 61.748 ↓ 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..61.748 rows=153,060 loops=1)

110. 0.000 0.538 ↓ 0.0 0 1

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

  • Buckets: 4096 Batches: 4 Memory Usage: 0kB
111. 0.003 0.538 ↓ 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.538..0.538 rows=0 loops=1)

  • Recheck Cond: (useritemcodeparamid = $14)
112. 0.535 0.535 ↓ 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.535..0.535 rows=0 loops=1)

  • Index Cond: (useritemcodeparamid = $14)
113. 12.428 643.548 ↓ 1.6 43,937 1

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

  • Buckets: 4096 Batches: 2 Memory Usage: 773kB
114. 631.120 631.120 ↓ 1.6 43,937 1

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

  • Filter: ((creationdate >= '2019-12-30'::date) AND (creationdate < '2020-01-13'::date))
  • Rows Removed by Filter: 1136199
115.          

SubPlan (for Hash Join)

116. 111.440 17,941.840 ↓ 0.0 0 111,440

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

117. 334.320 17,830.400 ↓ 0.0 0 111,440

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

  • Sort Key: cw_3.creationdate
  • Sort Method: top-N heapsort Memory: 25kB
118. 72.450 17,496.080 ↓ 0.0 0 111,440

Nested Loop (cost=2.24..19.72 rows=1 width=12) (actual time=0.141..0.157 rows=0 loops=111,440)

119. 207.862 17,161.760 ↑ 1.0 1 111,440

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

120. 138.540 16,604.560 ↑ 1.0 1 111,440

Nested Loop (cost=1.69..18.91 rows=1 width=20) (actual time=0.114..0.149 rows=1 loops=111,440)

121. 151.276 7,577.920 ↑ 1.0 1 111,440

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

122. 222.880 6,017.760 ↑ 1.0 1 111,440

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

123. 4,234.720 4,234.720 ↑ 1.0 1 111,440

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

  • Index Cond: ((gsrn)::text = (deliverypoint_1.gsrn)::text)
124. 1,560.160 1,560.160 ↑ 1.0 1 111,440

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

  • Index Cond: (deliverypointid = dp_w_3.deliverypointid)
125. 1,408.884 1,408.884 ↑ 1.0 1 117,407

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

  • Index Cond: (deliveryperiodid = deliveryperiod_5.deliveryperiodid)
126. 8,888.100 8,888.100 ↑ 1.0 1 118,508

Index Scan using crmworkflow_contractdeliveryid_idx on crmworkflow cw_3 (cost=0.43..1.46 rows=1 width=24) (actual time=0.045..0.075 rows=1 loops=118,508)

  • 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)))
  • Rows Removed by Filter: 12
127. 349.338 349.338 ↑ 1.0 1 116,446

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

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

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

  • Index Cond: (parametervalueid = cw_3.crmworkflowstatusparamid)
  • Filter: ((internalparametervaluecode)::text = 'ERROR'::text)
  • Rows Removed by Filter: 1
129. 47.646 553.178 ↓ 2.0 147,654 1

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

  • Buckets: 2048 Batches: 16 (originally 8) Memory Usage: 1025kB
130. 183.852 505.532 ↓ 2.0 147,654 1

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

  • Hash Cond: (physicalconfig_3.physicalconfigid = m_1.physicalconfigid)
131. 123.771 123.771 ↑ 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.012..123.771 rows=259,196 loops=1)

  • Filter: (deleted IS FALSE)
  • Rows Removed by Filter: 1
132. 64.877 197.909 ↓ 2.0 147,654 1

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

  • Buckets: 2048 Batches: 16 (originally 8) Memory Usage: 1025kB
133. 133.032 133.032 ↓ 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.017..133.032 rows=147,654 loops=1)

  • Filter: (((metertype)::text = 'SMART_METER'::text) AND ((smartmeterstatus)::text = 'ACTIVE_READABLE'::text))
  • Rows Removed by Filter: 111543
134.          

SubPlan (for Hash Join)

135. 0.336 5.880 ↑ 1.0 1 84

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

136. 0.168 5.544 ↑ 1.0 1 84

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

137. 2.772 2.772 ↑ 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.032..0.033 rows=1 loops=84)

  • Index Cond: (physicalconfigid = physicalconfig_2.physicalconfigid)
138. 2.604 2.604 ↑ 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.031..0.031 rows=1 loops=84)

  • Index Cond: (physicalconfigid = physicalconfig_2.physicalconfigid)
  • Filter: (deleted IS FALSE)
139. 0.192 10.362 ↓ 2.0 2 33

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

140. 0.447 9.636 ↓ 5.0 5 33

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

141. 0.363 6.699 ↓ 3.1 25 33

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

142. 0.066 2.343 ↑ 1.0 1 33

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

143. 0.099 1.914 ↑ 1.0 1 33

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

144. 1.386 1.386 ↑ 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.042..0.042 rows=1 loops=33)

  • Index Cond: ((gsrn)::text = (deliverypoint_1.gsrn)::text)
145. 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.012..0.013 rows=1 loops=33)

  • Index Cond: (deliverypointid = dp_w_1.deliverypointid)
146. 0.363 0.363 ↑ 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.010..0.011 rows=1 loops=33)

  • Index Cond: (deliveryperiodid = deliveryperiod_2.deliveryperiodid)
147. 3.993 3.993 ↓ 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.121 rows=25 loops=33)

  • Index Cond: (contractdeliveryid = cdp_3.contractdeliveryid)
148. 2.490 2.490 ↓ 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.003..0.003 rows=0 loops=830)

  • Index Cond: (parametervalueid = cw_1.crmworkflowtypeparamid)
  • Filter: ((valuecode)::text = 'NL_indexCollectionKV'::text)
  • Rows Removed by Filter: 1
149. 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
150.          

SubPlan (for Nested Loop Anti Join)

151. 0.033 3.564 ↓ 0.0 0 33

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

152. 0.075 3.531 ↓ 0.0 0 33

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

153. 0.135 3.168 ↓ 3.0 3 33

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

154. 0.066 2.673 ↓ 4.0 4 33

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

155. 0.066 1.518 ↑ 1.0 1 33

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

156. 0.033 1.287 ↑ 1.0 1 33

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

157. 1.089 1.089 ↑ 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.032..0.033 rows=1 loops=33)

  • Index Cond: ((gsrn)::text = (deliverypoint_1.gsrn)::text)
158. 0.165 0.165 ↑ 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.005 rows=1 loops=33)

  • Index Cond: (deliverypointid = dp_w_2.deliverypointid)
159. 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)
160. 1.089 1.089 ↓ 4.0 4 33

Index Scan using crmworkflow_contractdeliveryid_idx on crmworkflow cw_2 (cost=0.43..1.57 rows=1 width=16) (actual time=0.008..0.033 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)) - 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
161. 0.360 0.360 ↑ 1.0 1 120

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

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

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

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

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

164. 0.087 32.505 ↓ 0.0 0 33

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

165. 0.324 30.195 ↑ 1.0 1 33

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

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

Bitmap Heap Scan on index (cost=10.01..14.04 rows=1 width=11) (actual time=0.732..0.761 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
167. 0.231 4.323 ↓ 0.0 0 33

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

168. 2.211 2.211 ↑ 2.3 26 33

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

  • Index Cond: (deliverypointcomponentid = deliverypointcomponent_1.deliverypointcomponentid)
169. 1.881 1.881 ↑ 2.4 25 33

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

  • Index Cond: (deliverypointid = deliverypoint_1.deliverypointid)
170. 0.078 4.758 ↓ 0.0 0 39

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

171. 0.351 4.680 ↓ 0.0 0 39

Sort (cost=16.92..16.92 rows=1 width=156) (actual time=0.120..0.120 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
172. 0.189 4.329 ↑ 1.0 1 39

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

173. 1.053 1.053 ↑ 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.019..0.027 rows=1 loops=39)

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

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

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

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

  • Index Cond: (endindexid = index.indexid)
  • Filter: ((deleted IS FALSE) AND ((sourceid)::text = 'DGO'::text))
  • Rows Removed by Filter: 0
Planning time : 76.421 ms
Execution time : 29,907.037 ms