explain.depesz.com

PostgreSQL's explain analyze made readable

Result: lFlI

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 22,302.293 ↑ 1.0 10 1

Limit (cost=78,114.31..624,766.73 rows=10 width=82) (actual time=6,791.732..22,302.293 rows=10 loops=1)

2.          

Initplan (for Limit)

3. 0.095 0.095 ↑ 1.0 1 1

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

  • Index Cond: ((valuecode)::text = 'NL.excludedFromConventionalMeteringRequest'::text)
  • Filter: ((parametercategorycode)::text = 'contractUserItemCode'::text)
4. 0.227 22,302.289 ↑ 17.7 10 1

Nested Loop Anti Join (cost=78,106.01..9,753,853.80 rows=177 width=82) (actual time=6,791.731..22,302.289 rows=10 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: 17
5. 3,478.476 21,554.402 ↑ 17.7 10 1

Nested Loop (cost=78,103.78..9,357,740.96 rows=177 width=82) (actual time=6,640.936..21,554.402 rows=10 loops=1)

6. 298.719 18,053.558 ↓ 2.8 22,368 1

Hash Join (cost=77,606.59..1,465,187.49 rows=7,934 width=82) (actual time=2,015.950..18,053.558 rows=22,368 loops=1)

  • Hash Cond: ((SubPlan 5) = m.meterid)
7. 108.324 14,280.662 ↓ 4.4 111,430 1

Nested Loop Anti Join (cost=52,985.10..363,093.47 rows=25,474 width=58) (actual time=1,197.282..14,280.662 rows=111,430 loops=1)

8. 201.747 10,829.438 ↓ 2.4 111,430 1

Nested Loop Left Join (cost=52,984.67..298,488.04 rows=45,953 width=58) (actual time=1,196.544..10,829.438 rows=111,430 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: 129839
9. 259.140 9,290.531 ↓ 2.4 111,430 1

Nested Loop Left Join (cost=52,984.25..268,837.38 rows=45,953 width=54) (actual time=1,196.203..9,290.531 rows=111,430 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: 182468
  • 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: 4744
10. 55.870 6,359.389 ↓ 1.9 116,174 1

Nested Loop (cost=52,983.83..199,924.24 rows=62,577 width=54) (actual time=1,195.888..6,359.389 rows=116,174 loops=1)

11. 50.122 5,490.301 ↓ 1.9 116,174 1

Nested Loop (cost=52,983.41..167,057.43 rows=62,577 width=54) (actual time=1,195.845..5,490.301 rows=116,174 loops=1)

12. 126.328 4,510.787 ↓ 1.9 116,174 1

Nested Loop (cost=52,982.99..132,853.39 rows=62,577 width=35) (actual time=1,195.802..4,510.787 rows=116,174 loops=1)

13. 140.103 3,338.893 ↓ 1.9 116,174 1

Merge Join (cost=52,982.57..98,051.24 rows=62,577 width=31) (actual time=1,195.750..3,338.893 rows=116,174 loops=1)

  • Merge Cond: (co_1.contractid = co.contractid)
14. 343.860 2,481.941 ↓ 1.8 116,174 1

Merge Join (cost=52,982.15..81,151.12 rows=63,960 width=20) (actual time=1,195.687..2,481.941 rows=116,174 loops=1)

  • Merge Cond: (co_1.contractid = cdp.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: 192398
15. 105.046 1,526.610 ↓ 1.0 305,014 1

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

16. 542.054 1,421.564 ↓ 1.0 305,014 1

Sort (cost=52,981.73..53,744.12 rows=304,958 width=8) (actual time=1,195.600..1,421.564 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: 5376kB
17. 41.692 879.510 ↓ 1.0 305,014 1

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

18. 433.215 433.215 ↓ 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.049..433.215 rows=152,507 loops=1)

  • Filter: (supplierindexcollectiondate IS NOT NULL)
  • Rows Removed by Filter: 538
19. 404.603 404.603 ↓ 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.028..404.603 rows=152,507 loops=1)

  • Filter: (supplierindexcollectiondate IS NOT NULL)
  • Rows Removed by Filter: 538
20. 87.302 611.471 ↓ 2.0 308,773 1

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

21. 524.169 524.169 ↓ 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.054..524.169 rows=154,488 loops=1)

22. 716.849 716.849 ↓ 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.047..716.849 rows=193,869 loops=1)

23. 1,045.566 1,045.566 ↑ 1.0 1 116,174

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

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

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

  • Index Cond: (deliverypointid = deliveryperiod_1.deliverypointid)
  • Filter: (deleted IS FALSE)
25. 813.218 813.218 ↑ 1.0 1 116,174

Index Scan using deliverypointcomponent_pk on deliverypointcomponent (cost=0.42..0.52 rows=1 width=4) (actual time=0.006..0.007 rows=1 loops=116,174)

  • Index Cond: (deliverypointcomponentid = deliveryperiod_1.deliverypointcomponentid)
  • Filter: (deleted IS FALSE)
26. 2,672.002 2,672.002 ↓ 1.5 3 116,174

Index Scan using generalconfig_deliverypointcomponentid_idx on generalconfig (cost=0.42..1.05 rows=2 width=42) (actual time=0.011..0.023 rows=3 loops=116,174)

  • Index Cond: (deliverypointcomponentid = deliverypointcomponent.deliverypointcomponentid)
  • Filter: ((deleted IS FALSE) AND ((sourceid)::text = 'DGO'::text))
  • Rows Removed by Filter: 1
27. 1,337.160 1,337.160 ↓ 2.0 2 111,430

Index Scan using physicalconfigid on physicalconfig (cost=0.42..0.63 rows=1 width=20) (actual time=0.007..0.012 rows=2 loops=111,430)

  • Index Cond: ((deliverypointid = deliverypoint.deliverypointid) AND (deliverypointcomponentid = deliverypointcomponent.deliverypointcomponentid))
  • Filter: ((deleted IS FALSE) AND ((sourceid)::text = 'DGO'::text))
  • Rows Removed by Filter: 0
28. 3,342.900 3,342.900 ↓ 0.0 0 111,430

Index Scan using idx_contractuserfields_contractid_pk on contractuserfields cuf (cost=0.43..1.40 rows=1 width=4) (actual time=0.030..0.030 rows=0 loops=111,430)

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

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

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

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

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

  • Filter: (deleted IS FALSE)
  • Rows Removed by Filter: 1
32. 81.907 294.785 ↓ 2.0 147,573 1

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

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

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

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

SubPlan (for Hash Join)

35. 267.598 2,675.980 ↑ 1.0 1 133,799

Aggregate (cost=16.89..16.90 rows=1 width=4) (actual time=0.019..0.020 rows=1 loops=133,799)

36. 133.813 2,408.382 ↑ 1.0 1 133,799

Nested Loop (cost=0.84..16.89 rows=1 width=4) (actual time=0.017..0.018 rows=1 loops=133,799)

37. 1,337.990 1,337.990 ↑ 1.0 1 133,799

Index Scan using meter_physicalconfigid_idx on meter m_1 (cost=0.42..8.44 rows=1 width=8) (actual time=0.009..0.010 rows=1 loops=133,799)

  • Index Cond: (physicalconfigid = physicalconfig.physicalconfigid)
38. 936.579 936.579 ↑ 1.0 1 133,797

Index Scan using physicalconfig_pk on physicalconfig physicalconfig_2 (cost=0.42..8.44 rows=1 width=4) (actual time=0.007..0.007 rows=1 loops=133,797)

  • Index Cond: (physicalconfigid = physicalconfig.physicalconfigid)
  • Filter: (deleted IS FALSE)
39. 0.000 22.368 ↓ 0.0 0 22,368

Index Scan using crmworkflow_pkey on crmworkflow cw3 (cost=497.19..994.77 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=22,368)

  • Index Cond: (crmworkflowid = (SubPlan 7))
  • Filter: ((creationdate >= '2019-12-30'::date) AND (creationdate < '2020-01-13'::date))
  • Rows Removed by Filter: 0
40.          

SubPlan (for Index Scan)

41. 44.736 3,444.672 ↓ 0.0 0 22,368

Limit (cost=496.76..496.76 rows=1 width=12) (actual time=0.153..0.154 rows=0 loops=22,368)

42. 89.472 3,399.936 ↓ 0.0 0 22,368

Sort (cost=496.76..496.76 rows=1 width=12) (actual time=0.152..0.152 rows=0 loops=22,368)

  • Sort Key: cw_4.creationdate
  • Sort Method: top-N heapsort Memory: 25kB
43. 10.914 3,310.464 ↓ 0.0 0 22,368

Nested Loop (cost=1.67..496.75 rows=1 width=12) (actual time=0.142..0.148 rows=0 loops=22,368)

44. 22.368 3,243.360 ↑ 1.0 1 22,368

Nested Loop (cost=1.40..496.44 rows=1 width=16) (actual time=0.134..0.145 rows=1 loops=22,368)

  • Join Filter: (cw_4.crmworkflowtypeparamid = pv_t_4.parametervalueid)
  • Rows Removed by Join Filter: 0
45. 268.416 268.416 ↑ 1.0 1 22,368

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.012 rows=1 loops=22,368)

  • Index Cond: ((valuecode)::text = 'NL_indexCollectionKV'::text)
