explain.depesz.com

PostgreSQL's explain analyze made readable

Result: hNn

Settings
# exclusive inclusive rows x rows loops node
1. 6,216.918 654,442.502 ↓ 1.6 28,067 1

Aggregate (cost=8,254,656,838.64..8,926,546,474.31 rows=17,294 width=308) (actual time=641,746.212..654,442.502 rows=28,067 loops=1)

  • Buffers: shared hit=17472 read=3769013, temp read=1480382 written=1482460
2. 63,218.531 648,225.584 ↑ 1,283.4 9,972,084 1

Sort (cost=8,254,656,838.64..8,286,651,558.49 rows=12,797,887,940 width=190) (actual time=641,744.486..648,225.584 rows=9,972,084 loops=1)

  • Sort Key: ou1.name, ou2.name, ou3.name, ou4.name, ou.name, ou3.code, ou4.code, ou.created, ou.code, ou.organisationunitid, av.value, ("substring"(ou5.comment, ("position"(ou5.comment, ':'::text) 1))), ("substring"(ou5.comment, 1, ("position"(ou5.comment, ':'::text) - 1))), ou5.description
  • Sort Method: external merge Disk: 1436896kB
  • Buffers: shared hit=17472 read=3769013, temp read=1480382 written=1482460
3. 2,472.119 585,007.053 ↑ 1,283.4 9,972,084 1

Merge Join (cost=8,584,812.47..200,924,012.16 rows=12,797,887,940 width=190) (actual time=555,325.507..585,007.053 rows=9,972,084 loops=1)

  • Buffers: shared hit=17466 read=3769013, temp read=808737 written=810087
4. 37.293 2,912.641 ↑ 6.2 28,067 1

Sort (cost=94,595.23..95,027.58 rows=172,937 width=180) (actual time=2,900.129..2,912.641 rows=28,067 loops=1)

  • Sort Key: ou.organisationunitid
  • Sort Method: external merge Disk: 3784kB
  • Buffers: shared hit=4804 read=2807, temp read=4466 written=4481
5. 119.642 2,875.348 ↑ 6.2 28,067 1

Unique (cost=55,966.8..62,451.94 rows=172,937 width=180) (actual time=1,923.042..2,875.348 rows=28,067 loops=1)

  • Buffers: shared hit=4804 read=2807, temp read=3993 written=4007
6. 1,987.335 2,755.706 ↓ 1.2 204,891 1

Sort (cost=55,966.8..56,399.15 rows=172,937 width=180) (actual time=1,923.04..2,755.706 rows=204,891 loops=1)

  • Sort Key: ou1.name, ou2.name, ou3.name, ou3.code, ou4.name, ou4.code, ou.name, ou.created, ou.code, ou.organisationunitid, av.value, ("substring"(ou5.comment, 1, ("position"(ou5.comment, ':'::text) - 1))), ("substring"(ou5.comment, ("position"(ou5.comment, ':'::text) 1))), ou5.description
  • Sort Method: external merge Disk: 27496kB
  • Buffers: shared hit=4804 read=2807, temp read=3993 written=4007
7. 147.318 768.371 ↓ 1.2 204,891 1

Hash Join (cost=13,674.88..25,552.88 rows=172,937 width=180) (actual time=441.32..768.371 rows=204,891 loops=1)

  • Buffers: shared hit=4804 read=2807, temp read=556 written=556
8. 126.531 604.182 ↑ 1.0 171,531 1

Hash Join (cost=13,668.29..20,574.36 rows=172,937 width=130) (actual time=424.422..604.182 rows=171,531 loops=1)

  • Buffers: shared hit=4802 read=2806, temp read=556 written=556
9. 53.559 53.559 ↑ 1.0 179,105 1

Seq Scan on orgunitgroupmembers ogm (cost=0..2,584.05 rows=179,105 width=8) (actual time=0.009..53.559 rows=179,105 loops=1)

  • Buffers: shared hit=3 read=790
10. 15.261 424.092 ↑ 1.0 28,067 1

