explain.depesz.com

PostgreSQL's explain analyze made readable

Result: lHnj : Optimization for: plan #IY3U

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 3,539.663 474,705.888 ↑ 395.4 1,819,346 1

Sort (cost=300,109,027.57..301,907,433.35 rows=719,362,311 width=442) (actual time=474,024.661..474,705.888 rows=1,819,346 loops=1)

  • Sort Key: sdl_0._gobid
  • Sort Method: external merge Disk: 987672kB
2. 8,642.600 471,166.225 ↑ 395.4 1,819,346 1

Hash Left Join (cost=2,058,598.68..24,347,823.05 rows=719,362,311 width=442) (actual time=458,535.854..471,166.225 rows=1,819,346 loops=1)

  • Hash Cond: (((rel_3.dst_id)::text = (zrt_0._id)::text) AND ((rel_3.dst_volgnummer)::text = (zrt_0.volgnummer)::text))
3. 870.913 357,449.706 ↑ 395.4 1,819,346 1

Merge Right Join (cost=1,578,512.89..12,897,456.94 rows=719,362,311 width=474) (actual time=353,456.169..357,449.706 rows=1,819,346 loops=1)

  • Merge Cond: ((rel_0.src_id)::text = (sdl_0._id)::text)
4. 5,293.366 240,801.592 ↑ 1.0 2,612,975 1

Sort (cost=1,060,926.02..1,067,459.34 rows=2,613,330 width=49) (actual time=240,304.702..240,801.592 rows=2,612,975 loops=1)

  • Sort Key: rel_0.src_id
  • Sort Method: external merge Disk: 85120kB
5. 1,865.555 235,508.226 ↑ 1.0 2,613,330 1

Hash Left Join (cost=680,420.90..730,060.21 rows=2,613,330 width=49) (actual time=232,477.916..235,508.226 rows=2,613,330 loops=1)

  • Hash Cond: ((rel_0.dst_id)::text = (tng_0._id)::text)
6. 1,172.719 1,172.719 ↑ 1.0 2,613,330 1

Seq Scan on mv_brk_sdl_brk_tng_is_bron_voor_tenaamstelling rel_0 (cost=0.00..42,779.30 rows=2,613,330 width=13) (actual time=2.340..1,172.719 rows=2,613,330 loops=1)

7. 847.292 232,469.952 ↓ 1.0 943,878 1

Hash (cost=668,963.69..668,963.69 rows=916,577 width=50) (actual time=232,469.951..232,469.952 rows=943,878 loops=1)

  • Buckets: 1048576 Batches: 1 Memory Usage: 84523kB
8. 222,133.110 231,622.660 ↓ 1.0 943,878 1

Bitmap Heap Scan on brk_tenaamstellingen tng_0 (cost=13,293.34..668,963.69 rows=916,577 width=50) (actual time=9,630.730..231,622.660 rows=943,878 loops=1)

  • Recheck Cond: ((_expiration_date IS NULL) OR (_expiration_date > now()))
  • Heap Blocks: exact=388334
9. 0.005 9,489.550 ↓ 0.0 0 1

BitmapOr (cost=13,293.34..13,293.34 rows=923,332 width=0) (actual time=9,489.550..9,489.550 rows=0 loops=1)

10. 9,356.470 9,356.470 ↓ 1.0 931,006 1

Bitmap Index Scan on brk_tng_1a9d849ff5a68997176b6144236806ae (cost=0.00..12,572.39 rows=904,501 width=0) (actual time=9,356.470..9,356.470 rows=931,006 loops=1)

  • Index Cond: (_expiration_date IS NULL)
11. 133.075 133.075 ↑ 1.5 12,872 1

Bitmap Index Scan on brk_tng_1a9d849ff5a68997176b6144236806ae (cost=0.00..262.67 rows=18,831 width=0) (actual time=133.075..133.075 rows=12,872 loops=1)

  • Index Cond: (_expiration_date > now())
12. 796.288 115,777.201 ↑ 15.1 1,819,346 1

Materialize (cost=517,472.94..1,101,845.13 rows=27,526,657 width=437) (actual time=113,151.432..115,777.201 rows=1,819,346 loops=1)

13. 606.383 114,980.913 ↑ 15.1 1,819,306 1

