explain.depesz.com

PostgreSQL's explain analyze made readable

Result: EOJe : original query avec table tempo

Settings
# exclusive inclusive rows x rows loops node
1. 14,008.245 275,967.239 ↓ 1.0 462,590 1

Hash Left Join (cost=5,733,980.78..34,532,166.77 rows=462,557 width=1,130) (actual time=168,993.996..275,967.239 rows=462,590 loops=1)

  • Hash Cond: (sub.currentproviderid = currentsup.parametervalueid)
2.          

Initplan (for Hash Left Join)

3. 0.000 757.393 ↓ 0.0 0 1

Result (cost=54,954.62..54,962.72 rows=60 width=32) (actual time=757.393..757.393 rows=0 loops=1)

4.          

Initplan (for Result)

5. 0.002 757.377 ↓ 0.0 0 1

Limit (cost=54,954.18..54,954.19 rows=1 width=8) (actual time=757.377..757.377 rows=0 loops=1)

6. 0.012 757.375 ↓ 0.0 0 1

Sort (cost=54,954.18..54,954.19 rows=1 width=8) (actual time=757.375..757.375 rows=0 loops=1)

  • Sort Key: index.todate DESC, index.indexid DESC
  • Sort Method: quicksort Memory: 25kB
7. 757.363 757.363 ↓ 0.0 0 1

Index Scan using "IX_index_context_index" on index (cost=0.43..54,954.17 rows=1 width=8) (actual time=757.363..757.363 rows=0 loops=1)

  • Index Cond: ((context)::text = 'INITIAL'::text)
  • Filter: ((externalref)::text = '7793'::text)
  • Rows Removed by Filter: 191,804
8. 0.001 757.392 ↓ 0.0 0 1

ProjectSet (cost=0.43..7.19 rows=60 width=40) (actual time=757.392..757.392 rows=0 loops=1)

9. 757.391 757.391 ↓ 0.0 0 1

Index Scan using "IX_indexdetail_indexid_indexdetail" on indexdetail id (cost=0.43..6.84 rows=6 width=39) (actual time=757.391..757.391 rows=0 loops=1)

  • Index Cond: (indexid = $18)
10. 513.423 177,388.240 ↓ 1.0 462,590 1

Hash Left Join (cost=5,678,815.11..6,470,836.74 rows=462,557 width=560) (actual time=167,208.608..177,388.240 rows=462,590 loops=1)

  • Hash Cond: (co.blocktypeparamid = block.parametervalueid)
11. 625.297 176,874.158 ↓ 1.0 462,590 1

Hash Left Join (cost=5,678,612.17..6,469,419.57 rows=462,557 width=549) (actual time=167,207.909..176,874.158 rows=462,590 loops=1)

  • Hash Cond: (lasthistory.productcodeparamid = lastprod.parametervalueid)
12. 655.549 176,248.186 ↓ 1.0 462,590 1

Hash Left Join (cost=5,678,409.22..6,467,998.28 rows=462,557 width=534) (actual time=167,207.211..176,248.186 rows=462,590 loops=1)

  • Hash Cond: (currenthistory.productcodeparamid = currentprod.parametervalueid)
13. 1,005.859 175,591.914 ↓ 1.0 462,590 1

Hash Left Join (cost=5,678,206.28..6,466,576.98 rows=462,557 width=519) (actual time=167,206.460..175,591.914 rows=462,590 loops=1)

  • Hash Cond: (firsthistory.productcodeparamid = firstprod.parametervalueid)
14. 700.914 174,581.583 ↓ 1.0 462,590 1

Hash Left Join (cost=5,678,003.33..6,465,155.69 rows=462,557 width=504) (actual time=167,201.950..174,581.583 rows=462,590 loops=1)

  • Hash Cond: ((COALESCE(gc_dgo.netarea, gc_usr.netarea))::text = (netarea.netarea)::text)
15. 2,315.655 173,880.083 ↓ 1.0 462,590 1

Hash Right Join (cost=5,677,999.31..6,463,911.72 rows=462,557 width=500) (actual time=167,201.306..173,880.083 rows=462,590 loops=1)

  • Hash Cond: (instaltemplate.contractid = co.contractid)
16. 465.294 4,379.769 ↑ 1.0 378,217 1

Nested Loop Left Join (cost=0.43..748,108.36 rows=378,217 width=16) (actual time=0.669..4,379.769 rows=378,217 loops=1)

17. 132.305 132.305 ↑ 1.0 378,217 1

Seq Scan on instaltemplate (cost=0.00..5,456.17 rows=378,217 width=8) (actual time=0.292..132.305 rows=378,217 loops=1)

18. 3,782.170 3,782.170 ↑ 1.0 1 378,217

Index Scan using invoice_pkey on invoice very_lastinstal_template (cost=0.43..1.96 rows=1 width=16) (actual time=0.010..0.010 rows=1 loops=378,217)

  • Index Cond: (invoiceid = instaltemplate.invoiceid)
19. 999.646 167,184.659 ↓ 1.0 462,590 1

Hash (cost=5,643,306.91..5,643,306.91 rows=462,557 width=488) (actual time=167,184.659..167,184.659 rows=462,590 loops=1)

  • Buckets: 8,192 Batches: 64 Memory Usage: 2,570kB
20. 1,257.052 166,185.013 ↓ 1.0 462,590 1

Hash Right Join (cost=5,024,769.39..5,643,306.91 rows=462,557 width=488) (actual time=152,432.537..166,185.013 rows=462,590 loops=1)

  • Hash Cond: (instal.contractid = co.contractid)
21. 5,000.662 40,914.831 ↑ 1.0 424,746 1

Hash Right Join (cost=932,418.19..1,512,057.45 rows=424,746 width=9) (actual time=28,043.681..40,914.831 rows=424,746 loops=1)

  • Hash Cond: (lastinstal_template_cr.finalinvoiceid = lastinstal_i.invoiceid)
22. 8,209.937 8,209.937 ↑ 1.0 8,920,569 1

