explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6AJ

Settings
# exclusive inclusive rows x rows loops node
1. 0.156 25,677.058 ↓ 4.6 1,001 1

Limit (cost=1,451,627.44..1,451,627.98 rows=217 width=232) (actual time=25,676.785..25,677.058 rows=1,001 loops=1)

2. 12.715 25,676.902 ↓ 4.6 1,001 1

Sort (cost=1,451,627.44..1,451,627.98 rows=217 width=232) (actual time=25,676.783..25,676.902 rows=1,001 loops=1)

  • Sort Key: deliverypoint.gsrn
  • Sort Method: quicksort Memory: 203kB
3. 5.123 25,664.187 ↓ 4.8 1,034 1

HashAggregate (cost=1,451,614.68..1,451,616.85 rows=217 width=82) (actual time=25,663.867..25,664.187 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.539 25,659.064 ↓ 4.8 1,034 1

Append (cost=53,440.45..1,451,606.54 rows=217 width=82) (actual time=1,523.476..25,659.064 rows=1,034 loops=1)

5. 0.407 5,559.405 ↓ 25.0 1,001 1

Limit (cost=53,440.45..76,124.56 rows=40 width=82) (actual time=1,523.476..5,559.405 rows=1,001 loops=1)

6.          

Initplan (for Limit)

7. 0.045 0.045 ↑ 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.045..0.045 rows=1 loops=1)

  • Index Cond: ((valuecode)::text = 'NL.excludedFromConventionalMeteringRequest'::text)
  • Filter: ((parametercategorycode)::text = 'contractUserItemCode'::text)
8. 11.040 5,558.953 ↓ 25.0 1,001 1