Merge Left Join (cost=517,472.94..1,033,028.49 rows=27,526,657 width=437) (actual time=113,151.425..114,980.913 rows=1,819,306 loops=1)

  • Merge Cond: ((sdl_0._id)::text = (rel_3.src_id)::text)
14. 192.224 113,804.555 ↑ 10.0 391,304 1

Merge Left Join (cost=517,449.57..586,795.01 rows=3,904,579 width=399) (actual time=113,151.358..113,804.555 rows=391,304 loops=1)

  • Merge Cond: ((sdl_0._id)::text = (rel_2.src_id)::text)
15. 215.196 48,896.981 ↑ 3.6 161,609 1

Merge Left Join (cost=219,035.32..228,343.96 rows=587,243 width=357) (actual time=48,539.414..48,896.981 rows=161,609 loops=1)

  • Merge Cond: ((sdl_0._id)::text = (rel_1.src_id)::text)
16. 420.322 16,824.059 ↑ 1.0 100,000 1

Sort (cost=22,287.01..22,537.01 rows=100,000 width=316) (actual time=16,784.644..16,824.059 rows=100,000 loops=1)

  • Sort Key: sdl_0._id
  • Sort Method: quicksort Memory: 54635kB
17. 22.422 16,403.737 ↑ 1.0 100,000 1

Subquery Scan on sdl_0 (cost=0.42..13,982.19 rows=100,000 width=316) (actual time=3.910..16,403.737 rows=100,000 loops=1)

18. 14.026 16,381.315 ↑ 1.0 100,000 1

Limit (cost=0.42..12,982.19 rows=100,000 width=908) (actual time=3.907..16,381.315 rows=100,000 loops=1)

19. 16,367.289 16,367.289 ↑ 8.9 100,000 1

Index Scan using tmp_brk_stukdelen_random_pkey on tmp_brk_stukdelen_random (cost=0.42..115,836.16 rows=892,296 width=908) (actual time=3.904..16,367.289 rows=100,000 loops=1)

  • Filter: ((_expiration_date IS NULL) OR (_expiration_date > now()))
20. 1,109.330 31,857.726 ↑ 1.0 587,171 1

Sort (cost=196,748.31..198,216.42 rows=587,243 width=47) (actual time=31,754.754..31,857.726 rows=587,171 loops=1)

  • Sort Key: rel_1.src_id
  • Sort Method: quicksort Memory: 96032kB
21. 451.042 30,748.396 ↑ 1.0 587,243 1

Hash Left Join (cost=124,983.91..140,479.87 rows=587,243 width=47) (actual time=29,642.370..30,748.396 rows=587,243 loops=1)

  • Hash Cond: ((rel_1.dst_id)::text = (art_0._id)::text)
22. 664.261 664.261 ↑ 1.0 587,243 1

Seq Scan on mv_brk_sdl_brk_art_is_bron_voor_aantekening_recht rel_1 (cost=0.00..13,954.43 rows=587,243 width=43) (actual time=2.590..664.261 rows=587,243 loops=1)

23. 342.330 29,633.093 ↓ 1.0 563,447 1

Hash (cost=118,187.41..118,187.41 rows=543,720 width=78) (actual time=29,633.093..29,633.093 rows=563,447 loops=1)

  • Buckets: 1048576 Batches: 1 Memory Usage: 69566kB
24. 29,290.763 29,290.763 ↓ 1.0 563,447 1

Seq Scan on brk_aantekeningenrechten art_0 (cost=0.00..118,187.41 rows=543,720 width=78) (actual time=4.427..29,290.763 rows=563,447 loops=1)

  • Filter: ((_expiration_date IS NULL) OR (_expiration_date > now()))
  • Rows Removed by Filter: 4426
25. 849.335 64,715.350 ↓ 1.2 775,920 1

Sort (cost=298,414.25..300,076.50 rows=664,900 width=48) (actual time=64,611.935..64,715.350 rows=775,920 loops=1)

  • Sort Key: rel_2.src_id
  • Sort Method: quicksort Memory: 93374kB
26. 478.244 63,866.015 ↑ 1.0 664,900 1

Hash Left Join (cost=215,399.46..234,109.19 rows=664,900 width=48) (actual time=62,531.437..63,866.015 rows=664,900 loops=1)

  • Hash Cond: (((rel_2.dst_id)::text = (akt_0._id)::text) AND ((rel_2.dst_volgnummer)::text = (akt_0.volgnummer)::text))
