explain.depesz.com

PostgreSQL's explain analyze made readable

Result: IY3U

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 4,661.540 438,605.860 ↑ 395.4 1,819,346 1

Sort (cost=558,798,863.37..560,597,269.15 rows=719,362,311 width=442) (actual time=438,009.461..438,605.860 rows=1,819,346 loops=1)

  • Sort Key: sdl_0._gobid
  • Sort Method: external merge Disk: 987736kB
2. 10,118.213 433,944.320 ↑ 395.4 1,819,346 1

Hash Left Join (cost=2,285,632.65..113,102,359.46 rows=719,362,311 width=442) (actual time=418,504.672..433,944.320 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. 849.636 314,437.402 ↑ 395.4 1,819,346 1

Merge Right Join (cost=1,780,259.64..13,102,363.38 rows=719,362,311 width=474) (actual time=309,096.575..314,437.402 rows=1,819,346 loops=1)

  • Merge Cond: ((rel_0.src_id)::text = (sdl_0._id)::text)
4. 6,127.478 235,050.687 ↑ 1.0 2,612,975 1

Sort (cost=1,178,641.30..1,185,174.62 rows=2,613,330 width=49) (actual time=233,699.773..235,050.687 rows=2,612,975 loops=1)

  • Sort Key: rel_0.src_id
  • Sort Method: external merge Disk: 85208kB
5. 2,035.334 228,923.209 ↑ 1.0 2,613,330 1

Hash Left Join (cost=711,345.13..795,457.44 rows=2,613,330 width=49) (actual time=224,937.035..228,923.209 rows=2,613,330 loops=1)

  • Hash Cond: ((rel_0.dst_id)::text = (tng_0._id)::text)
6. 1,999.223 1,999.223 ↑ 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=4.046..1,999.223 rows=2,613,330 loops=1)

7. 1,117.389 224,888.652 ↓ 1.0 943,878 1

Hash (cost=690,936.92..690,936.92 rows=916,577 width=50) (actual time=224,888.651..224,888.652 rows=943,878 loops=1)

  • Buckets: 131072 Batches: 16 Memory Usage: 5823kB
8. 215,994.922 223,771.263 ↓ 1.0 943,878 1

Bitmap Heap Scan on brk_tenaamstellingen tng_0 (cost=13,293.34..690,936.92 rows=916,577 width=50) (actual time=7,804.288..223,771.263 rows=943,878 loops=1)

  • Recheck Cond: ((_expiration_date IS NULL) OR (_expiration_date > now()))
  • Rows Removed by Index Recheck: 822913
  • Heap Blocks: exact=70061 lossy=318273
9. 0.004 7,776.341 ↓ 0.0 0 1

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

10. 7,664.824 7,664.824 ↓ 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=7,664.824..7,664.824 rows=931,006 loops=1)

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

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

  • Index Cond: (_expiration_date > now())
12. 761.173 78,537.079 ↑ 15.1 1,819,346 1

Materialize (cost=601,504.42..1,189,036.30 rows=27,526,657 width=437) (actual time=75,396.377..78,537.079 rows=1,819,346 loops=1)

13. 552.767 77,775.906 ↑ 15.1 1,819,306 1

Merge Left Join (cost=601,504.42..1,120,219.66 rows=27,526,657 width=437) (actual time=75,396.371..77,775.906 rows=1,819,306 loops=1)

  • Merge Cond: ((sdl_0._id)::text = (rel_3.src_id)::text)
14. 197.311 76,646.146 ↑ 10.0 391,304 1

Merge Left Join (cost=601,503.99..673,979.79 rows=3,904,579 width=399) (actual time=75,396.280..76,646.146 rows=391,304 loops=1)

  • Merge Cond: ((sdl_0._id)::text = (rel_2.src_id)::text)
15. 169.996 45,738.488 ↑ 3.6 161,609 1

Merge Left Join (cost=262,147.77..272,924.52 rows=587,243 width=357) (actual time=44,998.414..45,738.488 rows=161,609 loops=1)

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

Sort (cost=30,890.30..31,140.30 rows=100,000 width=316) (actual time=20,182.277..20,306.167 rows=100,000 loops=1)

  • Sort Key: sdl_0._id
  • Sort Method: external merge Disk: 31480kB
17. 23.444 19,839.129 ↑ 1.0 100,000 1

Subquery Scan on sdl_0 (cost=0.42..13,975.48 rows=100,000 width=316) (actual time=5.992..19,839.129 rows=100,000 loops=1)

18. 15.894 19,815.685 ↑ 1.0 100,000 1

Limit (cost=0.42..12,975.48 rows=100,000 width=908) (actual time=5.990..19,815.685 rows=100,000 loops=1)

19. 19,799.791 19,799.791 ↑ 8.9 100,000 1

Index Scan using tmp_brk_stukdelen_random_pkey on tmp_brk_stukdelen_random (cost=0.42..115,846.55 rows=892,837 width=908) (actual time=5.988..19,799.791 rows=100,000 loops=1)

  • Filter: ((_expiration_date IS NULL) OR (_expiration_date > now()))
