explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ru7h

Settings
# exclusive inclusive rows x rows loops node
1. 0.019 36,982.706 ↑ 1.1 132 1

Limit (cost=938,604.79..938,605.14 rows=140 width=192) (actual time=36,982.675..36,982.706 rows=132 loops=1)

2. 1.253 36,982.687 ↑ 1.1 132 1

Sort (cost=938,604.79..938,605.14 rows=140 width=192) (actual time=36,982.674..36,982.687 rows=132 loops=1)

  • Sort Key: deliverypoint.gsrn
  • Sort Method: quicksort Memory: 43kB
3. 0.900 36,981.434 ↑ 1.1 132 1

HashAggregate (cost=938,597.00..938,598.40 rows=140 width=82) (actual time=36,981.399..36,981.434 rows=132 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.075 36,980.534 ↑ 1.1 132 1

Append (cost=53,428.97..938,592.45 rows=140 width=82) (actual time=1,518.157..36,980.534 rows=132 loops=1)

5. 0.015 13,979.404 ↓ 2.5 100 1

Limit (cost=53,428.97..121,090.08 rows=40 width=82) (actual time=1,518.157..13,979.404 rows=100 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.044..0.045 rows=1 loops=1)

  • Index Cond: ((valuecode)::text = 'NL.excludedFromConventionalMeteringRequest'::text)
  • Filter: ((parametercategorycode)::text = 'contractUserItemCode'::text)
8. 1.223 13,979.344 ↓ 2.5 100 1

Nested Loop Anti Join (cost=53,420.68..121,081.79 rows=40 width=82) (actual time=1,518.157..13,979.344 rows=100 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: 126
9. 9.288 2,413.349 ↓ 2.5 102 1

Hash Join (cost=53,418.44..69,655.08 rows=40 width=82) (actual time=1,367.701..2,413.349 rows=102 loops=1)

  • Hash Cond: ((SubPlan 8) = m.meterid)
10. 2.234 1,835.494 ↓ 22.4 2,863 1

Nested Loop Anti Join (cost=28,796.95..39,115.87 rows=128 width=58) (actual time=882.594..1,835.494 rows=2,863 loops=1)

11. 5.144 1,512.604 ↓ 12.4 2,863 1

Nested Loop Left Join (cost=28,796.52..38,792.52 rows=230 width=58) (actual time=882.277..1,512.604 rows=2,863 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: 3014
12. 3.058 1,424.433 ↓ 12.4 2,863 1

Nested Loop (cost=28,796.10..38,644.12 rows=230 width=54) (actual time=882.221..1,424.433 rows=2,863 loops=1)

  • Join Filter: (co_1.contractid = co.contractid)
13. 9.681 1,364.115 ↓ 12.2 2,863 1

Nested Loop Left Join (cost=28,795.68..38,514.45 rows=235 width=43) (actual time=882.199..1,364.115 rows=2,863 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: 4135
  • 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: 465
14. 1.662 1,191.362 ↓ 10.4 3,328 1

Nested Loop (cost=28,795.26..38,162.05 rows=320 width=43) (actual time=882.160..1,191.362 rows=3,328 loops=1)

15. 3.533 1,133.124 ↓ 10.4 3,328 1

Nested Loop (cost=28,794.84..37,993.98 rows=320 width=43) (actual time=882.126..1,133.124 rows=3,328 loops=1)

16. 2.939 1,069.687 ↓ 10.4 3,328 1

Nested Loop (cost=28,794.42..37,819.07 rows=320 width=24) (actual time=882.094..1,069.687 rows=3,328 loops=1)

17. 7.619 1,010.172 ↓ 10.4 3,328 1

Nested Loop (cost=28,794.00..37,641.10 rows=320 width=20) (actual time=882.059..1,010.172 rows=3,328 loops=1)

18. 17.703 886.703 ↓ 5.4 8,275 1

HashAggregate (cost=28,793.58..28,808.82 rows=1,524 width=8) (actual time=881.926..886.703 rows=8,275 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.581 869.000 ↓ 20.0 30,543 1

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

20. 301.273 301.273 ↓ 1.0 782 1

Seq Scan on contract co_1 (cost=0.00..14,385.36 rows=762 width=8) (actual time=1.230..301.273 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: 152263
21. 564.146 564.146 ↓ 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..564.146 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: 123284
22. 115.850 115.850 ↓ 0.0 0 8,275

Index Scan using contractdelively_contract_id_idx on contractdelivery cdp (cost=0.42..5.78 rows=1 width=20) (actual time=0.014..0.014 rows=0 loops=8,275)

  • 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. 56.576 56.576 ↑ 1.0 1 3,328

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

  • Index Cond: (deliveryperiodid = cdp.deliveryperiodid)
  • Filter: (deleted IS FALSE)
24. 59.904 59.904 ↑ 1.0 1 3,328

Index Scan using deliverypoint_pkey on deliverypoint (cost=0.42..0.54 rows=1 width=23) (actual time=0.018..0.018 rows=1 loops=3,328)

  • Index Cond: (deliverypointid = deliveryperiod_1.deliverypointid)
  • Filter: (deleted IS FALSE)
25. 56.576 56.576 ↑ 1.0 1 3,328

Index Scan using deliverypointcomponent_pk on deliverypointcomponent (cost=0.42..0.52 rows=1 width=4) (actual time=0.016..0.017 rows=1 loops=3,328)

  • Index Cond: (deliverypointcomponentid = deliveryperiod_1.deliverypointcomponentid)
  • Filter: (deleted IS FALSE)
26. 163.072 163.072 ↑ 1.0 2 3,328

Index Scan using generalconfig_deliverypointcomponentid_idx on generalconfig (cost=0.42..1.05 rows=2 width=42) (actual time=0.026..0.049 rows=2 loops=3,328)

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

Index Scan using contract_pkey on contract co (cost=0.42..0.54 rows=1 width=19) (actual time=0.019..0.020 rows=1 loops=2,863)

  • Index Cond: (contractid = cdp.contractid)
28. 83.027 83.027 ↓ 2.0 2 2,863

Index Scan using physicalconfigid on physicalconfig (cost=0.42..0.63 rows=1 width=20) (actual time=0.020..0.029 rows=2 loops=2,863)

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

Index Scan using idx_contractuserfields_contractid_pk on contractuserfields cuf (cost=0.43..1.40 rows=1 width=4) (actual time=0.112..0.112 rows=0 loops=2,863)

  • Index Cond: (contractid = co.contractid)
  • Filter: (useritemcodeparamid = $36)
  • Rows Removed by Filter: 14
30. 44.705 476.652 ↓ 2.0 147,573 1

Hash (cost=23,193.56..23,193.56 rows=73,834 width=32) (actual time=476.652..476.652 rows=147,573 loops=1)

  • Buckets: 2048 Batches: 16 (originally 8) Memory Usage: 1025kB
31. 165.803 431.947 ↓ 2.0 147,573 1

Hash Join (cost=9,552.00..23,193.56 rows=73,834 width=32) (actual time=165.506..431.947 rows=147,573 loops=1)

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

Seq Scan on physicalconfig physicalconfig_1 (cost=0.00..8,031.65 rows=259,265 width=4) (actual time=0.008..101.246 rows=259,073 loops=1)

  • Filter: (deleted IS FALSE)
  • Rows Removed by Filter: 1
33. 54.086 164.898 ↓ 2.0 147,573 1

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

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

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

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

SubPlan (for Hash Join)

36. 2.965 91.915 ↑ 1.0 1 2,965

Aggregate (cost=16.89..16.90 rows=1 width=4) (actual time=0.031..0.031 rows=1 loops=2,965)

37. 5.930 88.950 ↑ 1.0 1 2,965

Nested Loop (cost=0.84..16.89 rows=1 width=4) (actual time=0.029..0.030 rows=1 loops=2,965)

38. 53.370 53.370 ↑ 1.0 1 2,965

Index Scan using meter_physicalconfigid_idx on meter m_3 (cost=0.42..8.44 rows=1 width=8) (actual time=0.018..0.018 rows=1 loops=2,965)

  • Index Cond: (physicalconfigid = physicalconfig.physicalconfigid)
39. 29.650 29.650 ↑ 1.0 1 2,965

Index Scan using physicalconfig_pk on physicalconfig physicalconfig_5 (cost=0.42..8.44 rows=1 width=4) (actual time=0.010..0.010 rows=1 loops=2,965)

  • Index Cond: (physicalconfigid = physicalconfig.physicalconfigid)
  • Filter: (deleted IS FALSE)
40. 0.261 39.372 ↑ 1.0 1 102

Nested Loop (cost=2.24..6.01 rows=1 width=23) (actual time=0.308..0.386 rows=1 loops=102)

41. 0.951 38.046 ↓ 2.0 2 102

Nested Loop (cost=1.96..5.70 rows=1 width=27) (actual time=0.264..0.373 rows=2 loops=102)

42. 0.837 31.824 ↓ 2.1 17 102

Nested Loop (cost=1.69..3.24 rows=8 width=31) (actual time=0.131..0.312 rows=17 loops=102)

43. 0.243 10.302 ↑ 1.0 1 102

Nested Loop (cost=1.26..1.67 rows=1 width=23) (actual time=0.099..0.101 rows=1 loops=102)

44. 0.408 7.854 ↑ 1.0 1 102

Nested Loop (cost=0.84..1.11 rows=1 width=23) (actual time=0.076..0.077 rows=1 loops=102)

45. 5.202 5.202 ↑ 1.0 1 102

Index Scan using deliverypoint_gsrn_idx on deliverypoint dp_w (cost=0.42..0.55 rows=1 width=23) (actual time=0.050..0.051 rows=1 loops=102)

  • Index Cond: ((gsrn)::text = (deliverypoint.gsrn)::text)
46. 2.244 2.244 ↑ 1.0 1 102

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

  • Index Cond: (deliverypointid = dp_w.deliverypointid)
47. 2.205 2.205 ↑ 1.0 1 105

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

  • Index Cond: (deliveryperiodid = deliveryperiod.deliveryperiodid)
48. 20.685 20.685 ↓ 1.1 17 105

Index Scan using crmworkflow_contractdeliveryid_idx on crmworkflow cw (cost=0.43..1.42 rows=15 width=16) (actual time=0.029..0.197 rows=17 loops=105)

  • Index Cond: (contractdeliveryid = cdp_1.contractdeliveryid)
49. 5.271 5.271 ↓ 0.0 0 1,757

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=1,757)

  • Index Cond: (parametervalueid = cw.crmworkflowtypeparamid)
  • Filter: ((valuecode)::text = 'NL_indexCollectionKV'::text)
  • Rows Removed by Filter: 1
50. 1.065 1.065 ↑ 1.0 1 213

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

  • 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. 0.100 11,414.400 ↓ 0.0 0 100

Limit (cost=9.69..1,213.06 rows=1 width=4) (actual time=114.144..114.144 rows=0 loops=100)

53. 29.303 11,414.300 ↓ 0.0 0 100

Nested Loop (cost=9.69..1,213.06 rows=1 width=4) (actual time=114.143..114.143 rows=0 loops=100)

54. 173.400 9,720.600 ↓ 2,378.0 2,378 100

Nested Loop (cost=9.01..1,205.28 rows=1 width=4) (actual time=0.061..97.206 rows=2,378 loops=100)

55. 905.570 9,070.400 ↓ 397.3 2,384 100

Hash Join (cost=8.74..1,203.28 rows=6 width=8) (actual time=0.056..90.704 rows=2,384 loops=100)

  • Hash Cond: (cw_4.crmworkflowstatusparamid = pv_s_4.parametervalueid)
56. 8,164.800 8,164.800 ↓ 8.4 50,751 100

Index Scan using crmworkflow_effectivedate_idx on crmworkflow cw_4 (cost=0.43..1,172.23 rows=6,049 width=12) (actual time=0.022..81.648 rows=50,751 loops=100)

  • Index Cond: ((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)))
57. 0.002 0.030 ↑ 1.0 1 1

Hash (cost=8.29..8.29 rows=1 width=4) (actual time=0.030..0.030 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
58. 0.028 0.028 ↑ 1.0 1 1

Index Scan using parametervalue_internalparametervaluecode_idx on parametervalue pv_s_4 (cost=0.28..8.29 rows=1 width=4) (actual time=0.028..0.028 rows=1 loops=1)

  • Index Cond: ((internalparametervaluecode)::text = 'RUNNING'::text)
59. 476.800 476.800 ↑ 1.0 1 238,400

Index Scan using parametervalueid on parametervalue pv_t_4 (cost=0.28..0.32 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=238,400)

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

Index Only Scan using crmworkflowdata_crmworkflowid_key_value_idx on crmworkflowdata cwd_1 (cost=0.68..7.77 rows=1 width=4) (actual time=0.007..0.007 rows=0 loops=237,771)

  • Index Cond: ((crmworkflowid = cw_4.crmworkflowid) AND (key = 'BUSINESS_KEY'::text))
  • Filter: (value ~~ ((deliverypoint.gsrn)::text || '_%'::text))
  • Rows Removed by Filter: 1
  • Heap Fetches: 238112
61. 0.100 111.000 ↓ 0.0 0 100

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

62. 0.380 110.900 ↓ 0.0 0 100

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

63. 1.520 107.000 ↑ 1.0 1 100

Nested Loop Left Join (cost=26.93..30.99 rows=1 width=4) (actual time=1.070..1.070 rows=1 loops=100)

  • Filter: ((((index_1.sourceid)::text = 'USER'::text) AND ((i2_1.marketstatus)::text = ANY ('{SUBMITTED,ACCEPTED}'::text[]))) OR ((index_1.sourceid)::text <> 'USER'::text))
64. 77.000 103.400 ↑ 1.0 1 100

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

  • 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: 20
  • Heap Blocks: exact=1926
65. 0.600 26.400 ↓ 0.0 0 100

BitmapAnd (cost=10.01..10.01 rows=1 width=0) (actual time=0.264..0.264 rows=0 loops=100)

66. 14.000 14.000 ↑ 2.9 21 100

Bitmap Index Scan on index_dpcomp_idx (cost=0.00..4.88 rows=60 width=0) (actual time=0.140..0.140 rows=21 loops=100)

  • Index Cond: (deliverypointcomponentid = deliverypointcomponent.deliverypointcomponentid)
67. 11.800 11.800 ↑ 2.9 21 100

Bitmap Index Scan on index_deliverypointid_idx (cost=0.00..4.88 rows=60 width=0) (actual time=0.118..0.118 rows=21 loops=100)

  • Index Cond: (deliverypointid = deliverypoint.deliverypointid)
68. 0.240 2.080 ↓ 0.0 0 80

Limit (cost=16.92..16.92 rows=1 width=156) (actual time=0.026..0.026 rows=0 loops=80)

69. 0.800 1.840 ↓ 0.0 0 80

Sort (cost=16.92..16.92 rows=1 width=156) (actual time=0.023..0.023 rows=0 loops=80)

  • 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
70. 0.080 1.040 ↓ 0.0 0 80

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

71. 0.960 0.960 ↓ 0.0 0 80

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

  • Index Cond: (sourceindexid = index_1.indexid)
  • Heap Fetches: 0
72. 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)
73. 3.520 3.520 ↓ 0.0 0 80

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

  • Index Cond: (endindexid = index_1.indexid)
  • Filter: ((deleted IS FALSE) AND ((sourceid)::text = 'DGO'::text))
  • Rows Removed by Filter: 1
74. 0.000 23,001.055 ↑ 3.1 32 1

Limit (cost=224,617.47..817,500.97 rows=100 width=82) (actual time=21,705.895..23,001.055 rows=32 loops=1)

75.          

Initplan (for Limit)

76. 0.053 0.053 ↑ 1.0 1 1

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

  • Index Cond: ((valuecode)::text = 'NL.excludedFromConventionalMeteringRequest'::text)
  • Filter: ((parametercategorycode)::text = 'contractUserItemCode'::text)
77. 0.386 23,001.029 ↑ 5.5 32 1

Nested Loop Anti Join (cost=224,609.17..1,274,012.97 rows=177 width=82) (actual time=21,705.894..23,001.029 rows=32 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: 52
78. 45.944 21,714.787 ↑ 5.5 32 1

Hash Join (cost=224,606.93..1,046,462.71 rows=177 width=82) (actual time=21,662.470..21,714.787 rows=32 loops=1)

  • Hash Cond: ((SubPlan 3) = m_1.meterid)
79. 202.391 21,200.434 ↑ 11.9 48 1

Hash Join (cost=199,985.45..997,278.78 rows=569 width=58) (actual time=4,613.823..21,200.434 rows=48 loops=1)

  • Hash Cond: ((SubPlan 5) = cw3.crmworkflowid)
80. 117.053 3,732.070 ↓ 4.4 111,428 1

Hash Anti Join (cost=160,070.27..202,521.04 rows=25,474 width=58) (actual time=2,608.889..3,732.070 rows=111,428 loops=1)

  • Hash Cond: (co_3.contractid = cuf_1.contractid)
81. 182.989 3,614.928 ↓ 2.4 111,428 1

Hash Join (cost=134,490.53..169,032.24 rows=45,953 width=58) (actual time=2,608.784..3,614.928 rows=111,428 loops=1)

  • Hash Cond: (co_4.contractid = co_3.contractid)
82. 501.857 3,321.203 ↓ 2.4 111,428 1

Hash Right Join (cost=127,264.58..159,202.64 rows=46,968 width=47) (actual time=2,494.432..3,321.203 rows=111,428 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: 182462
  • 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: 4744
83. 325.397 325.397 ↓ 1.1 312,030 1

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

  • Filter: ((deleted IS FALSE) AND ((sourceid)::text = 'DGO'::text))
  • Rows Removed by Filter: 189789
84. 47.732 2,493.949 ↓ 1.8 116,172 1

Hash (cost=125,742.18..125,742.18 rows=63,960 width=47) (actual time=2,493.949..2,493.949 rows=116,172 loops=1)

  • Buckets: 2048 Batches: 16 (originally 8) Memory Usage: 1025kB
85. 254.396 2,446.217 ↓ 1.8 116,172 1

Hash Right Join (cost=111,514.93..125,742.18 rows=63,960 width=47) (actual time=2,040.209..2,446.217 rows=116,172 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: 130004
86. 152.552 152.552 ↑ 1.0 259,071 1

Seq Scan on physicalconfig physicalconfig_2 (cost=0.00..8,679.81 rows=259,256 width=20) (actual time=0.031..152.552 rows=259,071 loops=1)

  • Filter: ((deleted IS FALSE) AND ((sourceid)::text = 'DGO'::text))
  • Rows Removed by Filter: 3
87. 39.203 2,039.269 ↓ 1.8 116,172 1

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

  • Buckets: 2048 Batches: 16 (originally 8) Memory Usage: 1025kB
88. 98.831 2,000.066 ↓ 1.8 116,172 1

Hash Join (cost=73,471.00..109,992.53 rows=63,960 width=43) (actual time=1,116.211..2,000.066 rows=116,172 loops=1)

  • Hash Cond: (deliveryperiod_3.deliverypointid = deliverypoint_1.deliverypointid)
89. 90.095 1,806.620 ↓ 1.8 116,172 1

Hash Join (cost=66,407.44..99,817.92 rows=63,960 width=24) (actual time=1,019.802..1,806.620 rows=116,172 loops=1)

  • Hash Cond: (deliveryperiod_3.deliverypointcomponentid = deliverypointcomponent_1.deliverypointcomponentid)
90. 109.992 1,645.053 ↓ 1.8 116,172 1

Hash Join (cost=60,231.29..91,102.52 rows=63,960 width=24) (actual time=948.225..1,645.053 rows=116,172 loops=1)

  • Hash Cond: (cdp_2.deliveryperiodid = deliveryperiod_3.deliveryperiodid)
91. 206.265 1,442.922 ↓ 1.8 116,172 1

Merge Join (cost=52,982.15..81,151.12 rows=63,960 width=20) (actual time=855.730..1,442.922 rows=116,172 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: 192400
92. 66.120 1,024.246 ↓ 1.0 305,014 1

Unique (cost=52,981.73..55,268.91 rows=304,958 width=8) (actual time=855.625..1,024.246 rows=305,014 loops=1)

93. 343.054 958.126 ↓ 1.0 305,014 1

Sort (cost=52,981.73..53,744.12 rows=304,958 width=8) (actual time=855.623..958.126 rows=305,014 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: 5376kB
94. 34.057 615.072 ↓ 1.0 305,014 1

Append (cost=0.00..21,030.72 rows=304,958 width=8) (actual time=0.042..615.072 rows=305,014 loops=1)

95. 301.466 301.466 ↓ 1.0 152,507 1

Seq Scan on contract co_4 (cost=0.00..8,990.57 rows=152,479 width=8) (actual time=0.041..301.466 rows=152,507 loops=1)

  • Filter: (supplierindexcollectiondate IS NOT NULL)
  • Rows Removed by Filter: 538
96. 279.549 279.549 ↓ 1.0 152,507 1

Seq Scan on contract co_5 (cost=0.00..8,990.57 rows=152,479 width=8) (actual time=0.027..279.549 rows=152,507 loops=1)

  • Filter: (supplierindexcollectiondate IS NOT NULL)
  • Rows Removed by Filter: 538
97. 50.034 212.411 ↓ 2.0 308,773 1

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

98. 162.377 162.377 ↓ 1.0 154,488 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.075..162.377 rows=154,488 loops=1)

99. 31.890 92.139 ↓ 1.0 155,311 1

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

  • Buckets: 4096 Batches: 8 Memory Usage: 847kB
100. 60.249 60.249 ↓ 1.0 155,311 1

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

  • Filter: (deleted IS FALSE)
101. 27.380 71.472 ↑ 1.0 147,230 1

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

  • Buckets: 4096 Batches: 8 Memory Usage: 654kB
102. 44.092 44.092 ↑ 1.0 147,230 1

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

  • Filter: (deleted IS FALSE)
103. 36.676 94.615 ↓ 1.0 147,230 1

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

  • Buckets: 2048 Batches: 8 Memory Usage: 1015kB
104. 57.939 57.939 ↓ 1.0 147,230 1

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

  • Filter: (deleted IS FALSE)
105. 51.227 110.736 ↓ 1.0 153,045 1

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

  • Buckets: 2048 Batches: 16 Memory Usage: 489kB
106. 59.509 59.509 ↓ 1.0 153,045 1

Seq Scan on contract co_3 (cost=0.00..4,416.20 rows=153,020 width=19) (actual time=0.030..59.509 rows=153,045 loops=1)

107. 0.000 0.089 ↓ 0.0 0 1

Hash (cost=24,460.34..24,460.34 rows=68,193 width=4) (actual time=0.089..0.089 rows=0 loops=1)

  • Buckets: 4096 Batches: 4 Memory Usage: 0kB
108. 0.001 0.089 ↓ 0.0 0 1

Bitmap Heap Scan on contractuserfields cuf_1 (cost=1,276.92..24,460.34 rows=68,193 width=4) (actual time=0.089..0.089 rows=0 loops=1)

  • Recheck Cond: (useritemcodeparamid = $11)
109. 0.088 0.088 ↓ 0.0 0 1

Bitmap Index Scan on idx_contractuserfields_useritemcodeparamid (cost=0.00..1,259.88 rows=68,193 width=0) (actual time=0.088..0.088 rows=0 loops=1)

  • Index Cond: (useritemcodeparamid = $11)
110. 8.050 433.097 ↓ 1.6 43,937 1

Hash (cost=39,471.46..39,471.46 rows=27,017 width=4) (actual time=433.097..433.097 rows=43,937 loops=1)

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

Seq Scan on crmworkflow cw3 (cost=0.00..39,471.46 rows=27,017 width=4) (actual time=0.133..425.047 rows=43,937 loops=1)

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

SubPlan (for Hash Join)

113. 111.476 16,832.876 ↓ 0.0 0 111,476

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

114. 222.952 16,721.400 ↓ 0.0 0 111,476

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

  • Sort Key: cw_3.creationdate
  • Sort Method: top-N heapsort Memory: 25kB
115. 72.567 16,498.448 ↓ 0.0 0 111,476

Nested Loop (cost=2.24..19.72 rows=1 width=12) (actual time=0.132..0.148 rows=0 loops=111,476)

116. 208.051 16,164.020 ↑ 1.0 1 111,476

Nested Loop (cost=1.96..19.32 rows=1 width=16) (actual time=0.116..0.145 rows=1 loops=111,476)

117. 174.726 15,606.640 ↑ 1.0 1 111,476

Nested Loop (cost=1.69..18.91 rows=1 width=20) (actual time=0.108..0.140 rows=1 loops=111,476)

118. 157.293 7,134.464 ↑ 1.0 1 111,476

Nested Loop (cost=1.26..17.45 rows=1 width=4) (actual time=0.061..0.064 rows=1 loops=111,476)

119. 111.476 5,685.276 ↑ 1.0 1 111,476

Nested Loop (cost=0.84..16.89 rows=1 width=4) (actual time=0.050..0.051 rows=1 loops=111,476)

120. 4,124.612 4,124.612 ↑ 1.0 1 111,476

Index Scan using deliverypoint_gsrn_idx on deliverypoint dp_w_2 (cost=0.42..8.44 rows=1 width=4) (actual time=0.036..0.037 rows=1 loops=111,476)

  • Index Cond: ((gsrn)::text = (deliverypoint_1.gsrn)::text)
121. 1,449.188 1,449.188 ↑ 1.0 1 111,476

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

  • Index Cond: (deliverypointid = dp_w_2.deliverypointid)
122. 1,291.895 1,291.895 ↑ 1.0 1 117,445

Index Scan using contractdelivery_deliveryperiodid_idx on contractdelivery cdp_4 (cost=0.42..0.55 rows=1 width=8) (actual time=0.010..0.011 rows=1 loops=117,445)

  • Index Cond: (deliveryperiodid = deliveryperiod_4.deliveryperiodid)
123. 8,297.450 8,297.450 ↑ 1.0 1 118,535

Index Scan using crmworkflow_contractdeliveryid_idx on crmworkflow cw_3 (cost=0.43..1.46 rows=1 width=24) (actual time=0.042..0.070 rows=1 loops=118,535)

  • 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: 12
124. 349.329 349.329 ↑ 1.0 1 116,443

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,443)

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

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,287)

  • Index Cond: (parametervalueid = cw_3.crmworkflowstatusparamid)
  • Filter: ((internalparametervaluecode)::text = 'ERROR'::text)
  • Rows Removed by Filter: 1
126. 46.513 463.209 ↓ 2.0 147,573 1

Hash (cost=23,193.56..23,193.56 rows=73,834 width=32) (actual time=463.209..463.209 rows=147,573 loops=1)

  • Buckets: 2048 Batches: 16 (originally 8) Memory Usage: 1025kB
127. 167.635 416.696 ↓ 2.0 147,573 1

Hash Join (cost=9,552.00..23,193.56 rows=73,834 width=32) (actual time=155.317..416.696 rows=147,573 loops=1)

  • Hash Cond: (physicalconfig_3.physicalconfigid = m_1.physicalconfigid)
128. 94.568 94.568 ↑ 1.0 259,073 1

Seq Scan on physicalconfig physicalconfig_3 (cost=0.00..8,031.65 rows=259,265 width=4) (actual time=0.016..94.568 rows=259,073 loops=1)

  • Filter: (deleted IS FALSE)
  • Rows Removed by Filter: 1
129. 51.272 154.493 ↓ 2.0 147,573 1

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

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

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

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

SubPlan (for Hash Join)

132. 0.320 5.200 ↑ 1.0 1 80

Aggregate (cost=16.89..16.90 rows=1 width=4) (actual time=0.065..0.065 rows=1 loops=80)

133. 0.160 4.880 ↑ 1.0 1 80

Nested Loop (cost=0.84..16.89 rows=1 width=4) (actual time=0.060..0.061 rows=1 loops=80)

134. 2.400 2.400 ↑ 1.0 1 80

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

  • Index Cond: (physicalconfigid = physicalconfig_2.physicalconfigid)
135. 2.320 2.320 ↑ 1.0 1 80

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

  • Index Cond: (physicalconfigid = physicalconfig_2.physicalconfigid)
  • Filter: (deleted IS FALSE)
136. 0.200 9.408 ↓ 2.0 2 32

Nested Loop (cost=2.24..6.01 rows=1 width=23) (actual time=0.231..0.294 rows=2 loops=32)

137. 0.000 8.704 ↓ 5.0 5 32

Nested Loop (cost=1.96..5.70 rows=1 width=27) (actual time=0.102..0.272 rows=5 loops=32)

138. 0.320 6.272 ↓ 3.2 26 32

Nested Loop (cost=1.69..3.24 rows=8 width=31) (actual time=0.090..0.196 rows=26 loops=32)

139. 0.064 2.336 ↑ 1.0 1 32

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

140. 0.128 1.888 ↑ 1.0 1 32

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

141. 1.280 1.280 ↑ 1.0 1 32

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

  • Index Cond: ((gsrn)::text = (deliverypoint_1.gsrn)::text)
142. 0.480 0.480 ↑ 1.0 1 32

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

  • Index Cond: (deliverypointid = dp_w_1.deliverypointid)
143. 0.384 0.384 ↑ 1.0 1 32

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

  • Index Cond: (deliveryperiodid = deliveryperiod_2.deliveryperiodid)
144. 3.616 3.616 ↓ 1.7 26 32

Index Scan using crmworkflow_contractdeliveryid_idx on crmworkflow cw_1 (cost=0.43..1.42 rows=15 width=16) (actual time=0.017..0.113 rows=26 loops=32)

  • Index Cond: (contractdeliveryid = cdp_3.contractdeliveryid)
145. 2.457 2.457 ↓ 0.0 0 819

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

  • Index Cond: (parametervalueid = cw_1.crmworkflowtypeparamid)
  • Filter: ((valuecode)::text = 'NL_indexCollectionKV'::text)
  • Rows Removed by Filter: 1
146. 0.504 0.504 ↓ 0.0 0 168

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

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

SubPlan (for Nested Loop Anti Join)

148. 0.032 1,261.536 ↓ 0.0 0 32

Limit (cost=9.69..1,213.06 rows=1 width=4) (actual time=39.423..39.423 rows=0 loops=32)

149. 0.294 1,261.504 ↓ 0.0 0 32

Nested Loop (cost=9.69..1,213.06 rows=1 width=4) (actual time=39.422..39.422 rows=0 loops=32)

150. 0.520 1,256.800 ↓ 9.0 9 32

Nested Loop (cost=9.01..1,205.28 rows=1 width=4) (actual time=0.508..39.275 rows=9 loops=32)

151. 226.438 1,255.104 ↓ 1.5 9 32

Hash Join (cost=8.74..1,203.28 rows=6 width=8) (actual time=0.502..39.222 rows=9 loops=32)

  • Hash Cond: (cw_2.crmworkflowstatusparamid = pv_s_2.parametervalueid)
152. 1,028.640 1,028.640 ↓ 5.7 34,267 32

Index Scan using crmworkflow_effectivedate_idx on crmworkflow cw_2 (cost=0.43..1,172.23 rows=6,049 width=12) (actual time=0.019..32.145 rows=34,267 loops=32)

  • Index Cond: ((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)))
153. 0.001 0.026 ↑ 1.0 1 1

Hash (cost=8.29..8.29 rows=1 width=4) (actual time=0.026..0.026 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
154. 0.025 0.025 ↑ 1.0 1 1

Index Scan using parametervalue_internalparametervaluecode_idx on parametervalue pv_s_2 (cost=0.28..8.29 rows=1 width=4) (actual time=0.025..0.025 rows=1 loops=1)

  • Index Cond: ((internalparametervaluecode)::text = 'RUNNING'::text)
155. 1.176 1.176 ↑ 1.0 1 294

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

  • Index Cond: (parametervalueid = cw_2.crmworkflowtypeparamid)
  • Filter: ((valuecode)::text = ANY ('{NL_indexCollectionKV,NL_VKOindex}'::text[]))
156. 4.410 4.410 ↓ 0.0 0 294

Index Only Scan using crmworkflowdata_crmworkflowid_key_value_idx on crmworkflowdata cwd (cost=0.68..7.77 rows=1 width=4) (actual time=0.015..0.015 rows=0 loops=294)

  • Index Cond: ((crmworkflowid = cw_2.crmworkflowid) AND (key = 'BUSINESS_KEY'::text))
  • Filter: (value ~~ ((deliverypoint_1.gsrn)::text || '_%'::text))
  • Rows Removed by Filter: 1
  • Heap Fetches: 294
157. 0.064 14.912 ↓ 0.0 0 32

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

158. 0.060 14.848 ↓ 0.0 0 32

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

159. 0.492 13.888 ↑ 1.0 1 32

Nested Loop Left Join (cost=26.93..30.99 rows=1 width=4) (actual time=0.419..0.434 rows=1 loops=32)

  • Filter: ((((index.sourceid)::text = 'USER'::text) AND ((i2.marketstatus)::text = ANY ('{SUBMITTED,ACCEPTED}'::text[]))) OR ((index.sourceid)::text <> 'USER'::text))
160. 8.960 11.488 ↑ 1.0 1 32

Bitmap Heap Scan on index (cost=10.01..14.04 rows=1 width=11) (actual time=0.350..0.359 rows=1 loops=32)

  • 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=756
161. 0.192 2.528 ↓ 0.0 0 32

BitmapAnd (cost=10.01..10.01 rows=1 width=0) (actual time=0.079..0.079 rows=0 loops=32)

162. 1.344 1.344 ↑ 2.2 27 32

Bitmap Index Scan on index_dpcomp_idx (cost=0.00..4.88 rows=60 width=0) (actual time=0.042..0.042 rows=27 loops=32)

  • Index Cond: (deliverypointcomponentid = deliverypointcomponent_1.deliverypointcomponentid)
163. 0.992 0.992 ↑ 2.3 26 32

Bitmap Index Scan on index_deliverypointid_idx (cost=0.00..4.88 rows=60 width=0) (actual time=0.031..0.031 rows=26 loops=32)

  • Index Cond: (deliverypointid = deliverypoint_1.deliverypointid)
164. 0.072 1.908 ↓ 0.0 0 36

Limit (cost=16.92..16.92 rows=1 width=156) (actual time=0.053..0.053 rows=0 loops=36)

165. 0.396 1.836 ↓ 0.0 0 36

Sort (cost=16.92..16.92 rows=1 width=156) (actual time=0.051..0.051 rows=0 loops=36)

  • 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
166. 0.180 1.440 ↑ 1.0 1 36

Nested Loop (cost=0.85..16.91 rows=1 width=156) (actual time=0.029..0.040 rows=1 loops=36)

167. 0.540 0.540 ↑ 1.0 1 36

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

  • Index Cond: (sourceindexid = index.indexid)
  • Heap Fetches: 48
168. 0.720 0.720 ↑ 1.0 1 48

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

  • Index Cond: (indexid = ir.targetindexid)
  • Filter: ((sourceid)::text = 'SUPPLIER'::text)
169. 0.900 0.900 ↓ 0.0 0 36

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

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