46. 146.544 2,952.576 ↑ 39.0 1 22,368

Nested Loop (cost=1.12..487.66 rows=39 width=20) (actual time=0.108..0.132 rows=1 loops=22,368)

47. 2,303.904 2,303.904 ↑ 39.0 1 22,368

Index Scan using crmworkflowdata_key_value_idx on crmworkflowdata cwd_3 (cost=0.69..157.82 rows=39 width=4) (actual time=0.089..0.103 rows=1 loops=22,368)

  • 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))))
48. 502.128 502.128 ↑ 1.0 1 20,922

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

  • Index Cond: (crmworkflowid = cwd_3.crmworkflowid)
49. 56.190 56.190 ↓ 0.0 0 11,238

Index Scan using parametervalueid on parametervalue pv_s_4 (cost=0.28..0.30 rows=1 width=4) (actual time=0.004..0.005 rows=0 loops=11,238)

  • Index Cond: (parametervalueid = cw_4.crmworkflowstatusparamid)
  • Filter: ((internalparametervaluecode)::text = 'ERROR'::text)
  • Rows Removed by Filter: 1
50. 44.736 3,444.672 ↓ 0.0 0 22,368

Limit (cost=496.76..496.76 rows=1 width=12) (actual time=0.153..0.154 rows=0 loops=22,368)

