explain.depesz.com

PostgreSQL's explain analyze made readable

Result: PQMm

Settings
# exclusive inclusive rows x rows loops node
1. 0.079 169,639.741 ↓ 4.6 1,001 1

Limit (cost=1,394,384.41..1,394,384.96 rows=217 width=192) (actual time=169,639.601..169,639.741 rows=1,001 loops=1)

2. 7.263 169,639.662 ↓ 4.6 1,001 1

Sort (cost=1,394,384.41..1,394,384.96 rows=217 width=192) (actual time=169,639.600..169,639.662 rows=1,001 loops=1)

  • Sort Key: deliverypoint.gsrn
  • Sort Method: quicksort Memory: 194kB
3. 7.250 169,632.399 ↓ 4.8 1,034 1

HashAggregate (cost=1,394,371.65..1,394,373.82 rows=217 width=82) (actual time=169,632.083..169,632.399 rows=1,034 loops=1)

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

Append (cost=53,440.45..1,394,364.60 rows=217 width=82) (actual time=1,773.105..169,625.149 rows=1,034 loops=1)

5. 0.554 144,489.801 ↓ 25.0 1,001 1

Limit (cost=53,440.45..121,073.59 rows=40 width=82) (actual time=1,773.105..144,489.801 rows=1,001 loops=1)

6.          

Initplan (for Limit)

7. 0.047 0.047 ↑ 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.047..0.047 rows=1 loops=1)

  • Index Cond: ((valuecode)::text = 'NL.excludedFromConventionalMeteringRequest'::text)
  • Filter: ((parametercategorycode)::text = 'contractUserItemCode'::text)
8. 13.808 144,489.200 ↓ 25.0 1,001 1

Nested Loop Anti Join (cost=53,432.16..121,065.29 rows=40 width=82) (actual time=1,773.104..144,489.200 rows=1,001 loops=1)

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

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

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

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

11. 26.544 3,837.066 ↓ 45.3 10,413 1

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

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

Nested Loop (cost=28,796.10..38,644.16 rows=230 width=54) (actual time=1,024.597..3,466.893 rows=10,413 loops=1)

  • Join Filter: (co_1.contractid = co.contractid)
13. 43.656 3,209.175 ↓ 44.3 10,413 1

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

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

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

15. 12.883 2,116.119 ↓ 38.3 12,262 1

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

16. 19.070 1,845.734 ↓ 38.3 12,262 1

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

17. 31.797 1,593.686 ↓ 38.3 12,262 1

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

18. 32.157 1,042.658 ↓ 20.0 30,543 1

HashAggregate (cost=28,793.58..28,808.82 rows=1,524 width=8) (actual time=1,023.805..1,042.658 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. 4.098 1,010.501 ↓ 20.0 30,543 1

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

20. 374.363 374.363 ↓ 1.0 782 1

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

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

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

  • Filter: ((supplierindexcollectiondate IS NOT NULL) AND (to_date(((('2020-'::text || (date_part('month'::text, (supplierindexcollectiondate)::timestamp without time zone))::text) || '-'::text) || (date_part('day'::text, (supplierindexcollectiondate)::timestamp without time zone))::text), 'YYYY-MM-DD'::text) >= '2019-12-24'::date) AND (to_date(((('2020-'::text || (date_part('month'::text, (supplierindexcollectiondate)::timestamp without time zone))::text) || '-'::text) || (date_part('day'::text, (supplierindexcollectiondate)::timestamp without time zone))::text), 'YYYY-MM-DD'::text) < '2020-01-13'::date))
  • Rows Removed by Filter: 123,299
22. 519.231 519.231 ↓ 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.017..0.017 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. 232.978 232.978 ↑ 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.019..0.019 rows=1 loops=12,262)

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

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

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

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

  • Index Cond: (deliverypointcomponentid = deliveryperiod_1.deliverypointcomponentid)
  • Filter: (deleted IS FALSE)
