explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 492u : new query sibelga

Settings
# exclusive inclusive rows x rows loops node
1. 122.228 18,423.834 ↓ 1.0 104,757 1

Merge Left Join (cost=1,182,386.37..1,254,153.06 rows=100,236 width=4) (actual time=15,562.226..18,423.834 rows=104,757 loops=1)

  • Merge Cond: (co.contractid = instal.contractid)
2. 88.538 15,721.646 ↓ 1.0 104,757 1

Merge Left Join (cost=1,182,384.81..1,183,010.91 rows=100,236 width=4) (actual time=15,558.606..15,721.646 rows=104,757 loops=1)

  • Merge Cond: (co.contractid = instaltemplate.contractid)
3. 364.537 15,621.604 ↓ 1.0 104,757 1

Sort (cost=1,181,722.30..1,181,972.89 rows=100,236 width=4) (actual time=15,551.705..15,621.604 rows=104,757 loops=1)

  • Sort Key: co.contractid
  • Sort Method: external merge Disk: 1,416kB
4. 437.984 15,257.067 ↓ 1.0 104,757 1

Merge Left Join (cost=1,131,374.51..1,173,396.17 rows=100,236 width=4) (actual time=11,525.061..15,257.067 rows=104,757 loops=1)

  • Merge 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.todate >= cdp.todate))))
  • Rows Removed by Join Filter: 151,779
5. 65.687 14,214.506 ↓ 1.0 104,751 1

Merge Left Join (cost=1,131,374.09..1,161,821.02 rows=100,236 width=12) (actual time=11,524.878..14,214.506 rows=104,751 loops=1)

  • Merge Cond: (dp.deliverypointid = lc_usr.deliverypointid)
  • Join Filter: (((cdp.todate IS NULL) AND (lc_usr.todate IS NULL)) OR ((lc_usr.fromdate = cdp.fromdate) AND (lc_usr.todate = cdp.todate)) OR ((cdp.todate IS NOT NULL) AND (lc_usr.fromdate < cdp.todate) AND ((lc_usr.todate IS NULL) O (...)
  • Rows Removed by Join Filter: 5
6. 438.907 14,135.047 ↓ 1.0 104,751 1

Merge Left Join (cost=1,131,371.25..1,161,567.17 rows=100,236 width=16) (actual time=11,511.118..14,135.047 rows=104,751 loops=1)

  • Merge Cond: (dp.deliverypointid = lc_dgo.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) OR (lc_dgo.todate >= cdp.todate))))
  • Rows Removed by Join Filter: 151,779
7. 495.025 12,915.348 ↓ 1.0 104,751 1

Merge Left Join (cost=1,131,370.83..1,146,260.22 rows=100,236 width=16) (actual time=11,510.223..12,915.348 rows=104,751 loops=1)

  • Merge 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.todate))))
  • Rows Removed by Join Filter: 179,552
8. 75.531 11,651.811 ↓ 1.0 104,748 1

