explain.depesz.com

PostgreSQL's explain analyze made readable

Result: fL0Y

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 20,882.508 ↑ 1.0 10 1

Limit (cost=78,114.31..603,112.79 rows=10 width=82) (actual time=6,504.861..20,882.508 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.094..0.095 rows=1 loops=1)

  • Index Cond: ((valuecode)::text = 'NL.excludedFromConventionalMeteringRequest'::text)
  • Filter: ((parametercategorycode)::text = 'contractUserItemCode'::text)
4. 0.131 20,882.497 ↑ 17.7 10 1

Nested Loop Anti Join (cost=78,106.01..9,370,579.19 rows=177 width=82) (actual time=6,504.860..20,882.497 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,538.458 20,816.526 ↑ 17.7 10 1

Nested Loop (cost=78,103.78..9,357,740.96 rows=177 width=82) (actual time=6,490.754..20,816.526 rows=10 loops=1)

6. 420.973 17,255.700 ↓ 2.8 22,368 1

Hash Join (cost=77,606.59..1,465,187.49 rows=7,934 width=82) (actual time=1,879.762..17,255.700 rows=22,368 loops=1)

  • Hash Cond: ((SubPlan 2) = m.meterid)
7. 71.771 13,734.318 ↓ 4.4 111,430 1

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

8. 240.004 10,431.077 ↓ 2.4 111,430 1

Nested Loop Left Join (cost=52,984.67..298,488.04 rows=45,953 width=58) (actual time=1,169.470..10,431.077 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. 271.325 8,965.343 ↓ 2.4 111,430 1

Nested Loop Left Join (cost=52,984.25..268,837.38 rows=45,953 width=54) (actual time=1,169.112..8,965.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: 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. 137.107 6,138.190 ↓ 1.9 116,174 1

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

11. 108.068 5,304.039 ↓ 1.9 116,174 1

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

12. 71.471 4,382.753 ↓ 1.9 116,174 1

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

13. 155.396 3,265.716 ↓ 1.9 116,174 1

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

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

Merge Join (cost=52,982.15..81,151.12 rows=63,960 width=20) (actual time=1,168.444..2,432.839 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. 104.192 1,497.796 ↓ 1.0 305,014 1

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

16. 537.440 1,393.604 ↓ 1.0 305,014 1

Sort (cost=52,981.73..53,744.12 rows=304,958 width=8) (actual time=1,168.363..1,393.604 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. 42.945 856.164 ↓ 1.0 305,014 1

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

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

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

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

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

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

22. 677.481 677.481 ↓ 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.045..677.481 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.008..0.009 rows=1 loops=116,174)

  • Index Cond: (deliveryperiodid = cdp.deliveryperiodid)
  • Filter: (deleted IS FALSE)
24. 813.218 813.218 ↑ 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.007 rows=1 loops=116,174)

  • Index Cond: (deliverypointid = deliveryperiod_1.deliverypointid)
  • Filter: (deleted IS FALSE)
25. 697.044 697.044 ↑ 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.006 rows=1 loops=116,174)

  • Index Cond: (deliverypointcomponentid = deliveryperiod_1.deliverypointcomponentid)
  • Filter: (deleted IS FALSE)
26. 2,555.828 2,555.828 ↓ 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.022 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,225.730 1,225.730 ↓ 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.011 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,231.470 3,231.470 ↓ 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.029..0.029 rows=0 loops=111,430)

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

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

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

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

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

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

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

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

Seq Scan on meter m (cost=0.00..8,052.08 rows=73,834 width=36) (actual time=0.004..144.189 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. 133.799 2,408.382 ↑ 1.0 1 133,799

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

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

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

37. 1,204.191 1,204.191 ↑ 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.009 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.006..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 4))
  • Filter: ((creationdate >= '2019-12-30'::date) AND (creationdate < '2020-01-13'::date))
  • Rows Removed by Filter: 0
40.          

SubPlan (for Index Scan)