Nested Loop Anti Join (cost=53,432.16..76,116.27 rows=40 width=82) (actual time=1,523.475..5,558.953 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. 44.726 4,618.391 ↓ 25.6 1,024 1

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

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

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

11. 23.891 2,933.229 ↓ 45.3 10,413 1

Nested Loop Left Join (cost=28,796.52..38,792.60 rows=230 width=58) (actual time=940.725..2,933.229 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. 12.370 2,669.839 ↓ 45.3 10,413 1

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

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

Nested Loop Left Join (cost=28,795.68..38,514.49 rows=235 width=43) (actual time=940.630..2,470.035 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. 15.918 1,895.178 ↓ 38.3 12,262 1

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

15. 12.975 1,719.854 ↓ 38.3 12,262 1

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

16. 11.961 1,522.949 ↓ 38.3 12,262 1

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

17. 14.339 1,339.320 ↓ 38.3 12,262 1

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

18. 32.706 958.465 ↓ 20.0 30,543 1

HashAggregate (cost=28,793.58..28,808.82 rows=1,524 width=8) (actual time=940.286..958.465 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.767 925.759 ↓ 20.0 30,543 1

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

20. 312.445 312.445 ↓ 1.0 782 1

Seq Scan on contract co_1 (cost=0.00..14,385.36 rows=762 width=8) (actual time=1.424..312.445 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. 609.547 609.547 ↓ 39.1 29,761 1

Seq Scan on contract co_2 (cost=0.00..14,385.36 rows=762 width=8) (actual time=0.046..609.547 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. 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. 171.668 171.668 ↑ 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.013..0.014 rows=1 loops=12,262)

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

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

  • Index Cond: (deliverypointid = deliveryperiod_1.deliverypointid)
  • Filter: (deleted IS FALSE)
25. 159.406 159.406 ↑ 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.013 rows=1 loops=12,262)

  • Index Cond: (deliverypointcomponentid = deliveryperiod_1.deliverypointcomponentid)
  • Filter: (deleted IS FALSE)
26. 539.528 539.528 ↑ 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.022..0.044 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. 187.434 187.434 ↑ 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.017..0.018 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.015..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. 708.084 708.084 ↓ 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.068..0.068 rows=0 loops=10,413)

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

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

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

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

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

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

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

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

Seq Scan on meter m (cost=0.00..8,052.08 rows=73,834 width=36) (actual time=0.005..115.939 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 343.110 ↑ 1.0 1 11,437

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

37. 11.447 320.236 ↑ 1.0 1 11,437

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

38. 194.429 194.429 ↑ 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.017 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.167 382.976 ↑ 1.0 1 1,024

Nested Loop (cost=2.24..5.93 rows=1 width=8) (actual time=0.291..0.374 rows=1 loops=1,024)

41. 3.860 369.664 ↓ 2.0 2 1,024

Nested Loop (cost=1.96..5.63 rows=1 width=12) (actual time=0.245..0.361 rows=2 loops=1,024)

42. 9.520 294.912 ↓ 2.1 17 1,024

Nested Loop (cost=1.69..3.17 rows=8 width=16) (actual time=0.093..0.288 rows=17 loops=1,024)

43. 2.956 65.536 ↑ 1.0 1 1,024

Nested Loop (cost=1.26..1.60 rows=1 width=8) (actual time=0.059..0.064 rows=1 loops=1,024)

44. 2.048 41.984 ↑ 1.0 1 1,024

Nested Loop (cost=0.84..1.04 rows=1 width=8) (actual time=0.038..0.041 rows=1 loops=1,024)

45. 17.408 17.408 ↑ 1.0 1 1,024

Index Only Scan using deliverypoint_pkey on deliverypoint dp_w (cost=0.42..0.48 rows=1 width=4) (actual time=0.016..0.017 rows=1 loops=1,024)

  • Index Cond: (deliverypointid = deliverypoint.deliverypointid)
  • Heap Fetches: 852
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. 219.856 219.856 ↓ 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.032..0.208 rows=17 loops=1,057)

  • Index Cond: (contractdeliveryid = cdp_1.contractdeliveryid)
49. 70.892 70.892 ↓ 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.004..0.004 rows=0 loops=17,723)

  • Index Cond: (parametervalueid = cw.crmworkflowtypeparamid)
  • Filter: ((valuecode)::text = 'NL_indexCollectionKV'::text)
  • Rows Removed by Filter: 1
50. 11.145 11.145 ↑ 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.005 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 49.049 ↑ 1.0 1 1,001

Aggregate (cost=27.61..27.62 rows=1 width=0) (actual time=0.049..0.049 rows=1 loops=1,001)

53. 1.001 48.048 ↓ 0.0 0 1,001

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

54. 1.001 47.047 ↓ 0.0 0 1,001

Nested Loop (cost=1.82..19.16 rows=1 width=4) (actual time=0.047..0.047 rows=0 loops=1,001)

55. 1.080 46.046 ↓ 0.0 0 1,001

Nested Loop (cost=1.54..18.76 rows=1 width=8) (actual time=0.046..0.046 rows=0 loops=1,001)

56. 2.211 41.041 ↑ 1.0 1 1,001

Nested Loop (cost=1.27..18.35 rows=1 width=12) (actual time=0.023..0.041 rows=1 loops=1,001)

57. 1.702 14.014 ↑ 1.0 1 1,001

Nested Loop (cost=0.84..16.89 rows=1 width=8) (actual time=0.013..0.014 rows=1 loops=1,001)

58. 7.007 7.007 ↑ 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.006..0.007 rows=1 loops=1,001)

  • Index Cond: (deliverypointid = deliverypoint.deliverypointid)
59. 5.305 5.305 ↑ 1.0 1 1,061

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

  • Index Cond: (deliveryperiodid = deliveryperiod_8.deliveryperiodid)
60. 24.816 24.816 ↑ 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.008..0.024 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
61. 3.925 3.925 ↓ 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.005..0.005 rows=0 loops=785)

  • Index Cond: (parametervalueid = cw_6.crmworkflowtypeparamid)
  • Filter: ((valuecode)::text = 'NL_indexCollectionKV'::text)
  • Rows Removed by Filter: 1
62. 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)
63. 0.000 0.000 ↓ 0.0 0

Index Only Scan using deliverypoint_pkey on deliverypoint dp_w_6 (cost=0.42..8.44 rows=1 width=4) (never executed)

  • Index Cond: (deliverypointid = deliverypoint.deliverypointid)
  • Heap Fetches: 0
64. 1.001 45.045 ↓ 0.0 0 1,001

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

