explain.depesz.com

PostgreSQL's explain analyze made readable

Result: fnNO

Settings
# exclusive inclusive rows x rows loops node
1. 2,593.438 10,920,393.244 ↑ 1.0 1 1

Aggregate (cost=13,297,855.31..13,297,855.32 rows=1 width=8) (actual time=10,920,393.243..10,920,393.244 rows=1 loops=1)

2. 3,523.049 10,917,799.806 ↓ 14,786,537.0 14,786,537 1

Nested Loop (cost=1,775,877.53..13,297,855.31 rows=1 width=0) (actual time=77,977.563..10,917,799.806 rows=14,786,537 loops=1)

3. 2,960.934 6,389,596.435 ↓ 14,786,537.0 14,786,537 1

Nested Loop (cost=1,775,868.09..13,297,828.03 rows=1 width=15) (actual time=77,972.250..6,389,596.435 rows=14,786,537 loops=1)

4. 0.000 88,173.115 ↓ 1.7 15,324,726 1

Hash Left Join (cost=1,775,867.53..4,539,907.42 rows=9,272,154 width=16) (actual time=77,970.076..88,173.115 rows=15,324,726 loops=1)

  • Hash Cond: ((f.roof_type_code)::text = (h.code)::text)
5. 4,591.466 89,193.859 ↓ 1.7 15,324,726 1

