explain.depesz.com

PostgreSQL's explain analyze made readable

Result: a0BA

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 38,424.070 ↑ 1.0 10 1

Limit (cost=117,528.17..4,049,047.85 rows=10 width=82) (actual time=34,538.773..38,424.070 rows=10 loops=1)

2.          

Initplan (for Limit)

3. 0.067 0.067 ↑ 1.0 1 1

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

  • Index Cond: ((valuecode)::text = 'NL.excludedFromConventionalMeteringRequest'::text)
  • Filter: ((parametercategorycode)::text = 'contractUserItemCode'::text)
4. 32.209 38,424.066 ↑ 8.9 10 1

Hash Join (cost=117,519.88..35,108,044.96 rows=89 width=82) (actual time=34,538.771..38,424.066 rows=10 loops=1)

  • Hash Cond: ((SubPlan 7) = cw3.crmworkflowid)
5. 332.014 34,330.460 ↓ 6.3 25,139 1

Hash Join (cost=77,606.59..31,913,267.30 rows=3,967 width=82) (actual time=1,739.684..34,330.460 rows=25,139 loops=1)

  • Hash Cond: ((SubPlan 5) = m.meterid)
6. 55.742 30,478.340 ↓ 5.8 73,981 1

Nested Loop Anti Join (cost=52,985.10..31,349,657.05 rows=12,737 width=58) (actual time=1,179.194..30,478.340 rows=73,981 loops=1)

7. 178.165 26,871.510 ↓ 3.2 73,981 1