65. 1.001 44.044 ↓ 0.0 0 1,001

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

66. 0.000 43.043 ↓ 0.0 0 1,001

Nested Loop (cost=1.82..19.14 rows=1 width=8) (actual time=0.043..0.043 rows=0 loops=1,001)

67. 1.769 43.043 ↓ 0.0 0 1,001

Nested Loop (cost=1.54..18.80 rows=1 width=12) (actual time=0.043..0.043 rows=0 loops=1,001)

68. 2.211 38.038 ↑ 1.0 1 1,001

Nested Loop (cost=1.27..18.47 rows=1 width=16) (actual time=0.019..0.038 rows=1 loops=1,001)

69. 1.762 11.011 ↑ 1.0 1 1,001

Nested Loop (cost=0.84..16.89 rows=1 width=8) (actual time=0.010..0.011 rows=1 loops=1,001)

70. 5.005 5.005 ↑ 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.005 rows=1 loops=1,001)

  • Index Cond: (deliverypointid = deliverypoint.deliverypointid)
71. 4.244 4.244 ↑ 1.0 1 1,061

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

  • Index Cond: (deliveryperiodid = deliveryperiod_9.deliveryperiodid)
72. 24.816 24.816 ↑ 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.024 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
73. 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
74. 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)
75. 0.000 0.000 ↓ 0.0 0

Index Only Scan using deliverypoint_pkey on deliverypoint dp_w_7 (cost=0.42..8.44 rows=1 width=4) (never executed)

  • Index Cond: (deliverypointid = deliverypoint.deliverypointid)
  • Heap Fetches: 0
76. 0.000 397.397 ↓ 0.0 0 1,001

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

77. 2.056 397.397 ↓ 0.0 0 1,001

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

78. 10.441 371.371 ↑ 1.0 1 1,001

Nested Loop Left Join (cost=26.93..30.99 rows=1 width=4) (actual time=0.370..0.371 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. 279.279 347.347 ↑ 1.0 1 1,001

Bitmap Heap Scan on index index_1 (cost=10.01..14.04 rows=1 width=11) (actual time=0.347..0.347 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
80. 5.005 68.068 ↓ 0.0 0 1,001

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

81. 34.034 34.034 ↑ 2.7 22 1,001

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

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

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

  • Index Cond: (deliverypointid = deliverypoint.deliverypointid)
83. 2.397 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)

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. 23.970 23.970 ↓ 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.030..0.030 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. 1.001 55.055 ↓ 0.0 0 1,001

Limit (cost=34.55..34.56 rows=1 width=41) (actual time=0.055..0.055 rows=0 loops=1,001)

90. 4.004 54.054 ↓ 0.0 0 1,001

Sort (cost=34.55..34.56 rows=1 width=41) (actual time=0.054..0.054 rows=0 loops=1,001)

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

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

92. 0.000 49.049 ↓ 0.0 0 1,001

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

93. 1.001 49.049 ↓ 0.0 0 1,001

Nested Loop (cost=1.82..19.16 rows=1 width=8) (actual time=0.049..0.049 rows=0 loops=1,001)

94. 1.865 48.048 ↓ 0.0 0 1,001

Nested Loop (cost=1.54..18.76 rows=1 width=12) (actual time=0.048..0.048 rows=0 loops=1,001)

95. 2.178 43.043 ↑ 1.0 1 1,001

Nested Loop (cost=1.27..18.35 rows=1 width=16) (actual time=0.024..0.043 rows=1 loops=1,001)

96. 2.703 15.015 ↑ 1.0 1 1,001

Nested Loop (cost=0.84..16.89 rows=1 width=8) (actual time=0.014..0.015 rows=1 loops=1,001)

97. 7.007 7.007 ↑ 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.007..0.007 rows=1 loops=1,001)

  • Index Cond: (deliverypointid = deliverypoint.deliverypointid)
98. 5.305 5.305 ↑ 1.0 1 1,061

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

  • Index Cond: (deliveryperiodid = deliveryperiod_10.deliveryperiodid)
99. 25.850 25.850 ↑ 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.025 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
100. 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
101. 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)
102. 0.000 0.000 ↓ 0.0 0