26. 784.768 784.768 ↑ 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.032..0.064 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. 239.499 239.499 ↑ 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.022..0.023 rows=1 loops=10,413)

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

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

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

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

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

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

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

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

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

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

  • Buckets: 2,048 Batches: 16 (originally 8) Memory Usage: 1,025kB
34. 141.370 141.370 ↓ 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..141.370 rows=147,654 loops=1)

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

SubPlan (for Hash Join)

36. 22.874 457.480 ↑ 1.0 1 11,437

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

37. 22.887 434.606 ↑ 1.0 1 11,437

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

38. 263.051 263.051 ↑ 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.022..0.023 rows=1 loops=11,437)

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

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

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

41. 16.463 532.480 ↓ 2.0 2 1,024

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

42. 10.216 462.848 ↓ 2.1 17 1,024

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

43. 3.984 156.672 ↑ 1.0 1 1,024

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

44. 4.096 113.664 ↑ 1.0 1 1,024

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

45. 68.608 68.608 ↑ 1.0 1 1,024

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

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

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

  • Index Cond: (deliveryperiodid = deliveryperiod.deliveryperiodid)
48. 295.960 295.960 ↓ 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.049..0.280 rows=17 loops=1,057)

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

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

  • Index Cond: (parametervalueid = cw.crmworkflowtypeparamid)
  • Filter: ((valuecode)::text = 'NL_indexCollectionKV'::text)
  • Rows Removed by Filter: 1
50. 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.005..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 137,291.154 ↓ 0.0 0 1,001

Limit (cost=9.69..1,213.41 rows=1 width=4) (actual time=137.154..137.154 rows=0 loops=1,001)

53. 1,302.399 137,290.153 ↓ 0.0 0 1,001

Nested Loop (cost=9.69..1,213.41 rows=1 width=4) (actual time=137.153..137.153 rows=0 loops=1,001)

54. 644.886 117,175.058 ↓ 2,349.0 2,349 1,001

Nested Loop (cost=9.01..1,205.63 rows=1 width=4) (actual time=0.274..117.058 rows=2,349 loops=1,001)

55. 10,585.532 109,460.351 ↓ 392.3 2,354 1,001

Hash Join (cost=8.74..1,203.62 rows=6 width=8) (actual time=0.136..109.351 rows=2,354 loops=1,001)

  • Hash Cond: (cw_4.crmworkflowstatusparamid = pv_s_4.parametervalueid)
56. 98,874.776 98,874.776 ↓ 8.4 50,644 1,001

Index Scan using crmworkflow_effectivedate_idx on crmworkflow cw_4 (cost=0.43..1,172.57 rows=6,051 width=12) (actual time=0.025..98.776 rows=50,644 loops=1,001)

  • 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.043 ↑ 1.0 1 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 1kB
58. 0.041 0.041 ↑ 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.040..0.041 rows=1 loops=1)

  • Index Cond: ((internalparametervaluecode)::text = 'RUNNING'::text)
59. 7,069.821 7,069.821 ↑ 1.0 1 2,356,607

Index Scan using parametervalueid on parametervalue pv_t_4 (cost=0.28..0.32 rows=1 width=4) (actual time=0.002..0.003 rows=1 loops=2,356,607)

  • Index Cond: (parametervalueid = cw_4.crmworkflowtypeparamid)
  • Filter: ((valuecode)::text = ANY ('{NL_indexCollectionKV,NL_VKOindex}'::text[]))
  • Rows Removed by Filter: 0
60. 18,812.696 18,812.696 ↓ 0.0 0 2,351,587

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.008..0.008 rows=0 loops=2,351,587)

  • Index Cond: ((crmworkflowid = cw_4.crmworkflowid) AND (key = 'BUSINESS_KEY'::text))
  • Filter: (value ~~ ((deliverypoint.gsrn)::text || '_%'::text))
  • Rows Removed by Filter: 1
  • Heap Fetches: 2,355,227
61. 2.002 738.738 ↓ 0.0 0 1,001

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

62. 1.486 736.736 ↓ 0.0 0 1,001

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