Nested Loop Left Join (cost=52,984.67..31,317,355.04 rows=22,976 width=58) (actual time=1,178.892..26,871.510 rows=73,981 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: 87,232
8. 274.297 25,287.706 ↓ 3.2 73,981 1

Nested Loop Left Join (cost=52,984.25..31,302,530.03 rows=22,976 width=54) (actual time=1,178.758..25,287.706 rows=73,981 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: 127,394
  • 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: 4,743
9. 57.343 22,258.069 ↓ 2.5 78,724 1

Nested Loop (cost=52,983.83..31,268,074.01 rows=31,288 width=54) (actual time=1,178.588..22,258.069 rows=78,724 loops=1)

10. 198.038 21,177.314 ↓ 2.5 78,724 1

Nested Loop (cost=52,983.41..31,251,640.87 rows=31,288 width=54) (actual time=1,178.533..21,177.314 rows=78,724 loops=1)

  • Join Filter: (NOT (SubPlan 8))
  • Rows Removed by Join Filter: 37,450
11. 137.042 5,876.656 ↓ 1.9 116,174 1

Nested Loop (cost=52,982.99..132,473.57 rows=62,577 width=35) (actual time=1,177.719..5,876.656 rows=116,174 loops=1)

12. 166.001 3,997.004 ↓ 1.9 116,174 1

Merge Join (cost=52,982.57..97,671.42 rows=62,577 width=31) (actual time=1,177.623..3,997.004 rows=116,174 loops=1)

  • Merge Cond: (co_1.contractid = co.contractid)
13. 326.494 2,671.161 ↓ 1.8 116,174 1

Merge Join (cost=52,982.15..80,771.30 rows=63,960 width=20) (actual time=1,177.520..2,671.161 rows=116,174 loops=1)

  • Merge Cond: (cdp.contractid = co_1.contractid)
  • Join Filter: ((cdp.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 ((cdp.todate IS NULL) OR (cdp.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 (cdp.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 ((cdp.todate IS NULL) OR (cdp.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: 192,398
14. 723.104 723.104 ↓ 1.0 154,488 1

Index Scan using contractdelively_contract_id_idx on contractdelivery cdp (cost=0.42..14,277.26 rows=153,030 width=20) (actual time=0.016..723.104 rows=154,488 loops=1)

15. 89.241 1,621.563 ↓ 1.0 308,572 1

Materialize (cost=52,981.73..59,080.89 rows=304,958 width=8) (actual time=1,177.477..1,621.563 rows=308,572 loops=1)

16. 109.665 1,532.322 ↓ 1.0 305,014 1

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

17. 531.075 1,422.657 ↓ 1.0 305,014 1

Sort (cost=52,981.73..53,744.12 rows=304,958 width=8) (actual time=1,177.450..1,422.657 rows=305,014 loops=1)

  • Sort 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))
  • Sort Method: external merge Disk: 5,376kB
18. 41.433 891.582 ↓ 1.0 305,014 1

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

19. 465.859 465.859 ↓ 1.0 152,507 1

Seq Scan on contract co_1 (cost=0.00..8,990.57 rows=152,479 width=8) (actual time=0.108..465.859 rows=152,507 loops=1)

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

Seq Scan on contract co_2 (cost=0.00..8,990.57 rows=152,479 width=8) (actual time=0.052..384.290 rows=152,507 loops=1)

  • Filter: (supplierindexcollectiondate IS NOT NULL)
  • Rows Removed by Filter: 538
21. 1,159.842 1,159.842 ↓ 1.3 193,869 1

Index Scan using contract_pkey on contract co (cost=0.42..15,718.07 rows=153,020 width=19) (actual time=0.095..1,159.842 rows=193,869 loops=1)

22. 1,742.610 1,742.610 ↑ 1.0 1 116,174

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

  • Index Cond: (deliveryperiodid = cdp.deliveryperiodid)
  • Filter: (deleted IS FALSE)
23. 1,277.914 1,277.914 ↑ 1.0 1 116,174

Index Scan using deliverypoint_pkey on deliverypoint (cost=0.42..0.54 rows=1 width=23) (actual time=0.010..0.011 rows=1 loops=116,174)

  • Index Cond: (deliverypointid = deliveryperiod.deliverypointid)
  • Filter: (deleted IS FALSE)
24.          

SubPlan (for Nested Loop)

25. 83.214 13,824.706 ↓ 0.0 0 116,174

Nested Loop (cost=1.67..496.75 rows=1 width=0) (actual time=0.119..0.119 rows=0 loops=116,174)

26. 116.174 13,243.836 ↑ 1.0 1 116,174

Nested Loop (cost=1.40..496.44 rows=1 width=4) (actual time=0.111..0.114 rows=1 loops=116,174)

  • Join Filter: (cw_4.crmworkflowtypeparamid = pv_t_4.parametervalueid)
  • Rows Removed by Join Filter: 0
27. 1,510.262 1,510.262 ↑ 1.0 1 116,174

Index Scan using parametervalue_valuecode_idx on parametervalue pv_t_4 (cost=0.28..8.29 rows=1 width=4) (actual time=0.012..0.013 rows=1 loops=116,174)

  • Index Cond: ((valuecode)::text = 'NL_indexCollectionKV'::text)
28. 573.202 11,617.400 ↑ 39.0 1 116,174

Nested Loop (cost=1.12..487.66 rows=39 width=8) (actual time=0.092..0.100 rows=1 loops=116,174)

29. 8,945.398 8,945.398 ↑ 39.0 1 116,174

Index Scan using crmworkflowdata_key_value_idx on crmworkflowdata cwd_4 (cost=0.69..157.82 rows=39 width=4) (actual time=0.073..0.077 rows=1 loops=116,174)

  • Index Cond: (((key)::text = 'BUSINESS_KEY'::text) AND (value = (((deliverypoint.gsrn)::text || '_'::text) || to_char(((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)))::timestamp with time zone, 'YYYYMMDD'::text))))
30. 2,098.800 2,098.800 ↑ 1.0 1 87,450

Index Scan using crmworkflow_pkey on crmworkflow cw_4 (cost=0.43..8.45 rows=1 width=12) (actual time=0.023..0.024 rows=1 loops=87,450)

  • Index Cond: (crmworkflowid = cwd_4.crmworkflowid)
31. 497.656 497.656 ↑ 1.0 1 62,207

Index Scan using parametervalueid on parametervalue pv_s_4 (cost=0.28..0.30 rows=1 width=4) (actual time=0.008..0.008 rows=1 loops=62,207)

  • Index Cond: (parametervalueid = cw_4.crmworkflowstatusparamid)
  • Filter: ((internalparametervaluecode)::text = ANY ('{RUNNING,SUCCESS}'::text[]))
  • Rows Removed by Filter: 0
32. 1,023.412 1,023.412 ↑ 1.0 1 78,724

Index Scan using deliverypointcomponent_pk on deliverypointcomponent (cost=0.42..0.52 rows=1 width=4) (actual time=0.012..0.013 rows=1 loops=78,724)

  • Index Cond: (deliverypointcomponentid = deliveryperiod.deliverypointcomponentid)
  • Filter: (deleted IS FALSE)
33. 2,755.340 2,755.340 ↓ 1.5 3 78,724

Index Scan using generalconfig_deliverypointcomponentid_idx on generalconfig (cost=0.42..1.05 rows=2 width=42) (actual time=0.018..0.035 rows=3 loops=78,724)

  • Index Cond: (deliverypointcomponentid = deliverypointcomponent.deliverypointcomponentid)
  • Filter: ((deleted IS FALSE) AND ((sourceid)::text = 'DGO'::text))
  • Rows Removed by Filter: 1
34. 1,405.639 1,405.639 ↓ 2.0 2 73,981

Index Scan using physicalconfigid on physicalconfig (cost=0.42..0.63 rows=1 width=20) (actual time=0.011..0.019 rows=2 loops=73,981)

  • Index Cond: ((deliverypointid = deliverypoint.deliverypointid) AND (deliverypointcomponentid = deliverypointcomponent.deliverypointcomponentid))
  • Filter: ((deleted IS FALSE) AND ((sourceid)::text = 'DGO'::text))
  • Rows Removed by Filter: 0
35. 3,551.088 3,551.088 ↓ 0.0 0 73,981

Index Scan using idx_contractuserfields_contractid_pk on contractuserfields cuf (cost=0.43..1.40 rows=1 width=4) (actual time=0.048..0.048 rows=0 loops=73,981)

  • Index Cond: (contractid = co.contractid)
  • Filter: (useritemcodeparamid = $21)
  • Rows Removed by Filter: 13
36. 51.218 546.476 ↓ 2.0 147,573 1

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

  • Buckets: 2,048 Batches: 16 (originally 8) Memory Usage: 1,025kB
37. 194.106 495.258 ↓ 2.0 147,573 1

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

  • Hash Cond: (physicalconfig_1.physicalconfigid = m.physicalconfigid)
38. 119.725 119.725 ↑ 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.006..119.725 rows=259,073 loops=1)

  • Filter: (deleted IS FALSE)
  • Rows Removed by Filter: 1
39. 62.271 181.427 ↓ 2.0 147,573 1

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

  • Buckets: 2,048 Batches: 16 (originally 8) Memory Usage: 1,025kB
40. 119.156 119.156 ↓ 2.0 147,573 1

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

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

SubPlan (for Hash Join)

42. 198.242 2,973.630 ↑ 1.0 1 99,121

Aggregate (cost=16.89..16.90 rows=1 width=4) (actual time=0.030..0.030 rows=1 loops=99,121)

43. 99.143 2,775.388 ↑ 1.0 1 99,121

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

44. 1,585.936 1,585.936 ↑ 1.0 1 99,121

Index Scan using meter_physicalconfigid_idx on meter m_1 (cost=0.42..8.44 rows=1 width=8) (actual time=0.015..0.016 rows=1 loops=99,121)

  • Index Cond: (physicalconfigid = physicalconfig.physicalconfigid)
45. 1,090.309 1,090.309 ↑ 1.0 1 99,119

Index Scan using physicalconfig_pk on physicalconfig physicalconfig_2 (cost=0.42..8.44 rows=1 width=4) (actual time=0.011..0.011 rows=1 loops=99,119)

  • Index Cond: (physicalconfigid = physicalconfig.physicalconfigid)
  • Filter: (deleted IS FALSE)
46. 12.651 480.560 ↓ 1.6 43,937 1

Hash (cost=39,469.60..39,469.60 rows=27,015 width=4) (actual time=480.560..480.560 rows=43,937 loops=1)

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

Seq Scan on crmworkflow cw3 (cost=0.00..39,469.60 rows=27,015 width=4) (actual time=6.057..467.909 rows=43,937 loops=1)

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

SubPlan (for Hash Join)

49. 0.040 0.970 ↑ 1.0 1 10

Aggregate (cost=496.75..496.76 rows=1 width=0) (actual time=0.096..0.097 rows=1 loops=10)

50. 0.060 0.930 ↓ 3.0 3 10

Nested Loop (cost=1.67..496.75 rows=1 width=0) (actual time=0.070..0.093 rows=3 loops=10)

51. 0.030 0.780 ↓ 3.0 3 10

Nested Loop (cost=1.40..496.44 rows=1 width=4) (actual time=0.063..0.078 rows=3 loops=10)

  • Join Filter: (cw.crmworkflowtypeparamid = pv_t.parametervalueid)
  • Rows Removed by Join Filter: 1
52. 0.100 0.100 ↑ 1.0 1 10

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

  • Index Cond: ((valuecode)::text = 'NL_indexCollectionKV'::text)
53. 0.078 0.650 ↑ 9.8 4 10

Nested Loop (cost=1.12..487.66 rows=39 width=8) (actual time=0.051..0.065 rows=4 loops=10)

54. 0.420 0.420 ↑ 9.8 4 10

Index Scan using crmworkflowdata_key_value_idx on crmworkflowdata cwd (cost=0.69..157.82 rows=39 width=4) (actual time=0.040..0.042 rows=4 loops=10)

  • Index Cond: (((key)::text = 'BUSINESS_KEY'::text) AND (value = (((deliverypoint.gsrn)::text || '_'::text) || to_char(((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)))::timestamp with time zone, 'YYYYMMDD'::text))))
55. 0.152 0.152 ↑ 1.0 1 38

Index Scan using crmworkflow_pkey on crmworkflow cw (cost=0.43..8.45 rows=1 width=12) (actual time=0.004..0.004 rows=1 loops=38)

  • Index Cond: (crmworkflowid = cwd.crmworkflowid)
56. 0.090 0.090 ↑ 1.0 1 30

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

  • Index Cond: (parametervalueid = cw.crmworkflowstatusparamid)
  • Filter: ((internalparametervaluecode)::text = 'ERROR'::text)
57. 0.010 693.600 ↓ 0.0 0 10

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

58. 0.090 693.590 ↓ 0.0 0 10

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

59. 0.135 687.830 ↓ 8.0 8 10

Nested Loop (cost=9.01..1,205.28 rows=1 width=4) (actual time=1.345..68.783 rows=8 loops=10)

60. 63.549 687.290 ↓ 1.3 8 10

Hash Join (cost=8.74..1,203.28 rows=6 width=8) (actual time=1.337..68.729 rows=8 loops=10)

  • Hash Cond: (cw_1.crmworkflowstatusparamid = pv_s_1.parametervalueid)
61. 623.670 623.670 ↓ 6.0 36,500 10

Index Scan using crmworkflow_effectivedate_idx on crmworkflow cw_1 (cost=0.43..1,172.23 rows=6,049 width=12) (actual time=0.084..62.367 rows=36,500 loops=10)

  • 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)))
62. 0.002 0.071 ↑ 1.0 1 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 1kB
63. 0.069 0.069 ↑ 1.0 1 1

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

  • Index Cond: ((internalparametervaluecode)::text = 'RUNNING'::text)
64. 0.405 0.405 ↑ 1.0 1 81

Index Scan using parametervalueid on parametervalue pv_t_1 (cost=0.28..0.32 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=81)

  • Index Cond: (parametervalueid = cw_1.crmworkflowtypeparamid)
  • Filter: ((valuecode)::text = ANY ('{NL_indexCollectionKV,NL_VKOindex}'::text[]))
65. 5.670 5.670 ↓ 0.0 0 81

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.070..0.070 rows=0 loops=81)

  • Index Cond: ((crmworkflowid = cw_1.crmworkflowid) AND (key = 'BUSINESS_KEY'::text))
  • Filter: (value ~~ ((deliverypoint.gsrn)::text || '_%'::text))
  • Rows Removed by Filter: 1
  • Heap Fetches: 81