Hash (cost=12,796.46..12,796.46 rows=28,067 width=126) (actual time=424.092..424.092 rows=28,067 loops=1)

  • Buffers: shared hit=4799 read=2016, temp written=250
11. 9.664 408.831 ↑ 1.0 28,067 1

Hash Join (cost=10,641.85..12,796.46 rows=28,067 width=126) (actual time=327.959..408.831 rows=28,067 loops=1)

  • Buffers: shared hit=4799 read=2016
12. 11.184 351.443 ↑ 1.0 28,067 1

Hash Join (cost=10,038.88..12,119.78 rows=28,067 width=120) (actual time=280.098..351.443 rows=28,067 loops=1)

  • Buffers: shared hit=4797 read=1863
13. 10.470 333.183 ↑ 1.0 28,067 1

Hash Join (cost=9,544.18..11,307.65 rows=28,067 width=120) (actual time=272.864..333.183 rows=28,067 loops=1)

  • Buffers: shared hit=4794 read=1784
14. 9.597 310.654 ↑ 1.0 28,067 1

Hash Join (cost=7,932.15..9,621.94 rows=28,067 width=104) (actual time=260.643..310.654 rows=28,067 loops=1)

  • Buffers: shared hit=3836 read=1784
15. 7.666 290.428 ↑ 1.0 28,067 1

Hash Join (cost=6,320.12..7,936.22 rows=28,067 width=88) (actual time=249.845..290.428 rows=28,067 loops=1)

  • Buffers: shared hit=2878 read=1784
16. 6.837 267.984 ↑ 1.0 28,067 1

Hash Join (cost=4,708.09..6,250.5 rows=28,067 width=72) (actual time=234.889..267.984 rows=28,067 loops=1)

  • Buffers: shared hit=1920 read=1784
17. 6.270 239.203 ↑ 1.0 28,067 1

Hash Join (cost=3,096.06..4,564.78 rows=28,067 width=62) (actual time=212.731..239.203 rows=28,067 loops=1)

  • Buffers: shared hit=962 read=1784
18. 12.073 102.558 ↑ 1.0 28,067 1

Hash Join (cost=1,484.03..2,879.07 rows=28,067 width=52) (actual time=82.162..102.558 rows=28,067 loops=1)

  • Buffers: shared hit=959 read=829
19. 18.978 18.978 ↑ 1.0 28,067 1

Seq Scan on organisationunit ou (cost=0..1,321.35 rows=28,067 width=32) (actual time=10.2..18.978 rows=28,067 loops=1)

  • Filter: (ou.hierarchylevel = 6)
  • Buffers: shared hit=957 read=1
20. 11.534 71.507 ↑ 1.0 29,068 1

Hash (cost=1,120.68..1,120.68 rows=29,068 width=24) (actual time=71.506..71.507 rows=29,068 loops=1)

  • Buffers: shared hit=2 read=828
21. 59.973 59.973 ↑ 1.0 29,068 1

Seq Scan on _orgunitstructure os (cost=0..1,120.68 rows=29,068 width=24) (actual time=0.025..59.973 rows=29,068 loops=1)

  • Buffers: shared hit=2 read=828
22. 11.258 130.375 ↑ 1.0 29,068 1

Hash (cost=1,248.68..1,248.68 rows=29,068 width=18) (actual time=130.375..130.375 rows=29,068 loops=1)

  • Buffers: shared hit=3 read=955
23. 119.117 119.117 ↑ 1.0 29,068 1

Seq Scan on organisationunit ou1 (cost=0..1,248.68 rows=29,068 width=18) (actual time=0.009..119.117 rows=29,068 loops=1)

  • Buffers: shared hit=3 read=955
24. 11.364 21.944 ↑ 1.0 29,068 1

Hash (cost=1,248.68..1,248.68 rows=29,068 width=18) (actual time=21.944..21.944 rows=29,068 loops=1)

  • Buffers: shared hit=958
25. 10.580 10.580 ↑ 1.0 29,068 1

Seq Scan on organisationunit ou2 (cost=0..1,248.68 rows=29,068 width=18) (actual time=0.009..10.58 rows=29,068 loops=1)

  • Buffers: shared hit=958