63. 17.071 697.697 ↑ 1.0 1 1,001

Nested Loop Left Join (cost=26.93..30.99 rows=1 width=4) (actual time=0.696..0.697 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))
64. 468.468 656.656 ↑ 1.0 1 1,001

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

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

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

66. 98.098 98.098 ↑ 2.7 22 1,001

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

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

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

  • Index Cond: (deliverypointid = deliverypoint.deliverypointid)
68. 3.196 23.970 ↓ 0.0 0 799

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

69. 8.789 20.774 ↓ 0.0 0 799

Sort (cost=16.92..16.92 rows=1 width=156) (actual time=0.026..0.026 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
70. 0.799 11.985 ↓ 0.0 0 799

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

71. 11.186 11.186 ↓ 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.014..0.014 rows=0 loops=799)

  • 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. 37.553 37.553 ↓ 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.047..0.047 rows=0 loops=799)

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

Limit (cost=224,687.13..1,273,288.84 rows=177 width=82) (actual time=23,821.614..25,134.657 rows=33 loops=1)

75.          

Initplan (for Limit)

76. 0.106 0.106 ↑ 1.0 1 1

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

  • Index Cond: ((valuecode)::text = 'NL.excludedFromConventionalMeteringRequest'::text)
  • Filter: ((parametercategorycode)::text = 'contractUserItemCode'::text)
77. 0.462 25,134.632 ↑ 5.4 33 1

Nested Loop Anti Join (cost=224,678.83..1,273,280.55 rows=177 width=82) (actual time=23,821.613..25,134.632 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
78. 42.912 23,790.839 ↑ 5.4 33 1

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

  • Hash Cond: ((SubPlan 3) = m_1.meterid)
79. 171.442 23,080.152 ↑ 11.1 51 1

Hash Join (cost=200,043.62..996,515.38 rows=568 width=58) (actual time=6,221.001..23,080.152 rows=51 loops=1)

  • Hash Cond: ((SubPlan 5) = cw3.crmworkflowid)
80. 127.918 4,736.315 ↓ 4.4 111,389 1

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

  • Hash Cond: (co_3.contractid = cuf_1.contractid)
81. 193.584 4,608.037 ↓ 2.4 111,389 1

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

  • Hash Cond: (co_4.contractid = co_3.contractid)
82. 552.941 4,219.719 ↓ 2.4 111,389 1

Hash Right Join (cost=127,276.36..159,214.42 rows=46,968 width=47) (actual time=3,099.281..4,219.719 rows=111,389 loops=1)

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

  • Filter: ((deleted IS FALSE) AND ((sourceid)::text = 'DGO'::text))
  • Rows Removed by Filter: 190,015
84. 58.251 3,098.643 ↓ 1.8 116,134 1

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

  • Buckets: 2,048 Batches: 16 (originally 8) Memory Usage: 1,025kB
85. 313.879 3,040.392 ↓ 1.8 116,134 1

Hash Right Join (cost=111,514.93..125,753.96 rows=63,960 width=47) (actual time=2,539.061..3,040.392 rows=116,134 loops=1)

  • Hash Cond: ((physicalconfig_2.deliverypointid = deliverypoint_1.deliverypointid) AND (physicalconfig_2.deliverypointcomponentid = deliverypointcomponent_1.deliverypointcomponentid))
  • Join Filter: ((physicalconfig_2.fromdate <= (to_date(((('2019-'::text || (date_part('month'::text, (co_4.supplierindexcollectiondate)::timestamp without time zone))::text) || '-'::text) || (date_part('day'::text, (co_4.supplierindexcollectiondate)::timestamp without time zone))::text), 'YYYY-MM-DD'::text))) AND ((physicalconfig_2.todate IS NULL) OR (physicalconfig_2.todate > (to_date(((('2019-'::text || (date_part('month'::text, (co_4.supplierindexcollectiondate)::timestamp without time zone))::text) || '-'::text) || (date_part('day'::text, (co_4.supplierindexcollectiondate)::timestamp without time zone))::text), 'YYYY-MM-DD'::text)))))
  • Rows Removed by Join Filter: 130,078
86. 188.641 188.641 ↑ 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.037..188.641 rows=259,194 loops=1)

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

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

  • Buckets: 2,048 Batches: 16 (originally 8) Memory Usage: 1,025kB