Index Only Scan using deliverypoint_pkey on deliverypoint dp_w_8 (cost=0.42..8.44 rows=1 width=4) (never executed)

  • Index Cond: (deliverypointid = deliverypoint.deliverypointid)
  • Heap Fetches: 0
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 20,099.120 ↑ 5.4 33 1

Limit (cost=224,687.13..1,375,479.81 rows=177 width=82) (actual time=20,018.301..20,099.120 rows=33 loops=1)

105.          

Initplan (for Limit)

106. 0.101 0.101 ↑ 1.0 1 1

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

  • Index Cond: ((valuecode)::text = 'NL.excludedFromConventionalMeteringRequest'::text)
  • Filter: ((parametercategorycode)::text = 'contractUserItemCode'::text)
107. 0.355 20,099.110 ↑ 5.4 33 1

Nested Loop Anti Join (cost=224,678.83..1,375,471.51 rows=177 width=82) (actual time=20,018.300..20,099.110 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. 38.367 20,061.597 ↑ 5.4 33 1

Hash Join (cost=224,676.59..1,346,758.70 rows=177 width=82) (actual time=20,016.403..20,061.597 rows=33 loops=1)

  • Hash Cond: ((SubPlan 5) = m_1.meterid)
109. 149.881 19,365.123 ↑ 11.1 51 1

Hash Join (cost=200,043.62..1,297,605.54 rows=568 width=58) (actual time=5,535.229..19,365.123 rows=51 loops=1)

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

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

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

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

  • Hash Cond: (co_4.contractid = co_3.contractid)
112. 557.112 4,149.422 ↓ 2.4 111,376 1

Hash Right Join (cost=127,276.36..159,214.42 rows=46,968 width=47) (actual time=3,143.986..4,149.422 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: 182477
  • 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
113. 448.827 448.827 ↓ 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.031..448.827 rows=312,143 loops=1)

  • Filter: ((deleted IS FALSE) AND ((sourceid)::text = 'DGO'::text))
  • Rows Removed by Filter: 190015
114. 47.076 3,143.483 ↓ 1.8 116,121 1

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

  • Buckets: 2048 Batches: 16 (originally 8) Memory Usage: 1025kB
115. 264.851 3,096.407 ↓ 1.8 116,121 1

Hash Right Join (cost=111,514.93..125,753.96 rows=63,960 width=47) (actual time=2,676.326..3,096.407 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: 130058
116. 156.227 156.227 ↑ 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.032..156.227 rows=259,194 loops=1)

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

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

  • Buckets: 2048 Batches: 16 (originally 8) Memory Usage: 1025kB
118. 123.625 2,625.290 ↓ 1.8 116,121 1

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

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

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

  • Hash Cond: (deliveryperiod_3.deliverypointcomponentid = deliverypointcomponent_1.deliverypointcomponentid)
120. 121.849 2,152.813 ↓ 1.8 116,121 1

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

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

Merge Join (cost=52,982.15..81,151.12 rows=63,960 width=20) (actual time=1,273.393..1,898.857 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: 192481
122. 70.944 1,456.153 ↓ 1.0 305,044 1

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

123. 436.058 1,385.209 ↓ 1.0 305,044 1

Sort (cost=52,981.73..53,744.12 rows=304,958 width=8) (actual time=1,273.314..1,385.209 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
124. 50.020 949.151 ↓ 1.0 305,044 1

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

125. 482.831 482.831 ↓ 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.062..482.831 rows=152,522 loops=1)

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

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

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

128. 169.484 169.484 ↓ 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.049..169.484 rows=154,503 loops=1)

129. 46.667 132.107 ↓ 1.0 155,528 1

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

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

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

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

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

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

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

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

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

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

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

137. 0.000 0.161 ↓ 0.0 0 1

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

  • Buckets: 4096 Batches: 4 Memory Usage: 0kB
138. 0.002 0.161 ↓ 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.161..0.161 rows=0 loops=1)

  • Recheck Cond: (useritemcodeparamid = $26)
