explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Ft2R

Settings
# exclusive inclusive rows x rows loops node
1. 16.664 182,777.782 ↑ 509,558.1 9,749 1

Sort (cost=4,579,299,211.67..4,591,718,417.24 rows=4,967,682,229 width=442) (actual time=182,776.455..182,777.782 rows=9,749 loops=1)

  • Sort Key: brk_stukdelen._gobid
  • Sort Method: external merge Disk: 5144kB
2. 173.202 182,761.118 ↑ 509,558.1 9,749 1

Hash Left Join (cost=1,546,364.95..845,461,073.32 rows=4,967,682,229 width=442) (actual time=181,498.661..182,761.118 rows=9,749 loops=1)

  • Hash Cond: (((rel_3.dst_id)::text = (zrt_0._id)::text) AND ((rel_3.dst_volgnummer)::text = (zrt_0.volgnummer)::text))
3. 70.361 52,080.724 ↑ 509,558.1 9,749 1

Merge Right Join (cost=942,306.24..76,283,082.12 rows=4,967,682,229 width=474) (actual time=50,991.170..52,080.724 rows=9,749 loops=1)

  • Merge Cond: ((rel_2.src_id)::text = (brk_stukdelen._id)::text)
4. 1,038.627 32,313.173 ↑ 1.0 662,959 1

Sort (cost=404,750.48..406,412.73 rows=664,900 width=48) (actual time=32,135.140..32,313.173 rows=662,959 loops=1)

  • Sort Key: rel_2.src_id
  • Sort Method: external merge Disk: 30560kB
5. 14,822.540 31,274.546 ↑ 1.0 664,900 1

Hash Right Join (cost=37,241.34..328,465.22 rows=664,900 width=48) (actual time=17,766.376..31,274.546 rows=664,900 loops=1)

  • Hash Cond: (((akt_0._id)::text = (rel_2.dst_id)::text) AND ((akt_0.volgnummer)::text = (rel_2.dst_volgnummer)::text))
6. 635.647 2,018.944 ↑ 1.0 424,427 1

Bitmap Heap Scan on brk_aantekeningenkadastraleobjecten akt_0 (cost=6,204.84..274,443.11 rows=432,386 width=82) (actual time=1,407.390..2,018.944 rows=424,427 loops=1)

  • Recheck Cond: ((_expiration_date IS NULL) OR (_expiration_date > now()))
  • Heap Blocks: exact=98529
7. 0.004 1,383.297 ↓ 0.0 0 1

BitmapOr (cost=6,204.84..6,204.84 rows=435,079 width=0) (actual time=1,383.297..1,383.297 rows=0 loops=1)

8. 1,383.225 1,383.225 ↑ 1.0 424,419 1

Bitmap Index Scan on brk_akt_1a9d849ff5a68997176b6144236806ae (cost=0.00..5,882.53 rows=427,440 width=0) (actual time=1,383.225..1,383.225 rows=424,419 loops=1)

  • Index Cond: (_expiration_date IS NULL)
9. 0.068 0.068 ↑ 954.9 8 1

Bitmap Index Scan on brk_akt_1a9d849ff5a68997176b6144236806ae (cost=0.00..106.12 rows=7,639 width=0) (actual time=0.068..0.068 rows=8 loops=1)

  • Index Cond: (_expiration_date > now())
10. 13,950.359 14,433.062 ↑ 1.0 664,900 1

Hash (cost=15,219.00..15,219.00 rows=664,900 width=46) (actual time=14,433.062..14,433.062 rows=664,900 loops=1)

  • Buckets: 131072 (originally 131072) Batches: 65536 (originally 16) Memory Usage: 8370kB
11. 482.703 482.703 ↑ 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=1.260..482.703 rows=664,900 loops=1)

12. 3.413 19,697.190 ↑ 5,469.6 9,749 1

Materialize (cost=537,555.76..1,493,081.59 rows=53,322,827 width=438) (actual time=18,853.819..19,697.190 rows=9,749 loops=1)

13. 92.689 19,693.777 ↑ 6,638.8 8,032 1

Merge Left Join (cost=537,555.76..1,359,774.53 rows=53,322,827 width=438) (actual time=18,853.813..19,693.777 rows=8,032 loops=1)

  • Merge Cond: ((brk_stukdelen._id)::text = (rel_3.src_id)::text)
14. 88.658 17,811.280 ↑ 639.2 1,682 1

Merge Left Join (cost=439,436.63..457,362.59 rows=1,075,179 width=400) (actual time=17,349.374..17,811.280 rows=1,682 loops=1)

  • Merge Cond: ((brk_stukdelen._id)::text = (rel_1.src_id)::text)
15. 3.736 918.743 ↑ 66.0 1,000 1

Sort (cost=144,574.41..144,739.49 rows=66,033 width=359) (actual time=918.341..918.743 rows=1,000 loops=1)

  • Sort Key: brk_stukdelen._id
  • Sort Method: quicksort Memory: 540kB
16. 1.604 915.007 ↑ 66.0 1,000 1

Nested Loop Left Join (cost=1.28..132,941.37 rows=66,033 width=359) (actual time=3.171..915.007 rows=1,000 loops=1)

17. 0.374 35.403 ↑ 1.0 1,000 1

Limit (cost=0.42..344.62 rows=1,000 width=688) (actual time=0.042..35.403 rows=1,000 loops=1)

18. 35.029 35.029 ↑ 899.4 1,000 1