26. 7.984 14.778 ↑ 1.0 29,068 1

Hash (cost=1,248.68..1,248.68 rows=29,068 width=24) (actual time=14.777..14.778 rows=29,068 loops=1)

  • Buffers: shared hit=958
27. 6.794 6.794 ↑ 1.0 29,068 1

Seq Scan on organisationunit ou3 (cost=0..1,248.68 rows=29,068 width=24) (actual time=0.011..6.794 rows=29,068 loops=1)

  • Buffers: shared hit=958
28. 5.448 10.629 ↑ 1.0 29,068 1

Hash (cost=1,248.68..1,248.68 rows=29,068 width=24) (actual time=10.629..10.629 rows=29,068 loops=1)

  • Buffers: shared hit=958
29. 5.181 5.181 ↑ 1.0 29,068 1

Seq Scan on organisationunit ou4 (cost=0..1,248.68 rows=29,068 width=24) (actual time=0.009..5.181 rows=29,068 loops=1)

  • Buffers: shared hit=958
30. 5.416 12.059 ↑ 1.0 29,068 1

Hash (cost=1,248.68..1,248.68 rows=29,068 width=20) (actual time=12.059..12.059 rows=29,068 loops=1)

  • Buffers: shared hit=958
31. 6.643 6.643 ↑ 1.0 29,068 1

Seq Scan on organisationunit ou5 (cost=0..1,248.68 rows=29,068 width=20) (actual time=0.007..6.643 rows=29,068 loops=1)

  • Buffers: shared hit=958
32. 3.022 7.076 ↑ 1.0 18,342 1

Hash (cost=265.42..265.42 rows=18,342 width=8) (actual time=7.076..7.076 rows=18,342 loops=1)

  • Buffers: shared hit=3 read=79
33. 4.054 4.054 ↑ 1.0 18,342 1

Seq Scan on organisationunitattributevalues ouv (cost=0..265.42 rows=18,342 width=8) (actual time=0.01..4.054 rows=18,342 loops=1)

  • Buffers: shared hit=3 read=79
34. 6.264 47.724 ↑ 1.0 17,314 1

Hash (cost=386.55..386.55 rows=17,314 width=14) (actual time=47.724..47.724 rows=17,314 loops=1)

  • Buffers: shared hit=2 read=153
35. 41.460 41.460 ↑ 1.0 17,314 1

Seq Scan on attributevalue av (cost=0..386.55 rows=17,314 width=14) (actual time=0.008..41.46 rows=17,314 loops=1)

  • Filter: (av.attributeid = 4393575)
  • Buffers: shared hit=2 read=153
36. 0.042 16.871 ↓ 1.2 97 1

Hash (cost=5.56..5.56 rows=82 width=4) (actual time=16.871..16.871 rows=97 loops=1)

  • Buffers: shared hit=2 read=1
37. 0.056 16.829 ↓ 1.2 97 1

Hash Join (cost=3.85..5.56 rows=82 width=4) (actual time=16.784..16.829 rows=97 loops=1)

  • Buffers: shared hit=2 read=1
38. 16.745 16.745 ↑ 1.0 56 1

Seq Scan on orgunitgroupsetmembers ogs (cost=0..1.56 rows=56 width=8) (actual time=16.738..16.745 rows=56 loops=1)

  • Buffers: shared read=1
39. 0.013 0.028 ↑ 1.0 82 1

Hash (cost=2.82..2.82 rows=82 width=4) (actual time=0.028..0.028 rows=82 loops=1)

  • Buffers: shared hit=2
40. 0.015 0.015 ↑ 1.0 82 1

Seq Scan on orgunitgroup og (cost=0..2.82 rows=82 width=4) (actual time=0.006..0.015 rows=82 loops=1)

  • Buffers: shared hit=2
41. 1,127.010 579,622.293 ↑ 1.5 10,154,304 1

Materialize (cost=8,490,217.24..8,897,234.73 rows=14,800,636 width=14) (actual time=552,420.214..579,622.293 rows=10,154,304 loops=1)

  • Buffers: shared hit=12662 read=3766206, temp read=804271 written=805606
