explain.depesz.com

PostgreSQL's explain analyze made readable

Result: slj1

Settings
# exclusive inclusive rows x rows loops node
1. 0.007 13,622.070 ↑ 1.0 10 1

Limit (cost=78,106.01..592,128.66 rows=10 width=82) (actual time=3,798.975..13,622.070 rows=10 loops=1)

2. 0.106 13,622.063 ↑ 32.0 10 1

Nested Loop Anti Join (cost=78,106.01..16,526,830.71 rows=320 width=82) (actual time=3,798.974..13,622.063 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
3. 0.016 13,610.447 ↑ 32.0 10 1

Nested Loop Anti Join (cost=78,103.78..16,503,623.39 rows=320 width=82) (actual time=3,797.012..13,610.447 rows=10 loops=1)

  • Join Filter: (cuf.contractid = co.contractid)
4. 2,333.400 13,609.911 ↑ 32.0 10 1

Nested Loop (cost=78,103.35..16,503,610.14 rows=320 width=82) (actual time=3,796.498..13,609.911 rows=10 loops=1)

5. 320.317 11,254.143 ↓ 1.6 22,368 1

Hash Join (cost=77,606.16..2,266,374.78 rows=14,312 width=82) (actual time=1,572.354..11,254.143 rows=22,368 loops=1)

  • Hash Cond: ((SubPlan 2) = m.meterid)
6. 214.990 8,345.203 ↓ 2.4 111,430 1

Nested Loop Left Join (cost=52,984.67..298,488.04 rows=45,953 width=58) (actual time=987.816..8,345.203 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: 129,839
7. 298.790 7,127.343 ↓ 2.4 111,430 1

Nested Loop Left Join (cost=52,984.25..268,837.38 rows=45,953 width=54) (actual time=987.765..7,127.343 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: 182,468
  • 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,744
8. 103.603 4,969.769 ↓ 1.9 116,174 1

Nested Loop (cost=52,983.83..199,924.24 rows=62,577 width=54) (actual time=987.688..4,969.769 rows=116,174 loops=1)

9. 49.775 4,285.296 ↓ 1.9 116,174 1

Nested Loop (cost=52,983.41..167,057.43 rows=62,577 width=54) (actual time=987.632..4,285.296 rows=116,174 loops=1)

10. 80.628 3,538.477 ↓ 1.9 116,174 1

Nested Loop (cost=52,982.99..132,853.39 rows=62,577 width=35) (actual time=987.623..3,538.477 rows=116,174 loops=1)

11. 124.229 2,644.631 ↓ 1.9 116,174 1

Merge Join (cost=52,982.57..98,051.24 rows=62,577 width=31) (actual time=987.592..2,644.631 rows=116,174 loops=1)

  • Merge Cond: (co_1.contractid = co.contractid)
12. 309.481 2,028.565 ↓ 1.8 116,174 1

Merge Join (cost=52,982.15..81,151.12 rows=63,960 width=20) (actual time=987.561..2,028.565 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: 192,398
13. 94.537 1,280.766 ↓ 1.0 305,014 1

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

14. 459.445 1,186.229 ↓ 1.0 305,014 1

Sort (cost=52,981.73..53,744.12 rows=304,958 width=8) (actual time=987.483..1,186.229 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
15. 38.455 726.784 ↓ 1.0 305,014 1

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

16. 339.114 339.114 ↓ 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.050..339.114 rows=152,507 loops=1)

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

  • Filter: (supplierindexcollectiondate IS NOT NULL)
  • Rows Removed by Filter: 538
18. 78.663 438.318 ↓ 2.0 308,773 1

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

19. 359.655 359.655 ↓ 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.050..359.655 rows=154,488 loops=1)

20. 491.837 491.837 ↓ 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.026..491.837 rows=193,869 loops=1)

21. 813.218 813.218 ↑ 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.006..0.007 rows=1 loops=116,174)

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

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

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

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

  • Index Cond: (deliverypointcomponentid = deliveryperiod_1.deliverypointcomponentid)
  • Filter: (deleted IS FALSE)
24. 1,858.784 1,858.784 ↓ 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.008..0.016 rows=3 loops=116,174)

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

