explain.depesz.com

PostgreSQL's explain analyze made readable

Result: TTem : Optimization for: Optimization for: plan #IY3U; plan #lHnj

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 3,606.907 351,890.030 ↑ 395.4 1,819,346 1

Sort (cost=300,152,607.36..301,951,013.13 rows=719,362,311 width=442) (actual time=351,172.804..351,890.030 rows=1,819,346 loops=1)

  • Sort Key: sdl_0._gobid
  • Sort Method: external merge Disk: 987672kB
2. 8,193.284 348,283.123 ↑ 395.4 1,819,346 1

Hash Left Join (cost=2,102,179.63..24,391,402.84 rows=719,362,311 width=442) (actual time=335,320.022..348,283.123 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. 846.611 255,242.198 ↑ 395.4 1,819,346 1

Merge Right Join (cost=1,617,269.67..12,936,212.95 rows=719,362,311 width=474) (actual time=250,466.543..255,242.198 rows=1,819,346 loops=1)

  • Merge Cond: ((rel_0.src_id)::text = (sdl_0._id)::text)
4. 5,034.613 203,827.800 ↑ 1.0 2,612,975 1

Sort (cost=1,075,225.49..1,081,758.82 rows=2,613,330 width=49) (actual time=203,350.449..203,827.800 rows=2,612,975 loops=1)

  • Sort Key: rel_0.src_id
  • Sort Method: external merge Disk: 85120kB
5. 1,705.752 198,793.187 ↑ 1.0 2,613,330 1

Hash Left Join (cost=694,720.38..744,359.68 rows=2,613,330 width=49) (actual time=196,760.223..198,793.187 rows=2,613,330 loops=1)

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

7. 720.761 196,758.379 ↑ 1.0 943,878 1

Hash (cost=682,728.16..682,728.16 rows=959,378 width=50) (actual time=196,758.378..196,758.379 rows=943,878 loops=1)

  • Buckets: 1048576 Batches: 1 Memory Usage: 84523kB
8. 195,750.657 196,037.618 ↑ 1.0 943,878 1

Bitmap Heap Scan on brk_tenaamstellingen tng_0 (cost=10,326.41..682,728.16 rows=959,378 width=50) (actual time=425.001..196,037.618 rows=943,878 loops=1)

  • Recheck Cond: (COALESCE(_expiration_date, '9999-12-31 00:00:00'::timestamp without time zone) > now())
  • Heap Blocks: exact=388334
9. 286.961 286.961 ↑ 1.0 943,878 1

Bitmap Index Scan on tmp_exp_tng (cost=0.00..10,086.57 rows=959,378 width=0) (actual time=286.961..286.961 rows=943,878 loops=1)

  • Index Cond: (COALESCE(_expiration_date, '9999-12-31 00:00:00'::timestamp without time zone) > now())
10. 757.120 50,567.787 ↑ 15.1 1,819,346 1

Materialize (cost=541,929.48..1,126,301.68 rows=27,526,657 width=437) (actual time=47,116.057..50,567.787 rows=1,819,346 loops=1)

11. 595.161 49,810.667 ↑ 15.1 1,819,306 1

Merge Left Join (cost=541,929.48..1,057,485.04 rows=27,526,657 width=437) (actual time=47,116.048..49,810.667 rows=1,819,306 loops=1)

  • Merge Cond: ((sdl_0._id)::text = (rel_3.src_id)::text)
12. 191.568 47,772.905 ↑ 10.0 391,304 1

Merge Left Join (cost=541,906.12..611,251.55 rows=3,904,579 width=399) (actual time=47,112.171..47,772.905 rows=391,304 loops=1)

  • Merge Cond: ((sdl_0._id)::text = (rel_2.src_id)::text)
13. 216.085 30,450.583 ↑ 3.6 161,609 1

Merge Left Join (cost=245,333.34..254,641.98 rows=587,243 width=357) (actual time=30,086.185..30,450.583 rows=161,609 loops=1)

  • Merge Cond: ((sdl_0._id)::text = (rel_1.src_id)::text)
14. 377.547 20,269.380 ↑ 1.0 100,000 1

Sort (cost=48,250.53..48,500.53 rows=100,000 width=316) (actual time=20,225.183..20,269.380 rows=100,000 loops=1)

  • Sort Key: sdl_0._id
  • Sort Method: quicksort Memory: 54635kB
15. 23.743 19,891.833 ↑ 1.0 100,000 1

Subquery Scan on sdl_0 (cost=0.42..39,945.71 rows=100,000 width=316) (actual time=6.340..19,891.833 rows=100,000 loops=1)

16. 15.400 19,868.090 ↑ 1.0 100,000 1

Limit (cost=0.42..38,945.71 rows=100,000 width=908) (actual time=6.337..19,868.090 rows=100,000 loops=1)

17. 19,852.690 19,852.690 ↑ 3.0 100,000 1

Index Scan using tmp_brk_stukdelen_random_pkey on tmp_brk_stukdelen_random (cost=0.42..115,836.16 rows=297,432 width=908) (actual time=6.334..19,852.690 rows=100,000 loops=1)

  • Filter: (COALESCE(_expiration_date, '9999-12-31 00:00:00'::timestamp without time zone) > now())
18. 1,166.047 9,965.118 ↑ 1.0 587,171 1

Sort (cost=197,082.81..198,550.92 rows=587,243 width=47) (actual time=9,860.987..9,965.118 rows=587,171 loops=1)

  • Sort Key: rel_1.src_id
  • Sort Method: quicksort Memory: 96032kB
19. 448.482 8,799.071 ↑ 1.0 587,243 1

Hash Left Join (cost=125,318.41..140,814.37 rows=587,243 width=47) (actual time=8,066.456..8,799.071 rows=587,243 loops=1)

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

21. 225.739 8,061.007 ↑ 1.0 563,447 1

Hash (cost=118,253.18..118,253.18 rows=565,219 width=78) (actual time=8,061.007..8,061.007 rows=563,447 loops=1)

  • Buckets: 1048576 Batches: 1 Memory Usage: 69566kB
22. 7,835.268 7,835.268 ↑ 1.0 563,447 1

Seq Scan on brk_aantekeningenrechten art_0 (cost=0.00..118,253.18 rows=565,219 width=78) (actual time=1.562..7,835.268 rows=563,447 loops=1)

  • Filter: (COALESCE(_expiration_date, '9999-12-31 00:00:00'::timestamp without time zone) > now())
  • Rows Removed by Filter: 4426
23. 893.598 17,130.754 ↓ 1.2 775,920 1

Sort (cost=296,572.78..298,235.03 rows=664,900 width=48) (actual time=17,025.975..17,130.754 rows=775,920 loops=1)

  • Sort Key: rel_2.src_id
  • Sort Method: quicksort Memory: 93374kB
24. 446.479 16,237.156 ↑ 1.0 664,900 1

Hash Left Join (cost=213,557.99..232,267.71 rows=664,900 width=48) (actual time=15,511.999..16,237.156 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))
25. 281.267 281.267 ↑ 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.753..281.267 rows=664,900 loops=1)