20. 74.983 25,262.325 ↑ 1.0 587,171 1

Materialize (cost=231,257.47..234,193.68 rows=587,243 width=47) (actual time=24,816.121..25,262.325 rows=587,171 loops=1)

21. 1,347.600 25,187.342 ↑ 1.0 587,171 1

Sort (cost=231,257.47..232,725.58 rows=587,243 width=47) (actual time=24,816.115..25,187.342 rows=587,171 loops=1)

  • Sort Key: rel_1.src_id
  • Sort Method: external merge Disk: 34216kB
22. 829.468 23,839.742 ↑ 1.0 587,243 1

Hash Left Join (cost=131,728.97..164,406.92 rows=587,243 width=47) (actual time=21,815.252..23,839.742 rows=587,243 loops=1)

  • Hash Cond: ((rel_1.dst_id)::text = (art_0._id)::text)
23. 1,416.933 1,416.933 ↑ 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=9.055..1,416.933 rows=587,243 loops=1)

24. 337.023 21,593.341 ↓ 1.0 563,447 1

Hash (cost=118,119.12..118,119.12 rows=540,148 width=78) (actual time=21,593.341..21,593.341 rows=563,447 loops=1)

  • Buckets: 65536 Batches: 16 Memory Usage: 4361kB
25. 21,256.318 21,256.318 ↓ 1.0 563,447 1

Seq Scan on brk_aantekeningenrechten art_0 (cost=0.00..118,119.12 rows=540,148 width=78) (actual time=8.832..21,256.318 rows=563,447 loops=1)

  • Filter: ((_expiration_date IS NULL) OR (_expiration_date > now()))
  • Rows Removed by Filter: 4426
26. 94.495 30,710.347 ↓ 1.2 775,920 1

Materialize (cost=339,356.22..342,680.72 rows=664,900 width=48) (actual time=30,397.857..30,710.347 rows=775,920 loops=1)

27. 947.732 30,615.852 ↑ 1.0 664,883 1

Sort (cost=339,356.22..341,018.47 rows=664,900 width=48) (actual time=30,397.853..30,615.852 rows=664,883 loops=1)

  • Sort Key: rel_2.src_id
  • Sort Method: external merge Disk: 30568kB
28. 596.821 29,668.120 ↑ 1.0 664,900 1

Hash Left Join (cost=226,706.23..263,070.96 rows=664,900 width=48) (actual time=28,144.550..29,668.120 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))
29. 935.805 935.805 ↑ 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.968..935.805 rows=664,900 loops=1)

30. 291.763 28,135.494 ↑ 1.0 424,427 1

Hash (cost=214,193.41..214,193.41 rows=436,388 width=82) (actual time=28,135.493..28,135.494 rows=424,427 loops=1)

  • Buckets: 65536 Batches: 16 Memory Usage: 3505kB
31. 27,843.731 27,843.731 ↑ 1.0 424,427 1

Seq Scan on brk_aantekeningenkadastraleobjecten akt_0 (cost=0.00..214,193.41 rows=436,388 width=82) (actual time=8.055..27,843.731 rows=424,427 loops=1)

  • Filter: ((_expiration_date IS NULL) OR (_expiration_date > now()))
  • Rows Removed by Filter: 778928
32. 209.862 576.993 ↓ 3.0 2,084,406 1

Materialize (cost=0.42..25,341.03 rows=704,984 width=45) (actual time=0.066..576.993 rows=2,084,406 loops=1)

33. 367.131 367.131 ↑ 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.57 rows=704,984 width=45) (actual time=0.064..367.131 rows=704,884 loops=1)

34. 904.282 109,388.705 ↓ 1.0 788,683 1

Hash (cost=484,723.79..484,723.79 rows=772,815 width=68) (actual time=109,388.705..109,388.705 rows=788,683 loops=1)

  • Buckets: 65536 Batches: 16 Memory Usage: 4677kB
35. 99,930.838 108,484.423 ↓ 1.0 788,683 1

Bitmap Heap Scan on brk_zakelijkerechten zrt_0 (cost=11,108.09..484,723.79 rows=772,815 width=68) (actual time=8,576.444..108,484.423 rows=788,683 loops=1)

  • Recheck Cond: ((_expiration_date IS NULL) OR (_expiration_date > now()))
  • Rows Removed by Index Recheck: 768532
  • Heap Blocks: exact=73826 lossy=159344
36. 0.004 8,553.585 ↓ 0.0 0 1

BitmapOr (cost=11,108.09..11,108.09 rows=772,815 width=0) (actual time=8,553.585..8,553.585 rows=0 loops=1)

37. 8,553.523 8,553.523 ↓ 1.0 788,683 1

Bitmap Index Scan on brk_zrt_1a9d849ff5a68997176b6144236806ae (cost=0.00..10,720.14 rows=772,815 width=0) (actual time=8,553.523..8,553.523 rows=788,683 loops=1)

  • Index Cond: (_expiration_date IS NULL)
38. 0.058 0.058 ↓ 0.0 0 1

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

  • Index Cond: (_expiration_date > now())
Planning time : 355.883 ms
Execution time : 438,929.394 ms