139. 0.159 0.159 ↓ 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.159..0.159 rows=0 loops=1)

  • Index Cond: (useritemcodeparamid = $26)
140. 8.371 556.259 ↓ 1.6 43,937 1

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

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

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

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

SubPlan (for Hash Join)

143. 111.427 14,039.802 ↓ 0.0 0 111,427

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

144. 334.281 13,928.375 ↓ 0.0 0 111,427

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

  • Sort Key: cw_5.creationdate
  • Sort Method: top-N heapsort Memory: 25kB
145. 157.415 13,594.094 ↓ 0.0 0 111,427

Nested Loop (cost=2.24..27.61 rows=1 width=12) (actual time=0.106..0.122 rows=0 loops=111,427)

146. 72.411 12,814.105 ↓ 0.0 0 111,427

Nested Loop (cost=1.82..19.16 rows=1 width=16) (actual time=0.100..0.115 rows=0 loops=111,427)

147. 91.351 12,479.824 ↑ 1.0 1 111,427

Nested Loop (cost=1.54..18.76 rows=1 width=20) (actual time=0.083..0.112 rows=1 loops=111,427)

148. 152.598 11,922.689 ↑ 1.0 1 111,427

Nested Loop (cost=1.27..18.35 rows=1 width=24) (actual time=0.074..0.107 rows=1 loops=111,427)

149. 268.644 3,119.956 ↑ 1.0 1 111,427

Nested Loop (cost=0.84..16.89 rows=1 width=8) (actual time=0.025..0.028 rows=1 loops=111,427)

150. 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 = deliverypoint_1.deliverypointid)
151. 1,291.334 1,291.334 ↑ 1.0 1 117,394

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

  • Index Cond: (deliveryperiodid = deliveryperiod_7.deliveryperiodid)
152. 8,650.135 8,650.135 ↑ 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.044..0.073 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
153. 465.784 465.784 ↑ 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.004 rows=1 loops=116,446)

  • Index Cond: (parametervalueid = cw_5.crmworkflowtypeparamid)
  • Filter: ((valuecode)::text = 'NL_indexCollectionKV'::text)
  • Rows Removed by Filter: 0
154. 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
155. 622.574 622.574 ↑ 1.0 1 36,622

Index Only Scan using deliverypoint_pkey on deliverypoint dp_w_5 (cost=0.42..8.44 rows=1 width=4) (actual time=0.017..0.017 rows=1 loops=36,622)

  • Index Cond: (deliverypointid = deliverypoint_1.deliverypointid)
  • Heap Fetches: 30165
156. 48.344 651.975 ↓ 2.0 147,654 1

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

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

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

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

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

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

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

  • Filter: (((metertype)::text = 'SMART_METER'::text) AND ((smartmeterstatus)::text = 'ACTIVE_READABLE'::text))
  • Rows Removed by Filter: 111543
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.034..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.032..0.033 rows=1 loops=84)

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

Nested Loop (cost=2.24..5.93 rows=1 width=8) (actual time=0.228..0.306 rows=2 loops=33)

167. 0.513 9.339 ↓ 5.0 5 33

Nested Loop (cost=1.96..5.63 rows=1 width=12) (actual time=0.071..0.283 rows=5 loops=33)

168. 0.330 6.336 ↓ 3.1 25 33

Nested Loop (cost=1.69..3.17 rows=8 width=16) (actual time=0.060..0.192 rows=25 loops=33)

169. 0.066 1.452 ↑ 1.0 1 33

Nested Loop (cost=1.26..1.60 rows=1 width=8) (actual time=0.041..0.044 rows=1 loops=33)

170. 0.066 1.023 ↑ 1.0 1 33

Nested Loop (cost=0.84..1.04 rows=1 width=8) (actual time=0.029..0.031 rows=1 loops=33)

171. 0.528 0.528 ↑ 1.0 1 33

Index Only Scan using deliverypoint_pkey on deliverypoint dp_w_1 (cost=0.42..0.48 rows=1 width=4) (actual time=0.015..0.016 rows=1 loops=33)

  • Index Cond: (deliverypointid = deliverypoint_1.deliverypointid)
  • Heap Fetches: 30
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.012..0.013 rows=1 loops=33)

  • Index Cond: (deliverypointid = dp_w_1.deliverypointid)