27. 860.764 860.764 ↑ 1.0 664,900 1

Seq Scan on mv_brk_sdl_brk_akt_is_bron_voor_aantekening_kadastraal_object rel_2 (cost=0.00..15,219.00 rows=664,900 width=46) (actual time=3.598..860.764 rows=664,900 loops=1)

28. 342.188 62,527.007 ↑ 1.0 424,427 1

Hash (cost=208,853.64..208,853.64 rows=436,388 width=82) (actual time=62,527.007..62,527.007 rows=424,427 loops=1)

  • Buckets: 524288 Batches: 1 Memory Usage: 51652kB
29. 59,969.331 62,184.819 ↑ 1.0 424,427 1

Bitmap Heap Scan on brk_aantekeningenkadastraleobjecten akt_0 (cost=6,273.25..208,853.64 rows=436,388 width=82) (actual time=2,238.614..62,184.819 rows=424,427 loops=1)

  • Recheck Cond: ((_expiration_date IS NULL) OR (_expiration_date > now()))
  • Heap Blocks: exact=98969
30. 0.003 2,215.488 ↓ 0.0 0 1

BitmapOr (cost=6,273.25..6,273.25 rows=439,093 width=0) (actual time=2,215.488..2,215.488 rows=0 loops=1)

31. 2,215.450 2,215.450 ↑ 1.0 424,419 1

Bitmap Index Scan on brk_akt_1a9d849ff5a68997176b6144236806ae (cost=0.00..5,949.19 rows=431,488 width=0) (actual time=2,215.450..2,215.450 rows=424,419 loops=1)

  • Index Cond: (_expiration_date IS NULL)
32. 0.035 0.035 ↑ 950.6 8 1

Bitmap Index Scan on brk_akt_1a9d849ff5a68997176b6144236806ae (cost=0.00..105.87 rows=7,605 width=0) (actual time=0.034..0.035 rows=8 loops=1)

  • Index Cond: (_expiration_date > now())
33. 246.031 569.975 ↓ 3.0 2,084,406 1

Materialize (cost=0.42..25,340.79 rows=704,984 width=45) (actual time=0.057..569.975 rows=2,084,406 loops=1)

34. 323.944 323.944 ↑ 1.0 704,884 1

Index Scan using src_id_mv_brk_sdl_brk_zrt_is_bron_voor_zakelijk_recht on mv_brk_sdl_brk_zrt_is_bron_voor_zakelijk_recht rel_3 (cost=0.42..23,578.33 rows=704,984 width=45) (actual time=0.052..323.944 rows=704,884 loops=1)

35. 580.649 105,073.919 ↓ 1.0 788,683 1

Hash (cost=468,383.33..468,383.33 rows=780,164 width=68) (actual time=105,073.919..105,073.919 rows=788,683 loops=1)

  • Buckets: 1048576 Batches: 1 Memory Usage: 75267kB
36. 95,752.910 104,493.270 ↓ 1.0 788,683 1

Bitmap Heap Scan on brk_zakelijkerechten zrt_0 (cost=11,203.18..468,383.33 rows=780,164 width=68) (actual time=8,810.539..104,493.270 rows=788,683 loops=1)

  • Recheck Cond: ((_expiration_date IS NULL) OR (_expiration_date > now()))
  • Heap Blocks: exact=233170
37. 0.004 8,740.360 ↓ 0.0 0 1

BitmapOr (cost=11,203.18..11,203.18 rows=780,164 width=0) (actual time=8,740.360..8,740.360 rows=0 loops=1)

38. 8,740.335 8,740.335 ↓ 1.0 788,683 1

Bitmap Index Scan on brk_zrt_1a9d849ff5a68997176b6144236806ae (cost=0.00..10,811.56 rows=780,164 width=0) (actual time=8,740.335..8,740.335 rows=788,683 loops=1)

  • Index Cond: (_expiration_date IS NULL)
39. 0.021 0.021 ↓ 0.0 0 1

Bitmap Index Scan on brk_zrt_1a9d849ff5a68997176b6144236806ae (cost=0.00..1.54 rows=1 width=0) (actual time=0.021..0.021 rows=0 loops=1)

  • Index Cond: (_expiration_date > now())
Planning time : 257.016 ms
Execution time : 474,992.784 ms