51. 89.472 3,399.936 ↓ 0.0 0 22,368

Sort (cost=496.76..496.76 rows=1 width=12) (actual time=0.152..0.152 rows=0 loops=22,368)

  • Sort Key: cw_4.creationdate
  • Sort Method: top-N heapsort Memory: 25kB
52. 10.914 3,310.464 ↓ 0.0 0 22,368

Nested Loop (cost=1.67..496.75 rows=1 width=12) (actual time=0.142..0.148 rows=0 loops=22,368)

53. 22.368 3,243.360 ↑ 1.0 1 22,368

Nested Loop (cost=1.40..496.44 rows=1 width=16) (actual time=0.134..0.145 rows=1 loops=22,368)

  • Join Filter: (cw_4.crmworkflowtypeparamid = pv_t_4.parametervalueid)
  • Rows Removed by Join Filter: 0
54. 268.416 268.416 ↑ 1.0 1 22,368

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.012 rows=1 loops=22,368)

  • Index Cond: ((valuecode)::text = 'NL_indexCollectionKV'::text)
55. 146.544 2,952.576 ↑ 39.0 1 22,368

Nested Loop (cost=1.12..487.66 rows=39 width=20) (actual time=0.108..0.132 rows=1 loops=22,368)

56. 2,303.904 2,303.904 ↑ 39.0 1 22,368

Index Scan using crmworkflowdata_key_value_idx on crmworkflowdata cwd_3 (cost=0.69..157.82 rows=39 width=4) (actual time=0.089..0.103 rows=1 loops=22,368)

  • 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))))
57. 502.128 502.128 ↑ 1.0 1 20,922

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

  • Index Cond: (crmworkflowid = cwd_3.crmworkflowid)
58. 56.190 56.190 ↓ 0.0 0 11,238