173. 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.011..0.011 rows=1 loops=33)

  • Index Cond: (deliveryperiodid = deliveryperiod_2.deliveryperiodid)
174. 4.554 4.554 ↓ 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.138 rows=25 loops=33)

  • Index Cond: (contractdeliveryid = cdp_3.contractdeliveryid)
175. 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
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.003 ↑ 1.0 1 33

Aggregate (cost=27.61..27.62 rows=1 width=0) (actual time=0.091..0.091 rows=1 loops=33)

179. 0.111 2.937 ↓ 3.0 3 33

Nested Loop (cost=2.24..27.61 rows=1 width=0) (actual time=0.033..0.089 rows=3 loops=33)

180. 0.075 2.442 ↓ 3.0 3 33

Nested Loop (cost=1.82..19.16 rows=1 width=4) (actual time=0.027..0.074 rows=3 loops=33)

181. 0.102 2.079 ↓ 3.0 3 33

Nested Loop (cost=1.54..18.76 rows=1 width=8) (actual time=0.023..0.063 rows=3 loops=33)

182. 0.033 1.617 ↓ 4.0 4 33

Nested Loop (cost=1.27..18.35 rows=1 width=12) (actual time=0.018..0.049 rows=4 loops=33)

183. 0.099 0.396 ↑ 1.0 1 33

Nested Loop (cost=0.84..16.89 rows=1 width=8) (actual time=0.010..0.012 rows=1 loops=33)

184. 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.005..0.005 rows=1 loops=33)

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

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

  • Index Cond: (deliveryperiodid = deliveryperiod_4.deliveryperiodid)
186. 1.188 1.188 ↓ 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.007..0.036 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
187. 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.002..0.003 rows=1 loops=120)

  • Index Cond: (parametervalueid = cw_2.crmworkflowtypeparamid)
  • Filter: ((valuecode)::text = 'NL_indexCollectionKV'::text)
  • Rows Removed by Filter: 0
188. 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.003..0.003 rows=1 loops=96)

  • Index Cond: (parametervalueid = cw_2.crmworkflowstatusparamid)
  • Filter: ((internalparametervaluecode)::text = 'ERROR'::text)
189. 0.384 0.384 ↑ 1.0 1 96

Index Only Scan using deliverypoint_pkey on deliverypoint dp_w_2 (cost=0.42..8.44 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=96)

  • Index Cond: (deliverypointid = deliverypoint_1.deliverypointid)
  • Heap Fetches: 87
190. 0.033 2.409 ↓ 0.0 0 33

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

191. 0.033 2.376 ↓ 0.0 0 33

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

192. 0.108 2.343 ↓ 0.0 0 33

Nested Loop (cost=1.82..19.14 rows=1 width=8) (actual time=0.071..0.071 rows=0 loops=33)

193. 0.135 1.947 ↓ 3.0 3 33

Nested Loop (cost=1.54..18.80 rows=1 width=12) (actual time=0.023..0.059 rows=3 loops=33)

194. 0.066 1.452 ↓ 4.0 4 33

Nested Loop (cost=1.27..18.47 rows=1 width=16) (actual time=0.019..0.044 rows=4 loops=33)

195. 0.066 0.363 ↑ 1.0 1 33

Nested Loop (cost=0.84..16.89 rows=1 width=8) (actual time=0.010..0.011 rows=1 loops=33)

196. 0.165 0.165 ↑ 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.005..0.005 rows=1 loops=33)

  • Index Cond: (deliverypointid = deliverypoint_1.deliverypointid)
197. 0.132 0.132 ↑ 1.0 1 33

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

  • Index Cond: (deliveryperiodid = deliveryperiod_5.deliveryperiodid)
198. 1.023 1.023 ↓ 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.031 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
199. 0.360 0.360 ↑ 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.003..0.003 rows=1 loops=120)

  • Index Cond: (parametervalueid = cw_3.crmworkflowtypeparamid)
  • Filter: ((valuecode)::text = ANY ('{NL_indexCollectionKV,NL_VKOindex}'::text[]))
  • Rows Removed by Filter: 0