66. 0.020 34.950 ↓ 0.0 0 10

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

67. 0.020 34.930 ↓ 0.0 0 10

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

68. 0.159 33.740 ↑ 1.0 1 10

Nested Loop Left Join (cost=26.93..30.99 rows=1 width=4) (actual time=3.300..3.374 rows=1 loops=10)

  • Filter: ((((index.sourceid)::text = 'USER'::text) AND ((i2.marketstatus)::text = ANY ('{SUBMITTED,ACCEPTED}'::text[]))) OR ((index.sourceid)::text <> 'USER'::text))
69. 24.140 29.900 ↑ 1.0 1 10

Bitmap Heap Scan on index (cost=10.01..14.04 rows=1 width=11) (actual time=2.927..2.990 rows=1 loops=10)

  • 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: 24
  • Heap Blocks: exact=241
70. 0.080 5.760 ↓ 0.0 0 10

BitmapAnd (cost=10.01..10.01 rows=1 width=0) (actual time=0.576..0.576 rows=0 loops=10)

71. 2.980 2.980 ↑ 2.2 27 10

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

  • Index Cond: (deliverypointcomponentid = deliverypointcomponent.deliverypointcomponentid)
72. 2.700 2.700 ↑ 2.3 26 10

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

  • Index Cond: (deliverypointid = deliverypoint.deliverypointid)
