explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 1MLD

Settings
# exclusive inclusive rows x rows loops node
1. 2,841.939 153,406.805 ↓ 10.0 10 1

Limit (cost=1,795,265.53..15,014,551.31 rows=1 width=787) (actual time=150,555.984..153,406.805 rows=10 loops=1)

2. 0.227 150,564.866 ↓ 10.0 10 1

Nested Loop (cost=1,795,265.53..15,014,551.31 rows=1 width=787) (actual time=150,555.983..150,564.866 rows=10 loops=1)

3. 0.011 150,560.639 ↓ 10.0 10 1

Nested Loop (cost=1,795,256.09..15,014,524.03 rows=1 width=743) (actual time=150,555.663..150,560.639 rows=10 loops=1)

4. 0.000 150,554.754 ↑ 842,923.1 11 1

Hash Left Join (cost=1,795,255.53..6,256,603.42 rows=9,272,154 width=728) (actual time=150,554.727..150,554.754 rows=11 loops=1)

  • Hash Cond: ((f.roof_type_code)::text = (h.code)::text)
5. 153,396.538 153,396.575 ↑ 842,923.1 11 1

Gather (cost=1,795,254.44..6,129,110.21 rows=9,272,154 width=631) (actual time=150,554.715..153,396.575 rows=11 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
  • -> Hash Left Join (cost=1794254.44..5200894.81 rows=3863398 width=631) (actual time=150497.497..150497.683 rows=75
  • Hash Cond: ((f.primary_roof_material_code)::text = (g.code)::text)
  • -> Hash Left Join (cost=1794253.30..5147771.95 rows=3863398 width=515) (actual time=150497.481..150497.647 ro
  • Hash Cond: ((d.zone_code)::text = (e.code)::text)
  • -> Parallel Hash Left Join (cost=1794252.03..5094648.96 rows=3863398 width=384) (actual time=150497.434
  • Hash Cond: ((a.building_pid)::text = (f.building_pid)::text)
  • -> Parallel Hash Left Join (cost=1505678.09..4345816.50 rows=3863398 width=380) (actual time=1156
  • Hash Cond: ((a.building_pid)::text = (d.building_pid)::text)
  • -> Parallel Hash Left Join (cost=1171048.16..3534886.07 rows=3863398 width=378) (actual tim
  • 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
  • -> Parallel Hash Left Join (cost=585524.08..2282700.25 rows=4958436 width=370) (actua
  • 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 w
  • -> Parallel Hash (cost=393592.37..393592.37 rows=9926137 width=27) (actual time
  • Buckets: 65536 Batches: 512 Memory Usage: 3520kB
  • -> Parallel Seq Scan on height_above_ground b_1 (cost=0.00..393592.37 row
  • -> Parallel Hash (cost=393592.37..393592.37 rows=9926137 width=27) (actual time=10452
  • Buckets: 65536 Batches: 512 Memory Usage: 3520kB
  • -> Parallel Seq Scan on height_above_ground c_1 (cost=0.00..393592.37 rows=9926
  • -> Parallel Hash (cost=218934.86..218934.86 rows=6301686 width=18) (actual time=6123.126..6
  • Buckets: 65536 Batches: 256 Memory Usage: 3808kB
  • -> Parallel Hash (cost=197432.64..197432.64 rows=4964264 width=20) (actual time=4900.672..4900.67
  • Buckets: 65536 Batches: 256 Memory Usage: 3072kB
  • -> Parallel Seq Scan on building_roof f (cost=0.00..197432.64 rows=4964264 width=20) (actua
6. 0.000 0.000 ↓ 0.0

Parallel Seq Scan on building_zone d (cost=0.00..218,934.86 rows=6,301,686 width=18) (actual rows= loops=)

7. 0.027 0.027 ↑ 1.0 12 3

Hash (cost=1.12..1.12 rows=12 width=133) (actual time=0.027..0.027 rows=12 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
  • -> Seq Scan on zone_aut e (cost=0.00..1.12 rows=12 width=133) (actual time=0.019..0.021 rows=12 l
8. 0.010 0.010 ↑ 1.0 6 3

Hash (cost=1.06..1.06 rows=6 width=118) (actual time=0.010..0.010 rows=6 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • -> Seq Scan on roof_material_aut g (cost=0.00..1.06 rows=6 width=118) (actual time=0.006..0.007 rows=6
9. 0.002 0.006 ↑ 1.0 4 1

Hash (cost=1.04..1.04 rows=4 width=99) (actual time=0.005..0.006 rows=4 loops=1)

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

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

11. 0.011 5.874 ↑ 1.0 1 11

Subquery Scan on b (cost=0.56..0.93 rows=1 width=31) (actual time=0.534..0.534 rows=1 loops=11)

  • Filter: ((a.building_pid)::text = (b.building_pid)::text)
12. 5.863 5.863 ↑ 1.0 1 11

Limit (cost=0.56..0.92 rows=1 width=149) (actual time=0.533..0.533 rows=1 loops=11)

  • -> Index Scan using building_address_building_pid_idx on building_address (cost=0.56..49.84 rows=138 width=149) (ac
  • Index Cond: ((a.building_pid)::text = (building_pid)::text)
13. 0.010 4.000 ↑ 1.0 1 10

Subquery Scan on c (cost=9.44..27.27 rows=1 width=50) (actual time=0.399..0.400 rows=1 loops=10)

  • Filter: ((b.address_detail_pid)::text = (c.address_detail_pid)::text)
14. 0.010 3.990 ↑ 1.0 1 10

Limit (cost=9.44..27.26 rows=1 width=854) (actual time=0.398..0.399 rows=1 loops=10)

15. 0.040 3.980 ↑ 1.0 1 10

Nested Loop (cost=9.44..27.26 rows=1 width=854) (actual time=0.398..0.398 rows=1 loops=10)

  • -> Index Scan using state_pid_idx on state (cost=0.14..0.15 rows=1 width=5) (actual time=0.002..0.002 rows=1 loops=
16. 0.080 3.940 ↑ 1.0 1 10

Nested Loop (cost=9.30..27.10 rows=1 width=49) (actual time=0.394..0.394 rows=1 loops=10)

  • -> Index Scan using locality_pid_idx on locality (cost=0.29..8.30 rows=1 width=19) (actual time=0.007..0.007
  • Index Cond: ((state_pid)::text = (locality.state_pid)::text)
17. 0.150 3.860 ↑ 1.0 1 10

Nested Loop (cost=9.02..18.78 rows=1 width=44) (actual time=0.386..0.386 rows=1 loops=10)

  • -> Index Scan using street_locality_pid_idx on street_locality (cost=0.42..8.44 rows=1 width=24) (actua
  • Index Cond: ((locality_pid)::text = (address_detail.locality_pid)::text)
18. 0.110 3.710 ↑ 1.0 1 10

Hash Right Join (cost=8.59..10.33 rows=1 width=40) (actual time=0.371..0.371 rows=1 loops=10)

  • Hash Cond: ((flat_type_aut.code)::text = (address_detail.flat_type_code)::text)
  • -> Seq Scan on flat_type_aut (cost=0.00..1.53 rows=53 width=4) (actual time=0.001..0.004 rows=52
  • Index Cond: ((street_locality_pid)::text = (address_detail.street_locality_pid)::text)
19. 3.600 3.600 ↑ 1.0 1 10

Hash (cost=8.58..8.58 rows=1 width=44) (actual time=0.360..0.360 rows=1 loops=10)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • -> Index Scan using address_detail_pid_idx on address_detail (cost=0.56..8.58 rows=1 width=
  • Index Cond: ((b.address_detail_pid)::text = (address_detail_pid)::text)