Index Scan using physicalconfigid on physicalconfig (cost=0.42..0.63 rows=1 width=20) (actual time=0.006..0.009 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
26. 45.144 581.638 ↓ 2.0 147,573 1

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

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

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

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

  • Filter: (deleted IS FALSE)
  • Rows Removed by Filter: 1
29. 77.686 244.724 ↓ 2.0 147,573 1

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

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

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

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

SubPlan (for Hash Join)

32. 133.799 2,006.985 ↑ 1.0 1 133,799

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

33. 133.811 1,873.186 ↑ 1.0 1 133,799

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

34. 936.593 936.593 ↑ 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.007..0.007 rows=1 loops=133,799)

  • Index Cond: (physicalconfigid = physicalconfig.physicalconfigid)
35. 802.782 802.782 ↑ 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.005..0.006 rows=1 loops=133,797)

  • Index Cond: (physicalconfigid = physicalconfig.physicalconfigid)
  • Filter: (deleted IS FALSE)
36. 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 3))
  • Filter: ((creationdate >= '2019-12-30'::date) AND (creationdate < '2020-01-13'::date))
  • Rows Removed by Filter: 0
37.          

SubPlan (for Index Scan)

38. 44.736 2,303.904 ↓ 0.0 0 22,368

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

39. 67.104 2,259.168 ↓ 0.0 0 22,368

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

  • Sort Key: cw_1.creationdate
  • Sort Method: top-N heapsort Memory: 25kB
40. 0.000 2,192.064 ↓ 0.0 0 22,368

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

41. 22.368 2,147.328 ↑ 1.0 1 22,368

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

  • Join Filter: (cw_1.crmworkflowtypeparamid = pv_t_1.parametervalueid)
  • Rows Removed by Join Filter: 0
42. 246.048 246.048 ↑ 1.0 1 22,368

Index Scan using parametervalue_valuecode_idx on parametervalue pv_t_1 (cost=0.28..8.29 rows=1 width=4) (actual time=0.010..0.011 rows=1 loops=22,368)

  • Index Cond: ((valuecode)::text = 'NL_indexCollectionKV'::text)
43. 88.794 1,878.912 ↑ 39.0 1 22,368

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

44. 1,476.288 1,476.288 ↑ 39.0 1 22,368

Index Scan using crmworkflowdata_key_value_idx on crmworkflowdata cwd (cost=0.69..157.82 rows=39 width=4) (actual time=0.061..0.066 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))))
45. 313.830 313.830 ↑ 1.0 1 20,922

Index Scan using crmworkflow_pkey on crmworkflow cw_1 (cost=0.43..8.45 rows=1 width=20) (actual time=0.014..0.015 rows=1 loops=20,922)

  • Index Cond: (crmworkflowid = cwd.crmworkflowid)
46. 44.952 44.952 ↓ 0.0 0 11,238

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

  • Index Cond: (parametervalueid = cw_1.crmworkflowstatusparamid)
  • Filter: ((internalparametervaluecode)::text = 'ERROR'::text)
  • Rows Removed by Filter: 1
47. 44.736 2,303.904 ↓ 0.0 0 22,368

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

48. 67.104 2,259.168 ↓ 0.0 0 22,368

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

  • Sort Key: cw_1.creationdate
  • Sort Method: top-N heapsort Memory: 25kB
49. 0.000 2,192.064 ↓ 0.0 0 22,368

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

50. 22.368 2,147.328 ↑ 1.0 1 22,368

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

  • Join Filter: (cw_1.crmworkflowtypeparamid = pv_t_1.parametervalueid)
  • Rows Removed by Join Filter: 0
51. 246.048 246.048 ↑ 1.0 1 22,368

Index Scan using parametervalue_valuecode_idx on parametervalue pv_t_1 (cost=0.28..8.29 rows=1 width=4) (actual time=0.010..0.011 rows=1 loops=22,368)

  • Index Cond: ((valuecode)::text = 'NL_indexCollectionKV'::text)