73. 0.027 3.681 ↓ 0.0 0 9

Limit (cost=16.92..16.92 rows=1 width=156) (actual time=0.409..0.409 rows=0 loops=9)

74. 0.108 3.654 ↓ 0.0 0 9

Sort (cost=16.92..16.92 rows=1 width=156) (actual time=0.406..0.406 rows=0 loops=9)

  • 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
75. 0.063 3.546 ↑ 1.0 1 9

Nested Loop (cost=0.85..16.91 rows=1 width=156) (actual time=0.295..0.394 rows=1 loops=9)

76. 1.323 1.323 ↑ 1.0 1 9

Index Only Scan using indexrelation_pk on indexrelation ir (cost=0.42..8.44 rows=1 width=4) (actual time=0.126..0.147 rows=1 loops=9)

  • Index Cond: (sourceindexid = index.indexid)
  • Heap Fetches: 12
77. 2.160 2.160 ↑ 1.0 1 12

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

  • Index Cond: (indexid = ir.targetindexid)
  • Filter: ((sourceid)::text = 'SUPPLIER'::text)
78. 1.170 1.170 ↓ 0.0 0 9

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

  • Index Cond: (endindexid = index.indexid)
  • Filter: ((deleted IS FALSE) AND ((sourceid)::text = 'DGO'::text))
  • Rows Removed by Filter: 0