42. 8,653.265 578,495.283 ↑ 1.5 10,154,304 1

Unique (cost=8,490,217.24..8,712,226.78 rows=14,800,636 width=18) (actual time=552,420.209..578,495.283 rows=10,154,304 loops=1)

  • Buffers: shared hit=12662 read=3766206, temp read=804271 written=805606
43. 188,912.927 569,842.018 ↓ 3.5 51,669,297 1

Sort (cost=8,490,217.24..8,527,218.83 rows=14,800,636 width=18) (actual time=552,420.208..569,842.018 rows=51,669,297 loops=1)

  • Sort Key: ou9.organisationunitid, dv.value, dv.periodid, dv.dataelementid, dv.categoryoptioncomboid
  • Sort Method: external merge Disk: 1526400kB
  • Buffers: shared hit=12662 read=3766206, temp read=804271 written=805606
44. 11,287.040 380,929.091 ↓ 3.5 51,669,297 1

Hash Join (cost=2,871.22..6,120,462.96 rows=14,800,636 width=18) (actual time=169.685..380,929.091 rows=51,669,297 loops=1)

  • Buffers: shared hit=12662 read=3766206
45. 0.000 369,549.377 ↓ 2.3 10,154,304 1

Gather (cost=2,618.62..5,916,846.72 rows=4,428,582 width=18) (actual time=76.989..369,549.377 rows=10,154,304 loops=1)

  • Buffers: shared hit=12643 read=3766183
46. 1,272.962 382,596.863 ↓ 1.8 3,384,768 3

Hash Join (cost=1,618.62..5,472,988.52 rows=1,845,242 width=18) (actual time=51.112..382,596.863 rows=3,384,768 loops=3)

  • Buffers: shared hit=12643 read=3766183
47. 29,337.311 381,308.811 ↓ 1.8 3,384,768 3

Hash Join (cost=6.59..5,466,532.02 rows=1,845,242 width=18) (actual time=35.863..381,308.811 rows=3,384,768 loops=3)

  • Buffers: shared hit=9769 read=3766183
48. 351,971.422 351,971.422 ↑ 1.3 106,876,436 3

Seq Scan on datavalue dv (cost=0..5,111,731.47 rows=133,595,547 width=18) (actual time=20.256..351,971.422 rows=106,876,436 loops=3)

  • Buffers: shared hit=9595 read=3766181
49. 0.005 0.078 ↑ 1.0 5 3

Hash (cost=6.53..6.53 rows=5 width=4) (actual time=0.078..0.078 rows=5 loops=3)

  • Buffers: shared hit=6
50. 0.073 0.073 ↑ 1.0 5 3

Seq Scan on period p (cost=0..6.53 rows=5 width=4) (actual time=0.064..0.073 rows=5 loops=3)

  • Filter: (p.startdate = '2019-01-01'::date)
  • Buffers: shared hit=6
51. 7.577 15.090 ↑ 1.0 29,068 3

Hash (cost=1,248.68..1,248.68 rows=29,068 width=4) (actual time=15.09..15.09 rows=29,068 loops=3)

  • Buffers: shared hit=2874
52. 7.513 7.513 ↑ 1.0 29,068 3

Seq Scan on organisationunit ou9 (cost=0..1,248.68 rows=29,068 width=4) (actual time=0.03..7.513 rows=29,068 loops=3)

  • Buffers: shared hit=2874
53. 2.501 92.674 ↑ 1.0 9,360 1

Hash (cost=135.6..135.6 rows=9,360 width=8) (actual time=92.673..92.674 rows=9,360 loops=1)

  • Buffers: shared hit=19 read=23
54. 90.173 90.173 ↑ 1.0 9,360 1

Seq Scan on dataelementgroupmembers dsm (cost=0..135.6 rows=9,360 width=8) (actual time=20.748..90.173 rows=9,360 loops=1)

  • Buffers: shared hit=19 read=23
Planning time : 1,215.476 ms
Execution time : 654,899.42 ms