Seq Scan on invoicerelation lastinstal_template_cr (cost=0.00..470,699.79 rows=9,013,591 width=8) (actual time=1.180..8,209.937 rows=8,920,569 loops=1)

  • Filter: ((nature)::text = 'TEMPLATED'::text)
  • Rows Removed by Filter: 12,429,581
23. 179.786 27,704.232 ↑ 1.0 424,746 1

Hash (cost=925,034.86..925,034.86 rows=424,746 width=13) (actual time=27,704.232..27,704.232 rows=424,746 loops=1)

  • Buckets: 131,072 Batches: 8 Memory Usage: 3,364kB
24. 8,192.355 27,524.446 ↑ 1.0 424,746 1

Hash Right Join (cost=418,739.97..925,034.86 rows=424,746 width=13) (actual time=11,999.707..27,524.446 rows=424,746 loops=1)

  • Hash Cond: (lastinstal_id.invoiceid = lastinstal_i.invoiceid)
25. 7,334.082 7,334.082 ↑ 1.0 13,104,947 1

Seq Scan on invdoc lastinstal_id (cost=0.00..323,668.47 rows=13,104,947 width=9) (actual time=0.208..7,334.082 rows=13,104,947 loops=1)

26. 229.140 11,998.009 ↑ 1.0 424,746 1

Hash (cost=411,770.65..411,770.65 rows=424,746 width=8) (actual time=11,998.009..11,998.009 rows=424,746 loops=1)

  • Buckets: 131,072 Batches: 8 Memory Usage: 3,096kB
27. 2,122.139 11,768.869 ↑ 1.0 424,746 1

Merge Left Join (cost=49,153.66..411,770.65 rows=424,746 width=8) (actual time=262.237..11,768.869 rows=424,746 loops=1)

  • Merge Cond: (instal.invoiceid = lastinstal_i.invoiceid)
28. 322.456 403.988 ↑ 1.0 424,746 1

Sort (cost=49,153.23..50,215.09 rows=424,746 width=8) (actual time=259.814..403.988 rows=424,746 loops=1)

  • Sort Key: instal.invoiceid
  • Sort Method: external merge Disk: 7,528kB
29. 81.532 81.532 ↑ 1.0 424,746 1

Seq Scan on instal (cost=0.00..6,127.46 rows=424,746 width=8) (actual time=0.183..81.532 rows=424,746 loops=1)

30. 9,242.742 9,242.742 ↓ 1.0 14,479,098 1

Index Only Scan using invoice_pkey on invoice lastinstal_i (cost=0.43..320,053.40 rows=14,477,131 width=4) (actual time=0.347..9,242.742 rows=14,479,098 loops=1)

  • Heap Fetches: 2,056
31. 1,195.016 124,013.130 ↓ 1.0 462,590 1

Hash (cost=4,057,659.24..4,057,659.24 rows=462,557 width=483) (actual time=124,013.130..124,013.130 rows=462,590 loops=1)

  • Buckets: 8,192 Batches: 64 Memory Usage: 2,536kB
32. 2,354.408 122,818.114 ↓ 1.0 462,590 1

Hash Left Join (cost=3,679,090.27..4,057,659.24 rows=462,557 width=483) (actual time=104,393.987..122,818.114 rows=462,590 loops=1)

  • Hash Cond: (dp.deliverypointid = gc_usr.deliverypointid)
  • Join Filter: (((cdp.todate IS NULL) AND (gc_usr.todate IS NULL)) OR ((cdp.todate IS NOT NULL) AND (gc_usr.fromdate < cdp.todate) AND ((gc_usr.todate IS NULL) OR (gc_usr.todate >= cdp.todate))))
  • Rows Removed by Join Filter: 799,140
33. 260.077 119,356.585 ↓ 1.0 462,590 1