52. 88.794 1,878.912 ↑ 39.0 1 22,368

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

53. 1,476.288 1,476.288 ↑ 39.0 1 22,368

Index Scan using crmworkflowdata_key_value_idx on crmworkflowdata cwd (cost=0.69..157.82 rows=39 width=4) (actual time=0.061..0.066 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))))
54. 313.830 313.830 ↑ 1.0 1 20,922

Index Scan using crmworkflow_pkey on crmworkflow cw_1 (cost=0.43..8.45 rows=1 width=20) (actual time=0.014..0.015 rows=1 loops=20,922)

  • Index Cond: (crmworkflowid = cwd.crmworkflowid)
55. 44.952 44.952 ↓ 0.0 0 11,238

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

  • Index Cond: (parametervalueid = cw_1.crmworkflowstatusparamid)
  • Filter: ((internalparametervaluecode)::text = 'ERROR'::text)
  • Rows Removed by Filter: 1
56. 0.011 0.520 ↓ 0.0 0 10

Materialize (cost=0.43..8.45 rows=1 width=4) (actual time=0.052..0.052 rows=0 loops=10)

57. 0.509 0.509 ↓ 0.0 0 1

Index Scan using idx_contractuserfields_useritemcodeparamid on contractuserfields cuf (cost=0.43..8.45 rows=1 width=4) (actual time=0.509..0.509 rows=0 loops=1)

  • Index Cond: (useritemcodeparamid = 100,030,178)
58. 0.055 5.960 ↓ 2.0 2 10

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

59. 0.209 5.740 ↓ 6.0 6 10

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

60. 0.130 4.700 ↓ 3.5 28 10

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

61. 0.010 1.500 ↑ 1.0 1 10

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

62. 0.050 1.130 ↑ 1.0 1 10

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

63. 0.690 0.690 ↑ 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.069..0.069 rows=1 loops=10)

  • Index Cond: ((gsrn)::text = (deliverypoint.gsrn)::text)
64. 0.390 0.390 ↑ 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.038..0.039 rows=1 loops=10)

  • Index Cond: (deliverypointid = dp_w.deliverypointid)
65. 0.360 0.360 ↑ 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.035..0.036 rows=1 loops=10)

  • Index Cond: (deliveryperiodid = deliveryperiod.deliveryperiodid)
66. 3.070 3.070 ↓ 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.037..0.307 rows=28 loops=10)

  • Index Cond: (contractdeliveryid = cdp_1.contractdeliveryid)
67. 0.831 0.831 ↓ 0.0 0 277

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

  • Index Cond: (parametervalueid = cw.crmworkflowtypeparamid)
  • Filter: ((valuecode)::text = 'NL_indexCollectionKV'::text)
  • Rows Removed by Filter: 1
68. 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
69.          

SubPlan (for Nested Loop Anti Join)

70. 0.010 5.550 ↓ 0.0 0 10

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

71. 0.024 5.540 ↓ 0.0 0 10

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

72. 0.125 5.120 ↑ 1.0 1 10

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

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

Bitmap Heap Scan on index (cost=10.01..14.04 rows=1 width=11) (actual time=0.393..0.406 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
74. 0.070 0.930 ↓ 0.0 0 10

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

75. 0.440 0.440 ↑ 2.1 28 10

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

  • Index Cond: (deliverypointcomponentid = deliverypointcomponent.deliverypointcomponentid)
76. 0.420 0.420 ↑ 2.2 27 10

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

  • Index Cond: (deliverypointid = deliverypoint.deliverypointid)
77. 0.011 0.935 ↓ 0.0 0 11

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

78. 0.088 0.924 ↓ 0.0 0 11

Sort (cost=16.92..16.92 rows=1 width=156) (actual time=0.084..0.084 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
79. 0.044 0.836 ↑ 1.0 1 11

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

80. 0.396 0.396 ↑ 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.030..0.036 rows=1 loops=11)

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

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

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

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

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