79. 0.020 9.480 ↑ 1.0 1 10

Limit (cost=455.57..455.57 rows=1 width=41) (actual time=0.948..0.948 rows=1 loops=10)

80. 0.140 9.460 ↑ 1.0 1 10

Sort (cost=455.57..455.57 rows=1 width=41) (actual time=0.946..0.946 rows=1 loops=10)

  • Sort Key: cw_2.crmworkflowid
  • Sort Method: top-N heapsort Memory: 25kB
81. 0.050 9.320 ↓ 3.0 3 10

Nested Loop (cost=2.35..455.56 rows=1 width=41) (actual time=0.231..0.932 rows=3 loops=10)

82. 0.066 9.150 ↓ 3.0 3 10

Nested Loop (cost=2.08..455.25 rows=1 width=45) (actual time=0.225..0.915 rows=3 loops=10)

83. 0.084 8.880 ↑ 5.3 3 10

Nested Loop (cost=1.80..450.33 rows=16 width=49) (actual time=0.214..0.888 rows=3 loops=10)

  • Join Filter: (cwd_2.crmworkflowid = cw_2.crmworkflowid)
84. 0.212 8.490 ↑ 5.3 3 10

Nested Loop (cost=1.38..433.70 rows=16 width=45) (actual time=0.199..0.849 rows=3 loops=10)

85. 0.640 0.640 ↑ 9.8 4 10

Index Scan using crmworkflowdata_key_value_idx on crmworkflowdata cwd_2 (cost=0.69..157.82 rows=39 width=4) (actual time=0.058..0.064 rows=4 loops=10)

  • Index Cond: (((key)::text = 'BUSINESS_KEY'::text) AND (value = (((deliverypoint.gsrn)::text || '_'::text) || to_char(((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)))::timestamp with time zone, 'YYYYMMDD'::text))))
86. 7.638 7.638 ↑ 1.0 1 38

Index Only Scan using crmworkflowdata_crmworkflowid_key_value_idx on crmworkflowdata cwd_err (cost=0.68..7.06 rows=1 width=41) (actual time=0.200..0.201 rows=1 loops=38)

  • Index Cond: ((crmworkflowid = cwd_2.crmworkflowid) AND (key = 'ERROR_REASON'::text))
  • Heap Fetches: 34