Index Scan using parametervalueid on parametervalue pv_s_4 (cost=0.28..0.30 rows=1 width=4) (actual time=0.004..0.005 rows=0 loops=11,238)

  • Index Cond: (parametervalueid = cw_4.crmworkflowstatusparamid)
  • Filter: ((internalparametervaluecode)::text = 'ERROR'::text)
  • Rows Removed by Filter: 1
59. 0.075 26.810 ↓ 2.0 2 10

Nested Loop (cost=2.24..6.01 rows=1 width=23) (actual time=1.654..2.681 rows=2 loops=10)

60. 0.132 26.570 ↓ 6.0 6 10

Nested Loop (cost=1.96..5.70 rows=1 width=27) (actual time=0.907..2.657 rows=6 loops=10)

61. 0.160 25.330 ↓ 3.5 28 10

Nested Loop (cost=1.69..3.24 rows=8 width=31) (actual time=0.882..2.533 rows=28 loops=10)

62. 0.040 4.830 ↑ 1.0 1 10

Nested Loop (cost=1.26..1.67 rows=1 width=23) (actual time=0.480..0.483 rows=1 loops=10)

63. 0.050 4.030 ↑ 1.0 1 10

Nested Loop (cost=0.84..1.11 rows=1 width=23) (actual time=0.402..0.403 rows=1 loops=10)

64. 2.740 2.740 ↑ 1.0 1 10

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

  • Index Cond: ((gsrn)::text = (deliverypoint.gsrn)::text)
65. 1.240 1.240 ↑ 1.0 1 10

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

  • Index Cond: (deliverypointid = dp_w.deliverypointid)
66. 0.760 0.760 ↑ 1.0 1 10

Index Scan using contractdelivery_deliveryperiodid_idx on contractdelivery cdp_1 (cost=0.42..0.55 rows=1 width=8) (actual time=0.075..0.076 rows=1 loops=10)

  • Index Cond: (deliveryperiodid = deliveryperiod.deliveryperiodid)
67. 20.340 20.340 ↓ 1.9 28 10

Index Scan using crmworkflow_contractdeliveryid_idx on crmworkflow cw (cost=0.43..1.42 rows=15 width=16) (actual time=0.399..2.034 rows=28 loops=10)

  • Index Cond: (contractdeliveryid = cdp_1.contractdeliveryid)
68. 1.108 1.108 ↓ 0.0 0 277

Index Scan using parametervalueid on parametervalue pv_t (cost=0.28..0.30 rows=1 width=4) (actual time=0.004..0.004 rows=0 loops=277)

  • Index Cond: (parametervalueid = cw.crmworkflowtypeparamid)
  • Filter: ((valuecode)::text = 'NL_indexCollectionKV'::text)
  • Rows Removed by Filter: 1
69. 0.165 0.165 ↓ 0.0 0 55

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

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

SubPlan (for Nested Loop Anti Join)

71. 0.040 1.330 ↑ 1.0 1 10

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

72. 0.070 1.290 ↓ 3.0 3 10

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

73. 0.030 1.130 ↓ 3.0 3 10

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

  • Join Filter: (cw_1.crmworkflowtypeparamid = pv_t_1.parametervalueid)
  • Rows Removed by Join Filter: 1
74. 0.150 0.150 ↑ 1.0 1 10

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

  • Index Cond: ((valuecode)::text = 'NL_indexCollectionKV'::text)
75. 0.166 0.950 ↑ 9.8 4 10

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

76. 0.550 0.550 ↑ 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.049..0.055 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))))
77. 0.234 0.234 ↑ 1.0 1 39

Index Scan using crmworkflow_pkey on crmworkflow cw_1 (cost=0.43..8.45 rows=1 width=12) (actual time=0.006..0.006 rows=1 loops=39)

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

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=1 loops=30)

  • Index Cond: (parametervalueid = cw_1.crmworkflowstatusparamid)
  • Filter: ((internalparametervaluecode)::text = 'ERROR'::text)
79. 0.020 685.400 ↓ 0.0 0 10

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

80. 0.055 685.380 ↓ 0.0 0 10

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

81. 0.152 679.250 ↓ 8.0 8 10

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