200. 0.288 0.288 ↓ 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.003..0.003 rows=0 loops=96)

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

Index Only Scan using deliverypoint_pkey on deliverypoint dp_w_3 (cost=0.42..8.44 rows=1 width=4) (never executed)

  • Index Cond: (deliverypointid = deliverypoint_1.deliverypointid)
  • Heap Fetches: 0
202. 0.033 15.510 ↓ 0.0 0 33

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

203. 0.027 15.477 ↓ 0.0 0 33

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

204. 0.297 14.553 ↑ 1.0 1 33

Nested Loop Left Join (cost=26.93..30.99 rows=1 width=4) (actual time=0.426..0.441 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. 10.230 12.540 ↑ 1.0 1 33

Bitmap Heap Scan on index (cost=10.01..14.04 rows=1 width=11) (actual time=0.371..0.380 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 2.310 ↓ 0.0 0 33

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

207. 1.122 1.122 ↑ 2.3 26 33

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

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

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

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

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

210. 0.273 1.638 ↓ 0.0 0 39

Sort (cost=16.92..16.92 rows=1 width=156) (actual time=0.042..0.042 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.094 1.365 ↑ 1.0 1 39

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

212. 0.585 0.585 ↑ 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.013..0.015 rows=1 loops=39)

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

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

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

Index Scan using consumption_indexid_idx on consumption (cost=0.43..35.25 rows=25 width=4) (actual time=0.023..0.023 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 6.138 ↑ 1.0 1 33

Limit (cost=34.55..34.56 rows=1 width=41) (actual time=0.186..0.186 rows=1 loops=33)

216. 0.198 6.072 ↑ 1.0 1 33

Sort (cost=34.55..34.56 rows=1 width=41) (actual time=0.184..0.184 rows=1 loops=33)

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

Nested Loop (cost=2.92..34.54 rows=1 width=41) (actual time=0.070..0.178 rows=3 loops=33)

218. 0.111 3.168 ↓ 3.0 3 33

Nested Loop (cost=2.24..27.61 rows=1 width=4) (actual time=0.038..0.096 rows=3 loops=33)

219. 0.045 2.673 ↓ 3.0 3 33

Nested Loop (cost=1.82..19.16 rows=1 width=8) (actual time=0.032..0.081 rows=3 loops=33)

220. 0.114 2.244 ↓ 3.0 3 33

Nested Loop (cost=1.54..18.76 rows=1 width=12) (actual time=0.026..0.068 rows=3 loops=33)

221. 0.066 1.650 ↓ 4.0 4 33

Nested Loop (cost=1.27..18.35 rows=1 width=16) (actual time=0.021..0.050 rows=4 loops=33)

222. 0.066 0.462 ↑ 1.0 1 33

Nested Loop (cost=0.84..16.89 rows=1 width=8) (actual time=0.012..0.014 rows=1 loops=33)

223. 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 = deliverypoint_1.deliverypointid)
224. 0.198 0.198 ↑ 1.0 1 33

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

  • Index Cond: (deliveryperiodid = deliveryperiod_6.deliveryperiodid)
225. 1.122 1.122 ↓ 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.008..0.034 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
226. 0.480 0.480 ↑ 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.004 rows=1 loops=120)

  • Index Cond: (parametervalueid = cw_4.crmworkflowtypeparamid)
  • Filter: ((valuecode)::text = 'NL_indexCollectionKV'::text)
  • Rows Removed by Filter: 0
227. 0.384 0.384 ↑ 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.004 rows=1 loops=96)

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

Index Only Scan using deliverypoint_pkey on deliverypoint dp_w_4 (cost=0.42..8.44 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=96)

  • Index Cond: (deliverypointid = deliverypoint_1.deliverypointid)
  • Heap Fetches: 87
229. 2.592 2.592 ↑ 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.026..0.027 rows=1 loops=96)

  • Index Cond: ((crmworkflowid = cw_4.crmworkflowid) AND (key = 'ERROR_REASON'::text))
  • Heap Fetches: 95
Planning time : 69.781 ms
Execution time : 25,680.931 ms