87. 0.306 0.306 ↑ 1.0 1 34

Index Scan using crmworkflow_pkey on crmworkflow cw_2 (cost=0.43..1.03 rows=1 width=12) (actual time=0.009..0.009 rows=1 loops=34)

  • Index Cond: (crmworkflowid = cwd_err.crmworkflowid)
88. 0.204 0.204 ↑ 1.0 1 34

Index Scan using parametervalueid on parametervalue pv_t_2 (cost=0.28..0.30 rows=1 width=4) (actual time=0.006..0.006 rows=1 loops=34)

  • Index Cond: (parametervalueid = cw_2.crmworkflowtypeparamid)
  • Filter: ((valuecode)::text = 'NL_indexCollectionKV'::text)
  • Rows Removed by Filter: 0
89. 0.120 0.120 ↑ 1.0 1 30

Index Scan using parametervalueid on parametervalue pv_s_2 (cost=0.28..0.30 rows=1 width=4) (actual time=0.003..0.004 rows=1 loops=30)

  • Index Cond: (parametervalueid = cw_2.crmworkflowstatusparamid)
  • Filter: ((internalparametervaluecode)::text = 'ERROR'::text)
90. 50.298 2,841.837 ↓ 0.0 0 25,149

Limit (cost=496.76..496.76 rows=1 width=12) (actual time=0.113..0.113 rows=0 loops=25,149)

91. 75.447 2,791.539 ↓ 0.0 0 25,149

Sort (cost=496.76..496.76 rows=1 width=12) (actual time=0.111..0.111 rows=0 loops=25,149)

  • Sort Key: cw_3.creationdate
  • Sort Method: top-N heapsort Memory: 25kB
92. 12.193 2,716.092 ↓ 0.0 0 25,149

Nested Loop (cost=1.67..496.75 rows=1 width=12) (actual time=0.099..0.108 rows=0 loops=25,149)

93. 25.149 2,665.794 ↓ 0.0 0 25,149

Nested Loop (cost=1.40..496.44 rows=1 width=16) (actual time=0.097..0.106 rows=0 loops=25,149)

  • Join Filter: (cw_3.crmworkflowtypeparamid = pv_t_3.parametervalueid)
  • Rows Removed by Join Filter: 0
94. 276.639 276.639 ↑ 1.0 1 25,149

Index Scan using parametervalue_valuecode_idx on parametervalue pv_t_3 (cost=0.28..8.29 rows=1 width=4) (actual time=0.011..0.011 rows=1 loops=25,149)

  • Index Cond: ((valuecode)::text = 'NL_indexCollectionKV'::text)
95. 117.903 2,364.006 ↑ 39.0 1 25,149

Nested Loop (cost=1.12..487.66 rows=39 width=20) (actual time=0.079..0.094 rows=1 loops=25,149)

96. 1,886.175 1,886.175 ↑ 39.0 1 25,149

Index Scan using crmworkflowdata_key_value_idx on crmworkflowdata cwd_3 (cost=0.69..157.82 rows=39 width=4) (actual time=0.067..0.075 rows=1 loops=25,149)

  • Index Cond: (((key)::text = 'BUSINESS_KEY'::text) AND (value = (((deliverypoint.gsrn)::text || '_'::text) || to_char(((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)))::timestamp with time zone, 'YYYYMMDD'::text))))
97. 359.928 359.928 ↑ 1.0 1 14,997

Index Scan using crmworkflow_pkey on crmworkflow cw_3 (cost=0.43..8.45 rows=1 width=20) (actual time=0.022..0.024 rows=1 loops=14,997)

  • Index Cond: (crmworkflowid = cwd_3.crmworkflowid)
98. 38.105 38.105 ↑ 1.0 1 7,621

Index Scan using parametervalueid on parametervalue pv_s_3 (cost=0.28..0.30 rows=1 width=4) (actual time=0.004..0.005 rows=1 loops=7,621)

  • Index Cond: (parametervalueid = cw_3.crmworkflowstatusparamid)
  • Filter: ((internalparametervaluecode)::text = 'ERROR'::text)
  • Rows Removed by Filter: 0
Planning time : 90.504 ms
Execution time : 38,428.443 ms