Merge Left Join (cost=1,131,370.41..1,131,874.19 rows=100,236 width=35) (actual time=11,510.083..11,651.811 rows=104,748 loops=1)

  • Merge Cond: (dp.deliverypointid = gc_usr.deliverypointid)
  • Join Filter: (((cdp.todate IS NULL) AND (gc_usr.todate IS NULL)) OR ((cdp.fromdate = gc_usr.fromdate) AND (cdp.todate = gc_usr.todate)) OR ((cdp.todate IS NOT NULL) AND (gc_usr.fromdate < cdp.todate) AND ((gc_usr (...)
  • Rows Removed by Join Filter: 9
9. 367.426 11,560.381 ↓ 1.0 104,748 1

Sort (cost=1,131,363.40..1,131,613.99 rows=100,236 width=16) (actual time=11,494.209..11,560.381 rows=104,748 loops=1)

  • Sort Key: dp.deliverypointid
  • Sort Method: external merge Disk: 2,472kB
10. 92.289 11,192.955 ↓ 1.0 104,748 1

Hash Left Join (cost=131,041.38..1,123,037.28 rows=100,236 width=16) (actual time=4,213.254..11,192.955 rows=104,748 loops=1)

  • Hash Cond: ((brpj.brp)::text = (brp.externalid)::text)
11. 171.883 11,100.534 ↓ 1.0 104,741 1

Hash Left Join (cost=131,037.60..1,121,279.50 rows=100,219 width=29) (actual time=4,213.065..11,100.534 rows=104,741 loops=1)

  • Hash Cond: (per.deliveryperiodid = brpj.deliveryperiodid)
  • Join Filter: (((cdp.todate IS NULL) AND (brpj.todate IS NULL)) OR ((cdp.fromdate = cdp.todate) AND (brpj.fromdate = cdp.fromdate)) OR ((cdp.todate IS NOT NULL) AND (brpj.fromdate < cdp.todate) A (...)
  • Rows Removed by Join Filter: 841
12. 145.649 10,875.927 ↓ 1.0 104,741 1

Nested Loop Left Join (cost=129,883.67..1,118,920.20 rows=100,219 width=20) (actual time=4,159.667..10,875.927 rows=104,741 loops=1)

13. 262.404 6,540.638 ↓ 1.0 104,741 1

Hash Left Join (cost=129,882.83..396,921.44 rows=100,219 width=24) (actual time=4,154.707..6,540.638 rows=104,741 loops=1)

  • Hash Cond: (((dp.gridoperator)::text = (dgo_encom.gridoperator)::text) AND ((dp.regulator)::text = (dgo_hgz.regulator)::text) AND ((dp.market)::text = (dgo_hgz.marketcode)::text))
14. 208.485 6,277.659 ↓ 1.0 104,741 1

Hash Left Join (cost=129,873.14..395,724.95 rows=100,219 width=44) (actual time=4,154.008..6,277.659 rows=104,741 loops=1)

  • Hash Cond: (per.deliverypointid = dp.deliverypointid)
15. 201.459 5,944.175 ↓ 1.0 104,741 1

Hash Left Join (cost=125,531.72..388,375.52 rows=100,219 width=20) (actual time=4,028.089..5,944.175 rows=104,741 loops=1)

  • Hash Cond: (cdp.deliveryperiodid = per.deliveryperiodid)
16. 210.404 5,614.417 ↓ 1.0 104,741 1

Hash Right Join (cost=121,633.30..381,761.15 rows=100,219 width=16) (actual time=3,898.134..5,614.417 rows=104,741 loops=1)

  • Hash Cond: (cj.contractid = co.contractid)
17. 249.392 1,508.756 ↓ 190.9 100,230 1

Seq Scan on contractjournal cj (cost=0.00..259,624.64 rows=525 width=4) (actual time=0.584..1,508.756 rows=100,230 loops=1)

  • Filter: (journalentry = (SubPlan 9))
  • Rows Removed by Filter: 4,717
18.          

SubPlan (for Seq Scan)

19. 209.894 1,259.364 ↑ 1.0 1 104,947

Result (cost=2.44..2.45 rows=1 width=4) (actual time=0.012..0.012 rows=1 loops=104,947)

20.          

Initplan (for Result)

21. 1,049.470 1,049.470 ↑ 1.0 1 104,947

Limit (cost=0.42..2.44 rows=1 width=4) (actual time=0.010..0.010 rows=1 loops=104,947)

  • -> Index Only Scan Backward using contractjournal_pkey on contractjournal cj2 (cost=0.42..2.44 rows=1 width=4) (actual time=0.009..0.009 row (...)
  • Index Cond: ((contractid = cj.contractid) AND (journalentry IS NOT NULL))
  • Heap Fetches: 104,951
22. 80.282 3,895.257 ↓ 1.0 104,741 1

Hash (cost=119,890.56..119,890.56 rows=100,219 width=16) (actual time=3,895.256..3,895.257 rows=104,741 loops=1)

  • Buckets: 131,072 Batches: 2 Memory Usage: 3,468kB
23. 0.000 3,814.975 ↓ 1.0 104,741 1

Hash Right Join (cost=8,082.99..119,890.56 rows=100,219 width=16) (actual time=2,265.770..3,814.975 rows=104,741 loops=1)

  • Hash Cond: ((cdp.contractid = co.contractid) AND (cdp.contractdeliveryid = (SubPlan 7)))
24. 48.915 48.915 ↓ 1.0 92,553 1

Seq Scan on contractdelivery cdp (cost=0.00..2,055.42 rows=92,542 width=20) (actual time=0.030..48.915 rows=92,553 loops=1)

25. 1,775.760 2,263.165 ↓ 1.0 104,741 1

Hash (cost=6,187.70..6,187.70 rows=100,219 width=4) (actual time=2,263.165..2,263.165 rows=104,741 loops=1)

  • Buckets: 131,072 Batches: 2 Memory Usage: 2,919kB
26. 162.871 487.405 ↓ 1.0 104,741 1

Hash Right Join (cost=2,335.50..6,187.70 rows=100,219 width=4) (actual time=175.028..487.405 rows=104,741 loops=1)

  • Hash Cond: (co.customerid = cu.customerid)
27. 152.394 152.394 ↓ 1.0 100,230 1

Seq Scan on contract co (cost=0.00..2,474.19 rows=100,219 width=20) (actual time=1.701..152.394 rows=100,230 loops=1)

28. 46.313 172.140 ↓ 1.0 58,693 1

Hash (cost=1,601.89..1,601.89 rows=58,689 width=8) (actual time=172.140..172.140 rows=58,693 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 2,805kB
29. 125.827 125.827 ↓ 1.0 58,693 1

Seq Scan on customer cu (cost=0.00..1,601.89 rows=58,689 width=8) (actual time=1.131..125.827 rows=58,693 loops=1)

30.          

SubPlan (for Hash Right Join)

31. 393.634 2,755.438 ↑ 1.0 1 196,817

Limit (cost=2.32..2.32 rows=1 width=8) (actual time=0.013..0.014 rows=1 loops=196,817)

32. 2,361.804 2,361.804 ↑ 1.0 1 196,817

Sort (cost=2.32..2.32 rows=1 width=8) (actual time=0.012..0.012 rows=1 loops=196,817)

  • Sort Key: cdp2.todate DESC
  • Sort Method: quicksort Memory: 25kB
  • -> Index Scan using contractdelively_contract_id_idx on contractdelivery cdp2 (cost=0.29..2.31 rows=1 width=8) (actual time=0.007..0.008 (...)
  • Index Cond: (contractid = co.contractid)
33. 54.916 128.299 ↓ 1.0 91,453 1

Hash (cost=2,397.41..2,397.41 rows=91,441 width=8) (actual time=128.298..128.299 rows=91,453 loops=1)

  • Buckets: 131,072 Batches: 2 Memory Usage: 2,809kB
34. 73.383 73.383 ↓ 1.0 91,453 1

Seq Scan on deliveryperiod per (cost=0.00..2,397.41 rows=91,441 width=8) (actual time=0.018..73.383 rows=91,453 loops=1)

35. 47.144 124.999 ↓ 1.0 66,331 1

Hash (cost=3,058.30..3,058.30 rows=66,330 width=28) (actual time=124.998..124.999 rows=66,331 loops=1)

  • Buckets: 65,536 Batches: 2 Memory Usage: 2,498kB
36. 77.855 77.855 ↓ 1.0 66,331 1

Seq Scan on deliverypoint dp (cost=0.00..3,058.30 rows=66,330 width=28) (actual time=0.018..77.855 rows=66,331 loops=1)

37. 0.079 0.575 ↓ 1.2 62 1

Hash (cost=8.81..8.81 rows=50 width=40) (actual time=0.574..0.575 rows=62 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
38. 0.109 0.496 ↓ 1.2 62 1

Hash Join (cost=4.36..8.81 rows=50 width=40) (actual time=0.300..0.496 rows=62 loops=1)

  • Hash Cond: ((a.externalid)::text = (dgo_encom.gridoperator)::text)
39. 0.141 0.281 ↑ 1.0 63 1

Hash Join (cost=2.42..6.13 rows=63 width=17) (actual time=0.157..0.281 rows=63 loops=1)

  • Hash Cond: (a.actorid = dgo_hgz.gridoperatorid)
40. 0.050 0.050 ↑ 1.0 79 1

Seq Scan on actor a (cost=0.00..2.79 rows=79 width=13) (actual time=0.013..0.050 rows=79 loops=1)

41. 0.044 0.090 ↑ 1.0 63 1

Hash (cost=1.63..1.63 rows=63 width=12) (actual time=0.089..0.090 rows=63 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
42. 0.046 0.046 ↑ 1.0 63 1

Seq Scan on gridoperator dgo_hgz (cost=0.00..1.63 rows=63 width=12) (actual time=0.016..0.046 rows=63 loops=1)

43. 0.030 0.106 ↑ 1.0 42 1

Hash (cost=1.42..1.42 rows=42 width=32) (actual time=0.106..0.106 rows=42 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
44. 0.076 0.076 ↑ 1.0 42 1

Seq Scan on gridoperator dgo_encom (cost=0.00..1.42 rows=42 width=32) (actual time=0.046..0.076 rows=42 loops=1)

45. 231.421 4,189.640 ↑ 1.0 1 104,741

Nested Loop (cost=0.84..7.19 rows=1 width=4) (actual time=0.039..0.040 rows=1 loops=104,741)

46. 1,380.031 3,037.489 ↑ 1.0 1 104,741

Index Scan using freeadddetail_addressid_idx on freeaddressesdetail (cost=0.42..5.28 rows=1 width=4) (actual time=0.028..0.029 rows=1 loops=104,741)

  • Index Cond: (addressid = dp.addressid)
  • Filter: ((((addressid)::text || ';'::text) || (addressdetailnbr)::text) = (SubPlan 11))
  • Rows Removed by Filter: 0
47.          

SubPlan (for Index Scan)

48. 183.998 1,657.458 ↑ 1.0 1 92,081

Limit (cost=4.76..4.76 rows=1 width=36) (actual time=0.018..0.018 rows=1 loops=92,081)

49.          

Initplan (for Limit)

50. 0.164 0.164 ↑ 1.0 1 1

Index Scan using property_pkey on property (cost=0.27..2.29 rows=1 width=8) (actual time=0.162..0.164 rows=1 loops=1)

  • Index Cond: ((propertycode)::text = 'defaultCustomerLanguageCode'::text)
51. 1,473.296 1,473.296 ↑ 1.0 1 92,081

Sort (cost=2.47..2.47 rows=1 width=36) (actual time=0.016..0.016 rows=1 loops=92,081)

  • Sort Key: (CASE WHEN ((freeaddressesdetail_1.language)::text = ($9)::text) THEN 1 WHEN ((freeaddressesdetail_1.language)::text = 'NL'::text) THEN 2 WHEN ((freeadd (...)
  • Sort Method: quicksort Memory: 25kB
  • -> Index Scan using freeadddetail_addressid_idx on freeaddressesdetail freeaddressesdetail_1 (cost=0.42..2.46 rows=1 width=36) (actual time=0.011..0.012 rows=1 (...)
  • Index Cond: (addressid = freeaddressesdetail.addressid)
52. 920.730 920.730 ↑ 1.0 1 92,073

Index Scan using freeaddresses_pkey on freeaddresses (cost=0.42..1.91 rows=1 width=16) (actual time=0.010..0.010 rows=1 loops=92,073)

  • Index Cond: (addressid = freeaddressesdetail.addressid)
53. 23.443 52.724 ↓ 1.0 32,142 1

Hash (cost=752.30..752.30 rows=32,130 width=25) (actual time=52.724..52.724 rows=32,142 loops=1)

  • Buckets: 32,768 Batches: 1 Memory Usage: 2,057kB
54. 29.281 29.281 ↓ 1.0 32,142 1

Seq Scan on deliveryperiodbrpjournal brpj (cost=0.00..752.30 rows=32,130 width=25) (actual time=0.029..29.281 rows=32,142 loops=1)

55. 0.064 0.132 ↑ 1.0 78 1

Hash (cost=2.79..2.79 rows=79 width=9) (actual time=0.132..0.132 rows=78 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
56. 0.068 0.068 ↑ 1.0 79 1

Seq Scan on actor brp (cost=0.00..2.79 rows=79 width=9) (actual time=0.016..0.068 rows=79 loops=1)

57. 0.124 15.899 ↑ 1.6 44 1

Sort (cost=7.01..7.18 rows=69 width=31) (actual time=15.868..15.899 rows=44 loops=1)

  • Sort Key: gc_usr.deliverypointid
  • Sort Method: quicksort Memory: 26kB
58. 15.775 15.775 ↑ 1.9 36 1

Index Scan using generalconfig_sourceid_id on generalconfig gc_usr (cost=0.42..4.90 rows=69 width=31) (actual time=1.227..15.775 rows=36 loops=1)

  • Index Cond: ((sourceid)::text = 'USER'::text)
  • Filter: ((NOT deleted) AND ((status)::text = 'ACTUAL'::text))
  • Rows Removed by Filter: 11
59. 121.040 768.512 ↓ 1.4 270,225 1

Materialize (cost=0.42..8,940.48 rows=187,385 width=31) (actual time=0.102..768.512 rows=270,225 loops=1)

60. 647.472 647.472 ↑ 1.0 187,374 1

Index Scan using "IX_generalconfig_deliverypointid_generalconfig" on generalconfig gc_dgo (cost=0.42..8,472.02 rows=187,385 width=31) (actual time=0.070..647.472 rows=187,374 loops=1)

  • Filter: ((NOT deleted) AND ((sourceid)::text = 'DGO'::text) AND ((status)::text = 'ACTUAL'::text))
  • Rows Removed by Filter: 2,833
61. 97.833 780.792 ↓ 1.4 237,350 1

Materialize (cost=0.42..10,487.76 rows=164,792 width=12) (actual time=0.849..780.792 rows=237,350 loops=1)

62. 682.959 682.959 ↓ 1.0 164,899 1

Index Scan using "IX_logicalconfig_deliverypointid_logicalconfig" on logicalconfig lc_dgo (cost=0.42..10,075.78 rows=164,792 width=12) (actual time=0.844..682.959 rows=164,899 loops=1)

  • Filter: ((NOT deleted) AND ((sourceid)::text = 'DGO'::text) AND ((status)::text = 'ACTUAL'::text))
  • Rows Removed by Filter: 3,120
63. 0.089 13.772 ↓ 2.0 22 1

Sort (cost=2.84..2.87 rows=11 width=12) (actual time=13.756..13.772 rows=22 loops=1)

  • Sort Key: lc_usr.deliverypointid
  • Sort Method: quicksort Memory: 25kB
64. 13.683 13.683 ↓ 1.9 21 1

Index Scan using logicalconfig_sourceid_idx on logicalconfig lc_usr (cost=0.42..2.65 rows=11 width=12) (actual time=1.180..13.683 rows=21 loops=1)

  • Index Cond: ((sourceid)::text = 'USER'::text)
  • Filter: ((NOT deleted) AND ((status)::text = 'ACTUAL'::text))
65. 98.971 604.577 ↓ 1.4 237,361 1

Materialize (cost=0.42..6,751.45 rows=164,955 width=12) (actual time=0.162..604.577 rows=237,361 loops=1)

66. 505.606 505.606 ↑ 1.0 164,905 1

Index Scan using "IX_physicalconfig_deliverypointid_physicalconfig" on physicalconfig pc_dgo (cost=0.42..6,339.06 rows=164,955 width=12) (actual time=0.118..505.606 rows=164,905 loops=1)

  • Filter: ((NOT deleted) AND ((sourceid)::text = 'DGO'::text) AND ((status)::text = 'ACTUAL'::text))
  • Rows Removed by Filter: 25
67. 7.928 11.504 ↓ 1.0 8,330 1

Sort (cost=662.51..683.32 rows=8,327 width=8) (actual time=6.894..11.504 rows=8,330 loops=1)

  • Sort Key: instaltemplate.contractid
  • Sort Method: quicksort Memory: 775kB
68. 3.576 3.576 ↑ 1.0 8,327 1

Seq Scan on instaltemplate (cost=0.00..120.27 rows=8,327 width=8) (actual time=0.069..3.576 rows=8,327 loops=1)

69. 26.193 2,579.960 ↓ 1.0 34,337 1

Materialize (cost=1.56..70,462.30 rows=34,334 width=4) (actual time=3.615..2,579.960 rows=34,337 loops=1)

70. 52.504 2,553.767 ↑ 1.0 34,334 1

Nested Loop Left Join (cost=1.56..70,376.47 rows=34,334 width=4) (actual time=3.603..2,553.767 rows=34,334 loops=1)

71. 60.629 1,951.919 ↑ 1.0 34,334 1

Nested Loop Left Join (cost=1.14..49,496.21 rows=34,334 width=8) (actual time=2.735..1,951.919 rows=34,334 loops=1)

72. 65.678 758.268 ↑ 1.0 34,334 1

Nested Loop Left Join (cost=0.71..33,321.43 rows=34,334 width=8) (actual time=1.284..758.268 rows=34,334 loops=1)

73. 40.244 40.244 ↑ 1.0 34,334 1

Index Scan using tmp_instal_contractid on instal (cost=0.29..763.30 rows=34,334 width=8) (actual time=0.050..40.244 rows=34,334 loops=1)

74. 652.346 652.346 ↑ 1.0 1 34,334

Index Only Scan using invoice_pkey on invoice lastinstal_i (cost=0.42..0.94 rows=1 width=4) (actual time=0.019..0.019 rows=1 loops=34,334)

  • Index Cond: (invoiceid = instal.invoiceid)
  • Heap Fetches: 34,334
75. 1,133.022 1,133.022 ↑ 1.0 1 34,334

Index Only Scan using invdoc_invoiceid on invdoc lastinstal_id (cost=0.42..0.46 rows=1 width=4) (actual time=0.033..0.033 rows=1 loops=34,334)

  • Index Cond: (invoiceid = lastinstal_i.invoiceid)
  • Heap Fetches: 34,332
76. 549.344 549.344 ↓ 0.0 0 34,334

Index Scan using index_invoicerelation_finalinvoiceid on invoicerelation lastinstal_template_cr (cost=0.42..0.60 rows=1 width=8) (actual time=0.016..0.016 rows=0 loops=34,334)

  • Index Cond: (finalinvoiceid = lastinstal_i.invoiceid)
  • Filter: ((nature)::text = 'TEMPLATED'::text)
  • Rows Removed by Filter: 0
Planning time : 285.138 ms
Execution time : 18,446.038 ms