41. 22.368 3,489.408 ↓ 0.0 0 22,368

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

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

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

  • Sort Key: cw_1.creationdate
  • Sort Method: top-N heapsort Memory: 25kB
43. 33.282 3,377.568 ↓ 0.0 0 22,368

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

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

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

  • Join Filter: (cw_1.crmworkflowtypeparamid = pv_t_1.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_1 (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. 127.068 2,997.312 ↑ 39.0 1 22,368

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

47. 2,326.272 2,326.272 ↑ 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.090..0.104 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. 543.972 543.972 ↑ 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.025..0.026 rows=1 loops=20,922)

  • Index Cond: (crmworkflowid = cwd.crmworkflowid)
49. 56.190 56.190 ↓ 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.005..0.005 rows=0 loops=11,238)

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

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

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

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

  • Sort Key: cw_1.creationdate
  • Sort Method: top-N heapsort Memory: 25kB
52. 33.282 3,377.568 ↓ 0.0 0 22,368

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

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

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

  • Join Filter: (cw_1.crmworkflowtypeparamid = pv_t_1.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_1 (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. 127.068 2,997.312 ↑ 39.0 1 22,368

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

56. 2,326.272 2,326.272 ↑ 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.090..0.104 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. 543.972 543.972 ↑ 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.025..0.026 rows=1 loops=20,922)

  • Index Cond: (crmworkflowid = cwd.crmworkflowid)
58. 56.190 56.190 ↓ 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.005..0.005 rows=0 loops=11,238)

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

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

60. 0.095 30.220 ↓ 6.0 6 10

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

61. 0.190 28.740 ↓ 3.5 28 10

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

62. 0.040 5.650 ↑ 1.0 1 10

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

63. 0.050 4.700 ↑ 1.0 1 10

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

64. 3.170 3.170 ↑ 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.316..0.317 rows=1 loops=10)

  • Index Cond: ((gsrn)::text = (deliverypoint.gsrn)::text)
65. 1.480 1.480 ↑ 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.147..0.148 rows=1 loops=10)

  • Index Cond: (deliverypointid = dp_w.deliverypointid)
66. 0.910 0.910 ↑ 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.091..0.091 rows=1 loops=10)

  • Index Cond: (deliveryperiodid = deliveryperiod.deliveryperiodid)
67. 22.900 22.900 ↓ 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.248..2.290 rows=28 loops=10)

  • Index Cond: (contractdeliveryid = cdp_1.contractdeliveryid)
68. 1.385 1.385 ↓ 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.005 rows=0 loops=277)

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

Index Scan using parametervalueid on parametervalue pv_s (cost=0.28..0.30 rows=1 width=4) (actual time=0.004..0.004 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.010 35.300 ↓ 0.0 0 10

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

72. 0.033 35.290 ↓ 0.0 0 10

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

73. 0.157 33.860 ↑ 1.0 1 10

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

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

Bitmap Heap Scan on index (cost=10.01..14.04 rows=1 width=11) (actual time=3.001..3.070 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
75. 0.090 5.390 ↓ 0.0 0 10

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

76. 2.660 2.660 ↑ 2.1 28 10

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

  • Index Cond: (deliverypointcomponentid = deliverypointcomponent.deliverypointcomponentid)
77. 2.640 2.640 ↑ 2.2 27 10

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

  • Index Cond: (deliverypointid = deliverypoint.deliverypointid)
78. 0.033 3.003 ↓ 0.0 0 11

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

79. 0.132 2.970 ↓ 0.0 0 11

Sort (cost=16.92..16.92 rows=1 width=156) (actual time=0.270..0.270 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
80. 0.063 2.838 ↑ 1.0 1 11

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

81. 1.023 1.023 ↑ 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.066..0.093 rows=1 loops=11)

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

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

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

Index Scan using consumption_indexid_idx on consumption (cost=0.43..35.25 rows=25 width=4) (actual time=0.127..0.127 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 : 38.907 ms
Execution time : 20,885.427 ms