Gather (cost=1,775,866.44..4,412,414.21 rows=9,272,154 width=18) (actual time=77,969.631..89,193.859 rows=15,324,726 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
6. 1,169.255 84,602.393 ↓ 1.3 5,108,242 3

Hash Left Join (cost=1,774,866.44..3,484,198.81 rows=3,863,398 width=18) (actual time=77,933.612..84,602.393 rows=5,108,242 loops=3)

  • Hash Cond: ((f.primary_roof_material_code)::text = (g.code)::text)
7. 7,608.622 83,432.906 ↓ 1.3 5,108,242 3

Parallel Hash Left Join (cost=1,774,865.30..3,431,075.95 rows=3,863,398 width=20) (actual time=77,933.084..83,432.906 rows=5,108,242 loops=3)

  • Hash Cond: ((a.building_pid)::text = (f.building_pid)::text)
8. 2,333.754 70,597.051 ↓ 1.3 5,108,242 3

Hash Left Join (cost=1,486,291.36..3,029,345.49 rows=3,863,398 width=16) (actual time=62,176.740..70,597.051 rows=5,108,242 loops=3)

  • Hash Cond: ((d.zone_code)::text = (e.code)::text)
9. 8,254.310 68,259.834 ↓ 1.3 5,108,242 3

Parallel Hash Left Join (cost=1,486,290.09..2,976,222.50 rows=3,863,398 width=18) (actual time=62,154.446..68,259.834 rows=5,108,242 loops=3)

  • Hash Cond: ((a.building_pid)::text = (d.building_pid)::text)
10. 9,793.052 54,092.090 ↓ 1.3 5,107,664 3

Parallel Hash Left Join (cost=1,151,660.16..2,512,394.07 rows=3,863,398 width=16) (actual time=46,407.719..54,092.090 rows=5,107,664 loops=3)

  • Hash Cond: ((a.building_pid)::text = (c_1.building_pid)::text)
  • Filter: (((c_1.height_type_code)::text = 'E1'::text) OR (c_1.building_pid IS NULL))
  • Rows Removed by Filter: 3970278
11. 24,487.565 34,544.097 ↓ 1.0 5,107,664 3

Parallel Hash Left Join (cost=575,830.08..1,715,396.25 rows=4,958,436 width=16) (actual time=26,419.430..34,544.097 rows=5,107,664 loops=3)

  • Hash Cond: ((a.building_pid)::text = (b_1.building_pid)::text)
  • Filter: (((b_1.height_type_code)::text = 'R1'::text) OR (b_1.building_pid IS NULL))
  • Rows Removed by Filter: 3970278
  • -> Parallel Seq Scan on building_polygon a (cost=0.00..871806.42 rows=6375742 width=16) (actual time=0.954..14025.962 rows=5107664 lo
12. 10,056.532 10,056.532 ↑ 1.3 7,940,555 3

Parallel Hash (cost=393,592.37..393,592.37 rows=9,926,137 width=19) (actual time=10,056.345..10,056.532 rows=7,940,555 loops=3)

  • Buckets: 65536 Batches: 512 Memory Usage: 3136kB
  • -> Parallel Seq Scan on height_above_ground b_1 (cost=0.00..393592.37 rows=9926137 width=19) (actual time=0.017..4821.715 rows=
13. 9,754.941 9,754.941 ↑ 1.3 7,940,555 3

Parallel Hash (cost=393,592.37..393,592.37 rows=9,926,137 width=19) (actual time=9,754.738..9,754.941 rows=7,940,555 loops=3)

  • Buckets: 65536 Batches: 512 Memory Usage: 3136kB
  • -> Parallel Seq Scan on height_above_ground c_1 (cost=0.00..393592.37 rows=9926137 width=19) (actual time=0.374..4927.162 rows=794055
14. 3,032.690 5,913.434 ↑ 1.3 5,041,329 3

Parallel Hash (cost=218,934.86..218,934.86 rows=6,301,686 width=18) (actual time=5,913.246..5,913.434 rows=5,041,329 loops=3)

  • Buckets: 65536 Batches: 256 Memory Usage: 3808kB
15. 2,880.744 2,880.744 ↑ 1.3 5,041,329 3

Parallel Seq Scan on building_zone d (cost=0.00..218,934.86 rows=6,301,686 width=18) (actual time=0.506..2,880.744 rows=5,041,329 loops=3)

16. 0.319 3.463 ↑ 1.0 12 3

Hash (cost=1.12..1.12 rows=12 width=2) (actual time=3.281..3.463 rows=12 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
17. 3.144 3.144 ↑ 1.0 12 3

Seq Scan on zone_aut e (cost=0.00..1.12 rows=12 width=2) (actual time=3.142..3.144 rows=12 loops=3)

18. 2,577.834 5,227.233 ↑ 1.2 3,971,766 3

Parallel Hash (cost=197,432.64..197,432.64 rows=4,964,264 width=20) (actual time=5,227.233..5,227.233 rows=3,971,766 loops=3)

  • Buckets: 65536 Batches: 256 Memory Usage: 3104kB
19. 2,649.399 2,649.399 ↑ 1.2 3,971,766 3

Parallel Seq Scan on building_roof f (cost=0.00..197,432.64 rows=4,964,264 width=20) (actual time=0.258..2,649.399 rows=3,971,766 loops=3)

20. 0.003 0.232 ↑ 1.0 6 3

Hash (cost=1.06..1.06 rows=6 width=2) (actual time=0.232..0.232 rows=6 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
21. 0.229 0.229 ↑ 1.0 6 3

Seq Scan on roof_material_aut g (cost=0.00..1.06 rows=6 width=2) (actual time=0.228..0.229 rows=6 loops=3)

22. 0.004 0.008 ↑ 1.0 4 1

Hash (cost=1.04..1.04 rows=4 width=2) (actual time=0.008..0.008 rows=4 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
23. 0.004 0.004 ↑ 1.0 4 1

Seq Scan on roof_type_aut h (cost=0.00..1.04 rows=4 width=2) (actual time=0.003..0.004 rows=4 loops=1)

24. 15,324.726 6,298,462.386 ↑ 1.0 1 15,324,726

Subquery Scan on b (cost=0.56..0.93 rows=1 width=31) (actual time=0.411..0.411 rows=1 loops=15,324,726)

  • Filter: ((a.building_pid)::text = (b.building_pid)::text)
25. 0.000 6,283,137.660 ↑ 1.0 1 15,324,726

Limit (cost=0.56..0.92 rows=1 width=149) (actual time=0.410..0.410 rows=1 loops=15,324,726)

26. 6,283,137.660 6,283,137.660 ↑ 138.0 1 15,324,726

Index Scan using building_address_building_pid_idx on building_address (cost=0.56..49.84 rows=138 width=149) (actual time=0.410..0.410 rows=1 loops=15,324,726)

  • Index Cond: ((a.building_pid)::text = (building_pid)::text)
27. 14,786.537 4,524,680.322 ↑ 1.0 1 14,786,537

Subquery Scan on c (cost=9.44..27.27 rows=1 width=15) (actual time=0.305..0.306 rows=1 loops=14,786,537)

  • Filter: ((b.address_detail_pid)::text = (c.address_detail_pid)::text)
28. 14,786.537 4,509,893.785 ↑ 1.0 1 14,786,537

Limit (cost=9.44..27.26 rows=1 width=1,327) (actual time=0.304..0.305 rows=1 loops=14,786,537)

29. 14,786.537 4,495,107.248 ↑ 1.0 1 14,786,537

Nested Loop (cost=9.44..27.26 rows=1 width=1,327) (actual time=0.304..0.304 rows=1 loops=14,786,537)

30. 14,786.537 4,465,534.174 ↑ 1.0 1 14,786,537

Nested Loop (cost=9.30..27.10 rows=1 width=17) (actual time=0.302..0.302 rows=1 loops=14,786,537)

31. 14,786.537 4,376,814.952 ↑ 1.0 1 14,786,537

Nested Loop (cost=9.02..18.78 rows=1 width=22) (actual time=0.296..0.296 rows=1 loops=14,786,537)

32. 88,719.222 4,199,376.508 ↑ 1.0 1 14,786,537

Hash Right Join (cost=8.59..10.33 rows=1 width=32) (actual time=0.284..0.284 rows=1 loops=14,786,537)

  • Hash Cond: ((flat_type_aut.code)::text = (address_detail.flat_type_code)::text)
33. 59,146.148 59,146.148 ↑ 1.0 53 14,786,537

Seq Scan on flat_type_aut (cost=0.00..1.53 rows=53 width=4) (actual time=0.001..0.004 rows=53 loops=14,786,537)

34. 14,786.537 4,051,511.138 ↑ 1.0 1 14,786,537

Hash (cost=8.58..8.58 rows=1 width=36) (actual time=0.274..0.274 rows=1 loops=14,786,537)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
35. 4,036,724.601 4,036,724.601 ↑ 1.0 1 14,786,537

Index Scan using address_detail_pid_idx on address_detail (cost=0.56..8.58 rows=1 width=36) (actual time=0.272..0.273 rows=1 loops=14,786,537)

  • Index Cond: ((b.address_detail_pid)::text = (address_detail_pid)::text)
36. 162,651.907 162,651.907 ↑ 1.0 1 14,786,537

Index Only Scan using street_locality_pid_idx on street_locality (cost=0.42..8.44 rows=1 width=10) (actual time=0.010..0.011 rows=1 loops=14,786,537)

  • Index Cond: (street_locality_pid = (address_detail.street_locality_pid)::text)
  • Heap Fetches: 14786537
37. 73,932.685 73,932.685 ↑ 1.0 1 14,786,537

Index Scan using locality_pid_idx on locality (cost=0.29..8.30 rows=1 width=9) (actual time=0.005..0.005 rows=1 loops=14,786,537)

  • Index Cond: ((locality_pid)::text = (address_detail.locality_pid)::text)
38. 14,786.537 14,786.537 ↑ 1.0 1 14,786,537

Index Only Scan using state_pid_idx on state (cost=0.14..0.15 rows=1 width=2) (actual time=0.001..0.001 rows=1 loops=14,786,537)

  • Index Cond: (state_pid = (locality.state_pid)::text)
  • Heap Fetches: 14786537
Planning time : 34.067 ms
Execution time : 10,926,854.874 ms