88. 104.272 2,495.254 ↓ 1.8 116,134 1

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

  • Hash Cond: (deliveryperiod_3.deliverypointid = deliverypoint_1.deliverypointid)
89. 101.396 2,281.980 ↓ 1.8 116,134 1

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

  • Hash Cond: (deliveryperiod_3.deliverypointcomponentid = deliverypointcomponent_1.deliverypointcomponentid)
90. 135.343 2,053.295 ↓ 1.8 116,134 1

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

  • Hash Cond: (cdp_2.deliveryperiodid = deliveryperiod_3.deliveryperiodid)
91. 274.870 1,768.145 ↓ 1.8 116,134 1

Merge Join (cost=52,982.15..81,151.12 rows=63,960 width=20) (actual time=973.415..1,768.145 rows=116,134 loops=1)

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

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

93. 402.718 1,116.497 ↓ 1.0 305,044 1

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

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

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

95. 347.530 347.530 ↓ 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.055..347.530 rows=152,522 loops=1)

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

  • Filter: (supplierindexcollectiondate IS NOT NULL)
  • Rows Removed by Filter: 538
97. 67.647 288.899 ↓ 2.0 308,803 1

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

98. 221.252 221.252 ↓ 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.039..221.252 rows=154,503 loops=1)

99. 47.977 149.807 ↓ 1.0 155,528 1

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

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

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

  • Filter: (deleted IS FALSE)
101. 43.899 127.289 ↑ 1.0 147,242 1

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

  • Buckets: 4,096 Batches: 8 Memory Usage: 654kB
102. 83.390 83.390 ↑ 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.108..83.390 rows=147,242 loops=1)

  • Filter: (deleted IS FALSE)
103. 39.821 109.002 ↓ 1.0 147,242 1

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

  • Buckets: 2,048 Batches: 8 Memory Usage: 1,015kB
104. 69.181 69.181 ↓ 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.021..69.181 rows=147,242 loops=1)

  • Filter: (deleted IS FALSE)
105. 72.321 194.734 ↓ 1.0 153,060 1

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

  • Buckets: 2,048 Batches: 16 Memory Usage: 489kB
106. 122.413 122.413 ↓ 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.016..122.413 rows=153,060 loops=1)

107. 0.001 0.360 ↓ 0.0 0 1

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

  • Buckets: 4,096 Batches: 4 Memory Usage: 0kB
108. 0.002 0.359 ↓ 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.359..0.359 rows=0 loops=1)

  • Recheck Cond: (useritemcodeparamid = $11)
109. 0.357 0.357 ↓ 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.357..0.357 rows=0 loops=1)

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

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

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

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

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

SubPlan (for Hash Join)

113. 111.440 17,496.080 ↓ 0.0 0 111,440

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

114. 334.320 17,384.640 ↓ 0.0 0 111,440

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

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

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

116. 207.862 16,716.000 ↑ 1.0 1 111,440

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

117. 152.676 16,158.800 ↑ 1.0 1 111,440

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

118. 157.243 7,355.040 ↑ 1.0 1 111,440

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

119. 111.440 5,906.320 ↑ 1.0 1 111,440

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

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

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

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

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

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

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

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

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

  • 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.338 349.338 ↑ 1.0 1 116,446

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

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

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

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

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

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

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

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

  • Filter: (deleted IS FALSE)
  • Rows Removed by Filter: 1
129. 74.107 251.565 ↓ 2.0 147,654 1

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

  • Buckets: 2,048 Batches: 16 (originally 8) Memory Usage: 1,025kB
130. 177.458 177.458 ↓ 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.150..177.458 rows=147,654 loops=1)

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