Hash Left Join (cost=3,630,253.79..3,940,471.44 rows=462,557 width=446) (actual time=103,286.726..119,356.585 rows=462,590 loops=1)

  • Hash Cond: (dp.deliverypointid = lc_usr.deliverypointid)
  • Join Filter: (((cdp.todate IS NULL) AND (lc_usr.todate IS NULL)) OR ((cdp.todate IS NOT NULL) AND (lc_usr.fromdate < cdp.todate) AND ((lc_usr.todate IS NULL) OR (lc_usr.todate >= cdp.todat (...)
  • Rows Removed by Join Filter: 2
34. 2,590.874 119,087.763 ↓ 1.0 462,590 1

Hash Left Join (cost=3,630,252.33..3,938,735.38 rows=462,557 width=442) (actual time=103,277.945..119,087.763 rows=462,590 loops=1)

  • Hash Cond: (dp.deliverypointid = gc_dgo.deliverypointid)
  • Join Filter: (((cdp.todate IS NULL) AND (gc_dgo.todate IS NULL)) OR ((cdp.todate IS NOT NULL) AND (gc_dgo.fromdate < cdp.todate) AND ((gc_dgo.todate IS NULL) OR (gc_dgo.todate >= cdp (...)
  • Rows Removed by Join Filter: 523,340
35. 774.963 114,665.500 ↓ 1.0 462,590 1

Hash Left Join (cost=3,570,063.02..3,807,864.93 rows=462,557 width=405) (actual time=101,446.431..114,665.500 rows=462,590 loops=1)

  • Hash Cond: ((SubPlan 27) = pc_usr.physicalconfigid)
36. 2,647.466 106,366.988 ↓ 1.0 462,590 1

Hash Left Join (cost=3,537,592.87..3,688,449.77 rows=462,557 width=401) (actual time=100,861.210..106,366.988 rows=462,590 loops=1)

  • Hash Cond: (dp.deliverypointid = pc_dgo.deliverypointid)
  • Join Filter: (((cdp.todate IS NULL) AND (pc_dgo.todate IS NULL)) OR ((cdp.todate IS NOT NULL) AND (pc_dgo.fromdate < cdp.todate) AND ((pc_dgo.todate IS NULL) OR (pc_dgo.t (...)
  • Rows Removed by Join Filter: 522,837
37. 405.417 102,791.630 ↓ 1.0 462,590 1

Hash Left Join (cost=3,503,916.89..3,590,386.22 rows=462,557 width=397) (actual time=99,929.069..102,791.630 rows=462,590 loops=1)

  • Hash Cond: (((brpj.brp)::text = (brp.externalid)::text) AND ((dp.market)::text = (brp.marketcode)::text))
38. 1,561.789 102,386.007 ↓ 1.0 462,590 1

Hash Right Join (cost=3,503,908.84..3,581,228.40 rows=462,557 width=402) (actual time=99,928.827..102,386.007 rows=462,590 loops=1)

  • Hash Cond: (lc_dgo.deliverypointid = dp.deliverypointid)
  • Join Filter: (((cdp.todate IS NULL) AND (lc_dgo.todate IS NULL)) OR ((cdp.todate IS NOT NULL) AND (lc_dgo.fromdate < cdp.todate) AND ((lc_dgo.todate IS NULL) (...)
  • Rows Removed by Join Filter: 523,247
39. 899.228 899.228 ↓ 1.0 574,695 1

Seq Scan on logicalconfig lc_dgo (cost=0.00..35,406.52 rows=573,285 width=16) (actual time=2.402..899.228 rows=574,695 loops=1)

  • Filter: ((NOT deleted) AND ((sourceid)::text = 'DGO'::text) AND ((status)::text = 'ACTUAL'::text))
  • Rows Removed by Filter: 199,073
40. 1,059.679 99,924.990 ↓ 1.0 462,590 1

Hash (cost=3,474,185.87..3,474,185.87 rows=462,557 width=398) (actual time=99,924.990..99,924.990 rows=462,590 loops=1)

  • Buckets: 16,384 Batches: 64 Memory Usage: 2,311kB
41. 533.305 98,865.311 ↓ 1.0 462,590 1

Hash Left Join (cost=1,506,332.96..3,474,185.87 rows=462,557 width=398) (actual time=87,208.135..98,865.311 rows=462,590 loops=1)

  • Hash Cond: (((dp.gridoperator)::text = (dgo_encom.gridoperator)::text) AND ((dp.regulator)::text = (dgo_hgz.regulator)::text) AND ((dp.market)::te (...)
42. 2,049.393 98,331.511 ↓ 1.0 462,590 1

Hash Left Join (cost=1,506,209.03..3,467,818.15 rows=462,557 width=292) (actual time=87,207.615..98,331.511 rows=462,590 loops=1)

  • Hash Cond: (per.deliveryperiodid = brpj.deliveryperiodid)
  • Join Filter: (((cdp.todate IS NULL) AND (brpj.todate IS NULL)) OR ((cdp.todate IS NOT NULL) AND (brpj.fromdate < cdp.todate) AND ((brpj.toda (...)
  • Rows Removed by Join Filter: 98,984
43. 926.092 95,851.060 ↓ 1.0 462,590 1

Merge Right Join (cost=1,487,802.45..3,402,269.48 rows=462,557 width=283) (actual time=86,770.329..95,851.060 rows=462,590 loops=1)

  • Merge Cond: (freeaddressesdetail.addressid = dp.addressid)
44. 397.041 7,412.194 ↓ 83.9 359,453 1

Nested Loop Left Join (cost=1.13..4,146,079.87 rows=4,285 width=54) (actual time=4.664..7,412.194 rows=359,453 loops=1)

45. 3,420.623 6,655.700 ↓ 83.9 359,453 1

Nested Loop (cost=0.85..4,138,823.29 rows=4,285 width=107) (actual time=1.031..6,655.700 rows=359,453 loops=1)

  • -> Index Scan using freeadddetail_addressid_idx on freeaddressesdetail (cost=0.42..4130279.18 rows=4,285 width=39) (actua (...)
  • Filter: ((((addressid)::text || ';'::text) || (addressdetailnbr)::text) = (SubPlan 26))
  • -> Index Scan using freeaddresses_pkey on freeaddresses (cost=0.42..1.99 rows=1 width=72) (actual time=0.005..0.005 rows (...)
  • Index Cond: (addressid = freeaddressesdetail.addressid)
46.          

SubPlan (for Nested Loop)

47. 359.453 3,235.077 ↑ 1.0 1 359,453

Limit (cost=4.76..4.77 rows=1 width=36) (actual time=0.008..0.009 rows=1 loops=359,453)

  • -> Index Scan using property_pkey on property (cost=0.27..2.29 rows=1 width=7) (actual time=0.929..0.931 (...)
  • Index Cond: ((propertycode)::text = 'defaultCustomerLanguageCode'::text)
48. 2,875.624 2,875.624 ↑ 1.0 1 359,453

Sort (cost=2.47..2.48 rows=1 width=36) (actual time=0.008..0.008 rows=1 loops=359,453)

  • Sort Key: (CASE WHEN ((freeaddressesdetail_1.language)::text = ($43)::text) THEN 1 WHEN ((freeaddresse (...)
  • Sort Method: quicksort Memory: 25kB
  • -> Index Scan using freeadddetail_addressid_idx on freeaddressesdetail freeaddressesdetail_1 (cost=0 (...)
  • Index Cond: (addressid = freeaddressesdetail.addressid)
49. 359.453 359.453 ↓ 0.0 0 359,453

Index Scan using town_pkey on town t (cost=0.28..1.69 rows=1 width=22) (actual time=0.001..0.001 rows=0 loops=359,453)

  • Index Cond: (((country)::text = (freeaddresses.country)::text) AND ((postalcode)::text = (freeaddresses.postalcode)::text) (...)
  • Filter: ((postalcode)::text <> 'UNKNOWN'::text)
  • Rows Removed by Filter: 0
50. 189.432 87,512.774 ↓ 1.0 462,590 1

Materialize (cost=1,487,801.32..1,490,114.11 rows=462,557 width=237) (actual time=86,765.647..87,512.774 rows=462,590 loops=1)

51. 56,343.717 87,323.342 ↓ 1.0 462,590 1

Sort (cost=1,487,801.32..1,488,957.72 rows=462,557 width=237) (actual time=86,765.640..87,323.342 rows=462,590 loops=1)

  • Sort Key: dp.addressid
  • Sort Method: external merge Disk: 90,552kB
  • -> Hash Left Join (cost=341,642.10..1384648.39 rows=462,557 width=237) (actual time=30,170.820..85581.030 rows=462,590 loops (...)
  • Hash Cond: (per.deliverypointid = dp.deliverypointid)
  • -> Hash Left Join (cost=322,370.95..1335445.02 rows=462,557 width=198) (actual time=29,684.279..83448.332 rows=462,590 (...)
  • Hash Cond: ((co.contractid = lasthistory.contractid) AND ((SubPlan 21) = lasthistory.contracthistentry))
  • -> Hash Left Join (cost=275,432.72..1218605.09 rows=462,557 width=178) (actual time=28,593.350..67797.565 rows= (...)
  • Hash Cond: ((co.contractid = currenthistory.contractid) AND ((SubPlan 19) = currenthistory.contracthiste (...)
  • -> Hash Left Join (cost=228,494.50..1104359.53 rows=462,557 width=158) (actual time=20,208.497..45668.642 (...)
  • Hash Cond: (cdp.contractdeliveryid = sub.contractdeliveryid)
  • -> Hash Left Join (cost=203,618.87..1056554.68 rows=462,557 width=142) (actual time=19,513.847..435 (...)
  • Hash Cond: (cdp.deliveryperiodid = per.deliveryperiodid)
  • -> Hash Left Join (cost=185,441.15..1016680.74 rows=462,557 width=138) (actual time=19,086.45 (...)
  • Hash Cond: ((co.contractid = cdp.contractid) AND ((SubPlan 22) = cdp.contractdeliveryi (...)
  • -> Hash Right Join (cost=158,233.35..911262.64 rows=462,557 width=118) (actual time=18 (...)
  • Hash Cond: (cj.contractid = co.contractid)
  • -> Seq Scan on contractjournal cj (cost=0.00..744809.20 rows=3,716 width=9) (ac (...)
  • Filter: (journalentry = (SubPlan 24))
  • Rows Removed by Filter: 280,637
  • -> Result (cost=0.95..0.96 rows=1 width=4) (actual time=0.007..0.007 r (...)
  • -> Limit (cost=0.42..0.95 rows=1 width=4) (actual time=0.006.. (...)
  • -> Index Only Scan Backward using contractjournal_pkey on (...)
  • Index Cond: ((contractid = cj.contractid) AND (journ (...)
  • Heap Fetches: 0
  • -> Hash (cost=144,320.39..144320.39 rows=462,557 width=113) (actual time=18,368.2 (...)
  • Buckets: 32,768 Batches: 32 Memory Usage: 1,800kB
  • -> Hash Left Join (cost=58,505.13..144320.39 rows=462,557 width=113) (actu (...)
  • Hash Cond: (co.contractstatusparamid = stat.parametervalueid)
  • -> Hash Left Join (cost=58,302.19..142899.09 rows=462,557 width=93) (...)
  • Hash Cond: (co.supplierid = s.supplierid)
  • -> Hash Left Join (cost=58,279.59..141653.09 rows=462,557 widt (...)
  • Hash Cond: ((co.contractid = firsthistory.contractid) AN (...)
  • -> Hash Right Join (cost=11,341.36..40171.17 rows=46,255 (...)
  • Hash Cond: (co.customerid = cu.customerid)
  • -> Seq Scan on contract co (cost=0.00..19238.57 (...)
  • -> Hash (cost=7,251.05..7251.05 rows=235,305 width (...)
  • Buckets: 131,072 Batches: 4 Memory Usage: 3 (...)
  • -> Seq Scan on customer cu (cost=0.00..725 (...)
  • -> Hash (cost=23,378.29..23378.29 rows=1,078,929 width=28 (...)
  • Buckets: 65,536 Batches: 32 Memory Usage: 2,351kB
  • -> Seq Scan on contracthistory firsthistory (cos (...)
  • -> Result (cost=1.57..1.58 rows=1 width=4) (actual t (...)
  • -> Limit (cost=0.43..1.57 rows=1 width=4) (a (...)
  • -> Index Only Scan using "Key4" on cont (...)
  • Index Cond: ((contractid = co.cont (...)
  • Heap Fetches: 925,114
  • -> Hash (cost=15.60..15.60 rows=560 width=36) (actual time=0 (...)
  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
  • -> Seq Scan on supplier s (cost=0.00..15.60 rows=560 w (...)
  • -> Hash (cost=187.42..187.42 rows=1,242 width=28) (actual time=6.72 (...)
  • Buckets: 2,048 Batches: 1 Memory Usage: 82kB
  • -> Seq Scan on parametervalue stat (cost=0.00..187.42 rows=1 (...)
  • -> Hash (cost=17,452.32..17452.32 rows=467,632 width=20) (actual time=574.171..574.171 (...)
  • Buckets: 65,536 Batches: 8 Memory Usage: 3,414kB
  • -> Seq Scan on contractdelivery cdp (cost=0.00..17452.32 rows=467,632 width=20) (...)
  • -> Limit (cost=2.45..2.45 rows=1 width=8) (actual time=0.014..0.014 rows=1 loops=9 (...)
  • -> Sort (cost=2.45..2.45 rows=1 width=8) (actual time=0.013..0.013 rows=1 lo (...)
  • Sort Key: cdp2_1.todate DESC
  • Sort Method: quicksort Memory: 25kB
  • -> Index Scan using contractdelively_contract_id_idx on contractdeliver (...)
  • Index Cond: (contractid = co.contractid)
  • -> Hash (cost=11,845.43..11845.43 rows=385,943 width=8) (actual time=360.582..360.583 rows=3 (...)
  • Buckets: 131,072 Batches: 8 Memory Usage: 2,904kB
  • -> Seq Scan on deliveryperiod per (cost=0.00..11845.43 rows=385,943 width=8) (actual (...)
  • -> Hash (cost=16,289.28..16289.28 rows=467,628 width=24) (actual time=694.540..694.540 rows=467,628 (...)
  • Buckets: 65,536 Batches: 8 Memory Usage: 3,945kB
  • -> Seq Scan on subscription sub (cost=0.00..16289.28 rows=467,628 width=24) (actual time=50 (...)
  • -> Hash (cost=23,378.29..23378.29 rows=1,078,929 width=28) (actual time=968.930..968.930 rows=1,078,930 loo (...)
  • Buckets: 65,536 Batches: 32 Memory Usage: 2,351kB
  • -> Seq Scan on contracthistory currenthistory (cost=0.00..23378.29 rows=1,078,929 width=28) (actua (...)
  • -> Hash (cost=23,378.29..23378.29 rows=1,078,929 width=28) (actual time=1,090.788..1090.788 rows=1,078,930 loops=1 (...)
  • Buckets: 65,536 Batches: 32 Memory Usage: 2,351kB
  • -> Seq Scan on contracthistory lasthistory (cost=0.00..23378.29 rows=1,078,929 width=28) (actual time=0. (...)
52. 486.434 486.434 ↑ 1.1 359,452 1

Hash (cost=10,987.18..10,987.18 rows=389,118 width=43) (actual time=486.434..486.434 rows=359,452 loops=1)

  • Buckets: 65,536 Batches: 16 Memory Usage: 2,227kB
  • -> Seq Scan on deliverypoint dp (cost=0.00..10987.18 rows=389,118 width=43) (actual time=0.739..255.268 rows= (...)
53.          

SubPlan (for Sort)

54. 923.314 18,466.280 ↑ 1.0 1 923,314

Result (cost=1.58..1.59 rows=1 width=4) (actual time=0.020..0.020 rows=1 loops=923,314)

55.          

Initplan (for Result)

56. 17,542.966 17,542.966 ↑ 1.0 1 923,314

Limit (cost=0.43..1.58 rows=1 width=4) (actual time=0.018..0.019 rows=1 loops=923,314)

  • -> Index Scan Backward using "Key4" on contracthistory ch2_1 (cost=0.43..5.03 rows=4 w (...)
  • Index Cond: ((contractid = co.contractid) AND (contracthistentry IS NOT NULL))
  • Filter: (startdate < now())
  • Rows Removed by Filter: 0
57. 925.147 12,026.911 ↑ 1.0 1 925,147

Result (cost=1.57..1.58 rows=1 width=4) (actual time=0.012..0.013 rows=1 loops=925,147)

58.          

Initplan (for Result)

59. 11,101.764 11,101.764 ↑ 1.0 1 925,147

Limit (cost=0.43..1.57 rows=1 width=4) (actual time=0.011..0.012 rows=1 loops=925,147)

  • -> Index Only Scan Backward using "Key4" on contracthistory ch2_2 (cost=0.43..5.01 rows=4 wi (...)
  • Index Cond: ((contractid = co.contractid) AND (contracthistentry IS NOT NULL))
  • Heap Fetches: 925,114
60. 431.058 431.058 ↑ 1.0 409,981 1

Hash (cost=10,478.81..10,478.81 rows=409,981 width=25) (actual time=431.058..431.058 rows=409,981 loops=1)

  • Buckets: 65,536 Batches: 8 Memory Usage: 3,323kB
  • -> Seq Scan on deliveryperiodbrpjournal brpj (cost=0.00..10478.81 rows=409,981 width=25) (actual time=1.804..244.299 rows=409,981 loop (...)
61. 0.049 0.495 ↓ 1.5 62 1

Hash (cost=123.20..123.20 rows=42 width=155) (actual time=0.495..0.495 rows=62 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 15kB
62. 0.052 0.446 ↓ 1.5 62 1

Nested Loop Left Join (cost=13.61..123.20 rows=42 width=155) (actual time=0.287..0.446 rows=62 loops=1)

  • -> Index Scan using freeaddresses_pkey on freeaddresses dgoadd (cost=0.43..2.45 rows=1 width=49) (actual time=0.000..0.000 rows=0 lo (...)
63. 0.039 0.394 ↓ 1.5 62 1

Hash Left Join (cost=13.19..20.51 rows=42 width=114) (actual time=0.278..0.394 rows=62 loops=1)

  • Hash Cond: (dgo_hgz.gridoperatorid = dgoac.actorid)
  • Index Cond: (addressid = dgoac.addressid)
64. 0.034 0.246 ↓ 1.5 62 1

Hash Join (cost=5.34..12.54 rows=42 width=87) (actual time=0.158..0.246 rows=62 loops=1)

  • Hash Cond: (a.actorid = dgo_hgz.gridoperatorid)
65. 0.058 0.148 ↓ 1.2 64 1

Hash Join (cost=1.92..8.97 rows=54 width=79) (actual time=0.084..0.148 rows=64 loops=1)

  • Hash Cond: ((a.externalid)::text = (dgo_encom.gridoperator)::text)
66. 0.041 0.041 ↑ 1.0 82 1

Seq Scan on actor a (cost=0.00..6.82 rows=82 width=24) (actual time=0.024..0.041 rows=82 loops=1)

67. 0.049 0.049 ↓ 1.0 42 1

Hash (cost=1.41..1.41 rows=41 width=64) (actual time=0.049..0.049 rows=42 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
  • -> Seq Scan on gridoperator dgo_encom (cost=0.00..1.41 rows=41 width=64) (actual time=0.024..0.032 rows=42 l (...)
68. 0.019 0.064 ↑ 1.0 63 1

Hash (cost=2.63..2.63 rows=63 width=12) (actual time=0.064..0.064 rows=63 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
69. 0.045 0.045 ↑ 1.0 63 1

Seq Scan on gridoperator dgo_hgz (cost=0.00..2.63 rows=63 width=12) (actual time=0.020..0.045 rows=63 loops=1)

70. 0.028 0.109 ↑ 1.0 82 1

Hash (cost=6.82..6.82 rows=82 width=31) (actual time=0.109..0.109 rows=82 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 13kB
71. 0.081 0.081 ↑ 1.0 82 1

Seq Scan on actor dgoac (cost=0.00..6.82 rows=82 width=31) (actual time=0.006..0.081 rows=82 loops=1)

72. 0.070 0.206 ↑ 1.0 81 1

Hash (cost=6.82..6.82 rows=82 width=22) (actual time=0.206..0.206 rows=81 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 13kB
73. 0.136 0.136 ↑ 1.0 82 1

Seq Scan on actor brp (cost=0.00..6.82 rows=82 width=22) (actual time=0.033..0.136 rows=82 loops=1)

74. 254.443 927.892 ↓ 1.0 574,082 1

Hash (cost=23,699.10..23,699.10 rows=573,911 width=16) (actual time=927.892..927.892 rows=574,082 loops=1)

  • Buckets: 131,072 Batches: 16 Memory Usage: 2,625kB
75. 673.449 673.449 ↓ 1.0 574,082 1

Seq Scan on physicalconfig pc_dgo (cost=0.00..23,699.10 rows=573,911 width=16) (actual time=0.318..673.449 rows=574,082 loops=1)

  • Filter: ((NOT deleted) AND ((sourceid)::text = 'DGO'::text) AND ((status)::text = 'ACTUAL'::text))
  • Rows Removed by Filter: 194,186
76. 236.617 584.639 ↑ 1.0 768,268 1

Hash (cost=19,854.40..19,854.40 rows=768,940 width=4) (actual time=584.638..584.639 rows=768,268 loops=1)

  • Buckets: 131,072 Batches: 16 Memory Usage: 2,719kB
77. 348.022 348.022 ↑ 1.0 768,268 1

Seq Scan on physicalconfig pc_usr (cost=0.00..19,854.40 rows=768,940 width=4) (actual time=0.028..348.022 rows=768,268 loops=1)

78.          

SubPlan (for Hash Left Join)

79. 462.594 6,938.910 ↓ 0.0 0 462,594

Limit (cost=1.46..1.47 rows=1 width=4) (actual time=0.015..0.015 rows=0 loops=462,594)

80. 462.594 6,476.316 ↓ 0.0 0 462,594

Sort (cost=1.46..1.47 rows=1 width=4) (actual time=0.014..0.014 rows=0 loops=462,594)

  • Sort Key: pc.physicalconfigid DESC
  • Sort Method: quicksort Memory: 25kB
81. 6,013.722 6,013.722 ↓ 0.0 0 462,594

Index Scan using physicalconfig_sourceid_idx on physicalconfig pc (cost=0.42..1.45 rows=1 width=4) (actual time=0.013..0.013 rows=0 loops=462,594)

  • Index Cond: ((sourceid)::text = 'USER'::text)
  • Filter: ((NOT deleted) AND ((status)::text = 'ACTUAL'::text) AND (deliverypointid = dp.deliverypointid) AND (((cdp.todate IS NULL) AND (todate IS NULL)) OR (...)
  • Rows Removed by Filter: 5
82. 377.408 1,831.389 ↑ 1.1 574,695 1

Hash (cost=45,789.22..45,789.22 rows=646,727 width=49) (actual time=1,831.389..1,831.389 rows=574,695 loops=1)

  • Buckets: 65,536 Batches: 16 Memory Usage: 3,201kB
83. 1,453.981 1,453.981 ↑ 1.1 574,695 1

Seq Scan on generalconfig gc_dgo (cost=0.00..45,789.22 rows=646,727 width=49) (actual time=0.253..1,453.981 rows=574,695 loops=1)

  • Filter: ((NOT deleted) AND ((sourceid)::text = 'DGO'::text) AND ((status)::text = 'ACTUAL'::text))
  • Rows Removed by Filter: 664,631
84. 0.016 8.745 ↓ 4.0 4 1

Hash (cost=1.45..1.45 rows=1 width=16) (actual time=8.745..8.745 rows=4 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
85. 8.729 8.729 ↓ 4.0 4 1

Index Scan using logicalconfig_sourceid_idx on logicalconfig lc_usr (cost=0.42..1.45 rows=1 width=16) (actual time=4.640..8.729 rows=4 loops=1)

  • Index Cond: ((sourceid)::text = 'USER'::text)
  • Filter: ((NOT deleted) AND ((status)::text = 'ACTUAL'::text))
86. 287.354 1,107.121 ↓ 1.2 467,632 1

Hash (cost=39,961.32..39,961.32 rows=398,573 width=49) (actual time=1,107.121..1,107.121 rows=467,632 loops=1)

  • Buckets: 65,536 Batches: 16 Memory Usage: 2,564kB
87. 702.747 819.767 ↓ 1.2 467,632 1

Bitmap Heap Scan on generalconfig gc_usr (cost=5,670.15..39,961.32 rows=398,573 width=49) (actual time=124.410..819.767 rows=467,632 loops=1)

  • Recheck Cond: ((sourceid)::text = 'USER'::text)
  • Filter: ((NOT deleted) AND ((status)::text = 'ACTUAL'::text))
  • Heap Blocks: exact=27,199
88. 117.020 117.020 ↑ 1.0 467,632 1

Bitmap Index Scan on generalconfig_sourceid_id (cost=0.00..5,570.51 rows=472,411 width=0) (actual time=117.020..117.020 rows=467,632 loops=1)

  • Index Cond: ((sourceid)::text = 'USER'::text)
89. 0.071 0.586 ↓ 1.4 126 1

Hash (cost=2.90..2.90 rows=90 width=23) (actual time=0.586..0.586 rows=126 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 15kB
90. 0.515 0.515 ↓ 1.4 126 1

Seq Scan on netarea (cost=0.00..2.90 rows=90 width=23) (actual time=0.453..0.515 rows=126 loops=1)

91. 0.432 4.472 ↑ 1.1 1,142 1

Hash (cost=187.42..187.42 rows=1,242 width=19) (actual time=4.472..4.472 rows=1,142 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 74kB
92. 4.040 4.040 ↑ 1.1 1,142 1

Seq Scan on parametervalue firstprod (cost=0.00..187.42 rows=1,242 width=19) (actual time=0.316..4.040 rows=1,142 loops=1)

93. 0.349 0.723 ↑ 1.1 1,142 1

Hash (cost=187.42..187.42 rows=1,242 width=19) (actual time=0.723..0.723 rows=1,142 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 74kB
94. 0.374 0.374 ↑ 1.1 1,142 1

Seq Scan on parametervalue currentprod (cost=0.00..187.42 rows=1,242 width=19) (actual time=0.007..0.374 rows=1,142 loops=1)

95. 0.334 0.675 ↑ 1.1 1,142 1

Hash (cost=187.42..187.42 rows=1,242 width=19) (actual time=0.675..0.675 rows=1,142 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 74kB
96. 0.341 0.341 ↑ 1.1 1,142 1

Seq Scan on parametervalue lastprod (cost=0.00..187.42 rows=1,242 width=19) (actual time=0.006..0.341 rows=1,142 loops=1)

97. 0.323 0.659 ↑ 1.1 1,142 1

Hash (cost=187.42..187.42 rows=1,242 width=19) (actual time=0.659..0.659 rows=1,142 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 75kB
98. 0.336 0.336 ↑ 1.1 1,142 1

Seq Scan on parametervalue block (cost=0.00..187.42 rows=1,242 width=19) (actual time=0.006..0.336 rows=1,142 loops=1)

99. 0.351 0.705 ↑ 1.1 1,142 1

Hash (cost=187.42..187.42 rows=1,242 width=19) (actual time=0.704..0.705 rows=1,142 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 75kB
100. 0.354 0.354 ↑ 1.1 1,142 1

Seq Scan on parametervalue currentsup (cost=0.00..187.42 rows=1,242 width=19) (actual time=0.006..0.354 rows=1,142 loops=1)

101.          

SubPlan (for Hash Left Join)

102. 1,387.770 29,143.170 ↑ 1.0 1 462,590

Aggregate (cost=8.88..8.89 rows=1 width=4) (actual time=0.063..0.063 rows=1 loops=462,590)

103. 27,755.400 27,755.400 ↓ 2.0 2 462,590

Index Scan using idx_contractrateid_contractid on contractrate cr (cost=0.43..8.88 rows=1 width=4) (actual time=0.049..0.060 rows=2 loops=462,590)

  • Index Cond: (contractid = co.contractid)
  • Filter: (((todate IS NULL) OR (todate > currenthistory.startdate)) AND (fromdate < COALESCE((currenthistory.forcedenddate)::timestamp without time zone, (currenthistory.startdate + (((currenthistory.duration)::text || 'months'::text))::inte (...)
  • Rows Removed by Filter: 3
104. 925.180 6,476.260 ↑ 1.0 1 462,590

Aggregate (cost=8.88..8.89 rows=1 width=4) (actual time=0.014..0.014 rows=1 loops=462,590)

105. 5,551.080 5,551.080 ↓ 2.0 2 462,590

Index Scan using idx_contractrateid_contractid on contractrate cr_1 (cost=0.43..8.88 rows=1 width=4) (actual time=0.009..0.012 rows=2 loops=462,590)

  • Index Cond: (contractid = co.contractid)
  • Filter: (((todate IS NULL) OR (todate > lasthistory.startdate)) AND (fromdate < COALESCE((lasthistory.forcedenddate)::timestamp without time zone, (lasthistory.startdate + (((lasthistory.duration)::text || 'months'::text))::interval))))
  • Rows Removed by Filter: 3
106. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.43..7.35 rows=1 width=0) (never executed)

107. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_attestation_customerid_pk on attestation a_1 (cost=0.29..5.17 rows=1 width=4) (never executed)

  • Index Cond: (customerid = cu.customerid)
  • Filter: ((fromdate <= now()) AND ((todate IS NULL) OR (todate >= now())))
108. 0.000 0.000 ↓ 0.0 0

Index Only Scan using attestationcategory_pkey on attestationcategory ac (cost=0.14..2.16 rows=1 width=4) (never executed)

  • Index Cond: (attestationcategoryid = a_1.attestationcategoryid)
  • Heap Fetches: 0
109. 2.326 37.718 ↓ 1.0 6,186 1

Hash Join (cost=15.62..1,612.82 rows=6,138 width=4) (actual time=1.453..37.718 rows=6,186 loops=1)

  • Hash Cond: (a_2.attestationcategoryid = ac_1.attestationcategoryid)
110. 34.995 34.995 ↓ 1.0 6,186 1

Seq Scan on attestation a_2 (cost=0.00..1,580.80 rows=6,138 width=8) (actual time=1.040..34.995 rows=6,186 loops=1)

  • Filter: ((fromdate <= now()) AND ((todate IS NULL) OR (todate >= now())))
  • Rows Removed by Filter: 35,004
111. 0.023 0.397 ↑ 35.7 7 1

Hash (cost=12.50..12.50 rows=250 width=4) (actual time=0.397..0.397 rows=7 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
112. 0.374 0.374 ↑ 35.7 7 1

Seq Scan on attestationcategory ac_1 (cost=0.00..12.50 rows=250 width=4) (actual time=0.372..0.374 rows=7 loops=1)

113. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.43..7.36 rows=1 width=0) (never executed)

114. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_attestation_customerid_pk on attestation a_3 (cost=0.29..5.17 rows=1 width=4) (never executed)

  • Index Cond: (customerid = cu.customerid)
  • Filter: ((fromdate <= now()) AND ((todate IS NULL) OR (todate >= now())))
115. 0.000 0.000 ↓ 0.0 0

Index Scan using attestationcategory_pkey on attestationcategory ac_2 (cost=0.14..2.16 rows=1 width=4) (never executed)

  • Index Cond: (attestationcategoryid = a_3.attestationcategoryid)
  • Filter: socialinvoiced
116. 2.314 23.767 ↓ 2.0 6,169 1

Hash Join (cost=14.06..1,611.25 rows=3,069 width=4) (actual time=0.062..23.767 rows=6,169 loops=1)

  • Hash Cond: (a_4.attestationcategoryid = ac_3.attestationcategoryid)
117. 21.427 21.427 ↓ 1.0 6,186 1

Seq Scan on attestation a_4 (cost=0.00..1,580.80 rows=6,138 width=8) (actual time=0.024..21.427 rows=6,186 loops=1)

  • Filter: ((fromdate <= now()) AND ((todate IS NULL) OR (todate >= now())))
  • Rows Removed by Filter: 35,004
118. 0.012 0.026 ↑ 20.8 6 1

Hash (cost=12.50..12.50 rows=125 width=4) (actual time=0.026..0.026 rows=6 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
119. 0.014 0.014 ↑ 20.8 6 1

Seq Scan on attestationcategory ac_3 (cost=0.00..12.50 rows=125 width=4) (actual time=0.011..0.014 rows=6 loops=1)

  • Filter: socialinvoiced
  • Rows Removed by Filter: 1
120. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.71..15.39 rows=1 width=0) (never executed)

121. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_contractrateid_contractid on contractrate cr_2 (cost=0.43..8.77 rows=2 width=4) (never executed)

  • Index Cond: (contractid = co.contractid)
  • Filter: ((fromdate <= now()) AND ((todate IS NULL) OR (todate > now())))
122. 0.000 0.000 ↓ 0.0 0

Index Scan using rate_rateid_key on rate r (cost=0.28..2.30 rows=1 width=4) (never executed)

  • Index Cond: (rateid = cr_2.rateid)
  • Filter: ((social IS TRUE) AND (socialinvoiced IS TRUE))
123. 1.616 737.910 ↓ 78.4 5,408 1

Nested Loop (cost=0.43..3,279.71 rows=69 width=4) (actual time=4.182..737.910 rows=5,408 loops=1)

124. 12.214 12.214 ↓ 3.0 3 1

Seq Scan on rate r_1 (cost=0.00..409.56 rows=1 width=4) (actual time=1.692..12.214 rows=3 loops=1)

  • Filter: ((social IS TRUE) AND (socialinvoiced IS TRUE))
  • Rows Removed by Filter: 6,953
125. 724.080 724.080 ↓ 2.1 1,803 3

Index Scan using contractrate_rateid_index on contractrate cr_3 (cost=0.43..2,861.55 rows=860 width=8) (actual time=1.110..241.360 rows=1,803 loops=3)

  • Index Cond: (rateid = r_1.rateid)
  • Filter: ((fromdate <= now()) AND ((todate IS NULL) OR (todate > now())))
  • Rows Removed by Filter: 4,655
126. 781.370 32,843.890 ↓ 0.0 0 462,590

Nested Loop (cost=1.27..5.35 rows=1 width=18) (actual time=0.071..0.071 rows=0 loops=462,590)

127. 781.340 22,666.910 ↑ 1.0 1 462,590

Nested Loop (cost=0.84..4.89 rows=1 width=4) (actual time=0.049..0.049 rows=1 loops=462,590)

128. 12,489.930 12,489.930 ↑ 1.0 1 462,590

Index Scan using contractdelively_contract_id_idx on contractdelivery cdp2 (cost=0.42..2.45 rows=1 width=4) (actual time=0.027..0.027 rows=1 loops=462,590)

  • Index Cond: (contractid = cdp.contractid)
  • Filter: ((fromdate <> todate) AND (cdp.fromdate = fromdate) AND (COALESCE(cdp.todate, '2099-12-31'::date) = COALESCE(todate, '2099-12-31'::date)))
  • Rows Removed by Filter: 0
129. 9,395.640 9,395.640 ↑ 1.0 1 313,188

Index Scan using deliveryperiod_pkey on deliveryperiod per2 (cost=0.42..2.44 rows=1 width=8) (actual time=0.030..0.030 rows=1 loops=313,188)

  • Index Cond: (deliveryperiodid = cdp2.deliveryperiodid)
130. 9,395.610 9,395.610 ↓ 0.0 0 313,187

Index Scan using deliverypoint_pkey on deliverypoint dp2 (cost=0.42..0.46 rows=1 width=22) (actual time=0.030..0.030 rows=0 loops=313,187)

  • Index Cond: (deliverypointid = per2.deliverypointid)
  • Filter: ((gsrn)::text <> (dp.gsrn)::text)
  • Rows Removed by Filter: 1
131. 0.000 0.000 ↓ 0.0 0 462,590

Index Scan using town_pkey on town t_1 (cost=0.28..2.30 rows=1 width=10) (actual time=0.000..0.000 rows=0 loops=462,590)

  • Index Cond: (((country)::text = (dgoadd.towncountrycode)::text) AND ((postalcode)::text = (dgoadd.townpostalcode)::text) AND (towncode = dgoadd.towntowncode))
132. 0.000 0.000 ↓ 0.0 0

Index Scan using meter_physicalconfigid_idx on meter (cost=0.42..2.45 rows=1 width=0) (never executed)

  • Index Cond: (physicalconfigid = COALESCE(pc_dgo.physicalconfigid, pc_usr.physicalconfigid))
  • Filter: ((metertype)::text = 'BUDGET_METER'::text)
133. 209.651 209.651 ↓ 1.1 7,071 1

Index Scan using "IX_meter_metertype_meter" on meter meter_1 (cost=0.42..1,392.48 rows=6,516 width=4) (actual time=3.507..209.651 rows=7,071 loops=1)

  • Index Cond: ((metertype)::text = 'BUDGET_METER'::text)
134. 14,340.290 14,340.290 ↑ 1.0 1 462,590

Index Scan using meter_physicalconfigid_idx on meter meter_2 (cost=0.42..2.44 rows=1 width=9) (actual time=0.030..0.031 rows=1 loops=462,590)

  • Index Cond: (physicalconfigid = COALESCE(pc_dgo.physicalconfigid, pc_usr.physicalconfigid))
Planning time : 227.308 ms
Execution time : 276,075.772 ms