82. 64.790 677.640 ↓ 1.3 8 10

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

  • Hash Cond: (cw_2.crmworkflowstatusparamid = pv_s_2.parametervalueid)
83. 612.750 612.750 ↓ 6.0 36,500 10

Index Scan using crmworkflow_effectivedate_idx on crmworkflow cw_2 (cost=0.43..1,172.23 rows=6,049 width=12) (actual time=0.104..61.275 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)))
84. 0.002 0.100 ↑ 1.0 1 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
85. 0.098 0.098 ↑ 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.096..0.098 rows=1 loops=1)

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

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

  • Index Cond: (parametervalueid = cw_2.crmworkflowtypeparamid)
  • Filter: ((valuecode)::text = ANY ('{NL_indexCollectionKV,NL_VKOindex}'::text[]))
87. 6.075 6.075 ↓ 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.075..0.075 rows=0 loops=81)

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

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

89. 0.018 27.000 ↓ 0.0 0 10

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

90. 0.144 25.860 ↑ 1.0 1 10

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

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

Bitmap Heap Scan on index (cost=10.01..14.04 rows=1 width=11) (actual time=2.240..2.290 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: 26
  • Heap Blocks: exact=252
92. 0.070 4.470 ↓ 0.0 0 10

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

93. 2.240 2.240 ↑ 2.1 28 10

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

  • Index Cond: (deliverypointcomponentid = deliverypointcomponent.deliverypointcomponentid)
94. 2.160 2.160 ↑ 2.2 27 10

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

  • Index Cond: (deliverypointid = deliverypoint.deliverypointid)
95. 0.033 2.816 ↓ 0.0 0 11

Limit (cost=16.92..16.92 rows=1 width=156) (actual time=0.256..0.256 rows=0 loops=11)

96. 0.132 2.783 ↓ 0.0 0 11

Sort (cost=16.92..16.92 rows=1 width=156) (actual time=0.253..0.253 rows=0 loops=11)

  • 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
97. 0.036 2.651 ↑ 1.0 1 11

Nested Loop (cost=0.85..16.91 rows=1 width=156) (actual time=0.166..0.241 rows=1 loops=11)

98. 1.067 1.067 ↑ 1.0 1 11

Index Only Scan using indexrelation_pk on indexrelation ir (cost=0.42..8.44 rows=1 width=4) (actual time=0.078..0.097 rows=1 loops=11)

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

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

  • Index Cond: (indexid = ir.targetindexid)
  • Filter: ((sourceid)::text = 'SUPPLIER'::text)
100. 1.122 1.122 ↓ 0.0 0 11

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

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

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

102. 0.100 7.090 ↑ 1.0 1 10

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

  • Sort Key: cw_3.crmworkflowid
  • Sort Method: top-N heapsort Memory: 25kB
103. 0.050 6.990 ↓ 3.0 3 10

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

104. 0.038 6.850 ↓ 3.0 3 10

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

105. 0.073 6.680 ↑ 5.3 3 10

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

  • Join Filter: (cwd_2.crmworkflowid = cw_3.crmworkflowid)
106. 0.165 6.310 ↑ 5.3 3 10

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

107. 0.490 0.490 ↑ 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.043..0.049 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))))
108. 5.655 5.655 ↑ 1.0 1 39

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.143..0.145 rows=1 loops=39)

  • Index Cond: ((crmworkflowid = cwd_2.crmworkflowid) AND (key = 'ERROR_REASON'::text))
  • Heap Fetches: 33
109. 0.297 0.297 ↑ 1.0 1 33

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

  • Index Cond: (crmworkflowid = cwd_err.crmworkflowid)
110. 0.132 0.132 ↑ 1.0 1 33

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

  • Index Cond: (parametervalueid = cw_3.crmworkflowtypeparamid)
  • Filter: ((valuecode)::text = 'NL_indexCollectionKV'::text)
  • Rows Removed by Filter: 0
111. 0.090 0.090 ↑ 1.0 1 30

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

  • Index Cond: (parametervalueid = cw_3.crmworkflowstatusparamid)
  • Filter: ((internalparametervaluecode)::text = 'ERROR'::text)
Planning time : 40.531 ms
Execution time : 22,305.978 ms