26. 250.511 15,509.410 ↑ 1.0 424,427 1

Hash (cost=207,097.04..207,097.04 rows=430,730 width=82) (actual time=15,509.409..15,509.410 rows=424,427 loops=1)

  • Buckets: 524288 Batches: 1 Memory Usage: 51652kB
27. 15,157.103 15,258.899 ↑ 1.0 424,427 1

Bitmap Heap Scan on brk_aantekeningenkadastraleobjecten akt_0 (cost=4,642.09..207,097.04 rows=430,730 width=82) (actual time=123.299..15,258.899 rows=424,427 loops=1)

  • Recheck Cond: (COALESCE(_expiration_date, '9999-12-31 00:00:00'::timestamp without time zone) > now())
  • Heap Blocks: exact=98969
28. 101.796 101.796 ↑ 1.0 424,427 1

Bitmap Index Scan on tmp_exp_akt (cost=0.00..4,534.40 rows=430,730 width=0) (actual time=101.796..101.796 rows=424,427 loops=1)

  • Index Cond: (COALESCE(_expiration_date, '9999-12-31 00:00:00'::timestamp without time zone) > now())
29. 239.008 1,442.601 ↓ 3.0 2,084,406 1

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

30. 1,203.593 1,203.593 ↑ 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=1.860..1,203.593 rows=704,884 loops=1)

31. 545.180 84,847.641 ↑ 1.0 788,683 1

Hash (cost=472,876.32..472,876.32 rows=802,243 width=68) (actual time=84,847.640..84,847.641 rows=788,683 loops=1)

  • Buckets: 1048576 Batches: 1 Memory Usage: 75267kB
32. 84,132.210 84,302.461 ↑ 1.0 788,683 1

Bitmap Heap Scan on brk_zakelijkerechten zrt_0 (cost=8,633.42..472,876.32 rows=802,243 width=68) (actual time=244.487..84,302.461 rows=788,683 loops=1)

  • Recheck Cond: (COALESCE(_expiration_date, '9999-12-31 00:00:00'::timestamp without time zone) > now())
  • Heap Blocks: exact=233170
33. 170.251 170.251 ↑ 1.0 788,683 1

Bitmap Index Scan on tmp_exp_zrt (cost=0.00..8,432.86 rows=802,243 width=0) (actual time=170.251..170.251 rows=788,683 loops=1)

  • Index Cond: (COALESCE(_expiration_date, '9999-12-31 00:00:00'::timestamp without time zone) > now())
Planning time : 124.738 ms
Execution time : 352,170.307 ms