SubPlan (for Hash Join)

132. 0.336 7.056 ↑ 1.0 1 84

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

133. 0.252 6.720 ↑ 1.0 1 84

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

134. 3.276 3.276 ↑ 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.038..0.039 rows=1 loops=84)

  • Index Cond: (physicalconfigid = physicalconfig_2.physicalconfigid)
135. 3.192 3.192 ↑ 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.037..0.038 rows=1 loops=84)

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

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

137. 0.612 14.289 ↓ 5.0 5 33

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

138. 0.363 11.187 ↓ 3.1 25 33

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

139. 0.099 3.729 ↑ 1.0 1 33

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

140. 0.132 2.838 ↑ 1.0 1 33

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

141. 1.749 1.749 ↑ 1.0 1 33

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

  • Index Cond: ((gsrn)::text = (deliverypoint_1.gsrn)::text)
142. 0.957 0.957 ↑ 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.028..0.029 rows=1 loops=33)

  • Index Cond: (deliverypointid = dp_w_1.deliverypointid)
143. 0.792 0.792 ↑ 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.024..0.024 rows=1 loops=33)

  • Index Cond: (deliveryperiodid = deliveryperiod_2.deliveryperiodid)
144. 7.095 7.095 ↓ 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.026..0.215 rows=25 loops=33)

  • Index Cond: (contractdeliveryid = cdp_3.contractdeliveryid)
145. 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
146. 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
147.          

SubPlan (for Nested Loop Anti Join)

148. 0.033 1,293.039 ↓ 0.0 0 33

Limit (cost=9.69..1,213.41 rows=1 width=4) (actual time=39.183..39.183 rows=0 loops=33)

149. 0.245 1,293.006 ↓ 0.0 0 33

Nested Loop (cost=9.69..1,213.41 rows=1 width=4) (actual time=39.182..39.182 rows=0 loops=33)

150. 0.624 1,279.575 ↓ 21.0 21 33

Nested Loop (cost=9.01..1,205.63 rows=1 width=4) (actual time=0.599..38.775 rows=21 loops=33)

151. 204.917 1,276.869 ↓ 3.5 21 33

Hash Join (cost=8.74..1,203.62 rows=6 width=8) (actual time=0.593..38.693 rows=21 loops=33)

  • Hash Cond: (cw_2.crmworkflowstatusparamid = pv_s_2.parametervalueid)
152. 1,071.840 1,071.840 ↓ 5.5 33,276 33

Index Scan using crmworkflow_effectivedate_idx on crmworkflow cw_2 (cost=0.43..1,172.57 rows=6,051 width=12) (actual time=0.029..32.480 rows=33,276 loops=33)

  • 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.005 0.112 ↑ 1.0 1 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 1kB
154. 0.107 0.107 ↑ 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.107..0.107 rows=1 loops=1)

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

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

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

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

  • 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: 719
157. 0.033 35.244 ↓ 0.0 0 33

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

158. 0.108 35.211 ↓ 0.0 0 33

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

159. 0.525 32.802 ↑ 1.0 1 33

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

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

Bitmap Heap Scan on index (cost=10.01..14.04 rows=1 width=11) (actual time=0.784..0.815 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
161. 0.231 5.841 ↓ 0.0 0 33

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

162. 2.937 2.937 ↑ 2.3 26 33

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

  • Index Cond: (deliverypointcomponentid = deliverypointcomponent_1.deliverypointcomponentid)
163. 2.673 2.673 ↑ 2.4 25 33

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

  • Index Cond: (deliverypointid = deliverypoint_1.deliverypointid)
164. 0.117 5.382 ↓ 0.0 0 39

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

165. 0.390 5.265 ↓ 0.0 0 39

Sort (cost=16.92..16.92 rows=1 width=156) (actual time=0.135..0.135 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
166. 0.198 4.875 ↑ 1.0 1 39

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

167. 1.443 1.443 ↑ 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.029..0.037 rows=1 loops=39)

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

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

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

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

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