Index Scan using brk_stukdelen_pkey on brk_stukdelen (cost=0.42..309,565.78 rows=899,398 width=688) (actual time=0.041..35.029 rows=1,000 loops=1)

  • Filter: ((_expiration_date IS NULL) OR (_expiration_date > now()))
19. 1.000 878.000 ↓ 0.0 0 1,000

Nested Loop Left Join (cost=0.86..131.93 rows=66 width=49) (actual time=0.878..0.878 rows=0 loops=1,000)

20. 877.000 877.000 ↓ 0.0 0 1,000

Index Scan using src_id_mv_brk_sdl_brk_tng_is_bron_voor_tenaamstelling on mv_brk_sdl_brk_tng_is_bron_voor_tenaamstelling rel_0 (cost=0.43..34.13 rows=66 width=13) (actual time=0.877..0.877 rows=0 loops=1,000)

  • Index Cond: ((brk_stukdelen._id)::text = (src_id)::text)
21. 0.000 0.000 ↓ 0.0 0

Index Scan using brk_tng_2a4dbedb477015cfe2b9f2c990906f44 on brk_tenaamstellingen tng_0 (cost=0.43..1.47 rows=1 width=50) (never executed)

  • Index Cond: ((_id)::text = (rel_0.dst_id)::text)
  • Filter: ((_expiration_date IS NULL) OR (_expiration_date > now()))
22. 65.026 16,803.879 ↑ 1.0 586,015 1

Materialize (cost=294,862.22..297,798.44 rows=587,243 width=47) (actual time=16,429.767..16,803.879 rows=586,015 loops=1)

23. 1,307.526 16,738.853 ↑ 1.0 586,015 1

Sort (cost=294,862.22..296,330.33 rows=587,243 width=47) (actual time=16,429.759..16,738.853 rows=586,015 loops=1)

  • Sort Key: rel_1.src_id
  • Sort Method: external merge Disk: 34200kB
24. 609.451 15,431.327 ↑ 1.0 587,243 1

Hash Right Join (cost=26,456.97..228,011.68 rows=587,243 width=47) (actual time=734.211..15,431.327 rows=587,243 loops=1)

  • Hash Cond: ((art_0._id)::text = (rel_1.dst_id)::text)
25. 14,175.871 14,175.871 ↓ 1.0 563,450 1

Seq Scan on brk_aantekeningenrechten art_0 (cost=0.00..174,782.17 rows=547,896 width=78) (actual time=1.847..14,175.871 rows=563,450 loops=1)

  • Filter: ((_expiration_date IS NULL) OR (_expiration_date > now()))
  • Rows Removed by Filter: 4423
26. 156.176 646.005 ↑ 1.0 587,243 1

Hash (cost=13,954.43..13,954.43 rows=587,243 width=43) (actual time=646.005..646.005 rows=587,243 loops=1)

  • Buckets: 131072 Batches: 16 Memory Usage: 3752kB
27. 489.829 489.829 ↑ 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=1.419..489.829 rows=587,243 loops=1)

28. 71.563 1,789.808 ↑ 1.0 696,456 1

Materialize (cost=98,119.13..101,644.05 rows=704,984 width=45) (actual time=1,496.696..1,789.808 rows=696,456 loops=1)

29. 1,164.974 1,718.245 ↑ 1.0 690,917 1

Sort (cost=98,119.13..99,881.59 rows=704,984 width=45) (actual time=1,496.692..1,718.245 rows=690,917 loops=1)

  • Sort Key: rel_3.src_id
  • Sort Method: external merge Disk: 38680kB
30. 553.271 553.271 ↑ 1.0 704,984 1

Seq Scan on mv_brk_sdl_brk_zrt_is_bron_voor_zakelijk_recht rel_3 (cost=0.00..16,935.84 rows=704,984 width=45) (actual time=1.882..553.271 rows=704,984 loops=1)

31. 638.917 130,507.192 ↑ 1.0 788,683 1

Hash (cost=582,690.93..582,690.93 rows=799,719 width=68) (actual time=130,507.192..130,507.192 rows=788,683 loops=1)

  • Buckets: 65536 Batches: 16 Memory Usage: 4677kB
32. 124,879.955 129,868.275 ↑ 1.0 788,683 1

Bitmap Heap Scan on brk_zakelijkerechten zrt_0 (cost=15,497.82..582,690.93 rows=799,719 width=68) (actual time=5,016.624..129,868.275 rows=788,683 loops=1)

  • Recheck Cond: ((_expiration_date IS NULL) OR (_expiration_date > now()))
  • Rows Removed by Index Recheck: 691593
  • Heap Blocks: exact=81583 lossy=160955
33. 0.004 4,988.320 ↓ 0.0 0 1

BitmapOr (cost=15,497.82..15,497.82 rows=799,719 width=0) (actual time=4,988.320..4,988.320 rows=0 loops=1)

34. 4,988.284 4,988.284 ↑ 1.0 788,683 1

Bitmap Index Scan on brk_zrt_1a9d849ff5a68997176b6144236806ae (cost=0.00..15,096.42 rows=799,719 width=0) (actual time=4,988.284..4,988.284 rows=788,683 loops=1)

  • Index Cond: (_expiration_date IS NULL)
35. 0.032 0.032 ↓ 0.0 0 1

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

  • Index Cond: (_expiration_date > now())
Planning time : 29.792 ms
Execution time : 182,793.429 ms