explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2XtQ

Settings
# exclusive inclusive rows x rows loops node
1. 228.484 90,301.040 ↑ 1.0 30,171 1

Hash Join (cost=1,222.33..32,021,406.61 rows=30,171 width=1,196) (actual time=23.917..90,301.040 rows=30,171 loops=1)

  • Hash Cond: (i.id = ig.id)
2. 35.258 142.714 ↑ 1.0 30,171 1

Hash Left Join (cost=79.48..1,679.25 rows=30,171 width=1,151) (actual time=0.368..142.714 rows=30,171 loops=1)

  • Hash Cond: (i."layoutId" = ly.id)
3. 36.082 107.441 ↑ 1.0 30,171 1

Hash Left Join (cost=68.36..1,582.22 rows=30,171 width=289) (actual time=0.348..107.441 rows=30,171 loops=1)

  • Hash Cond: (i."buildingId" = b.id)
4. 54.364 71.141 ↑ 1.0 30,171 1

Hash Join (cost=34.16..1,467.56 rows=30,171 width=225) (actual time=0.125..71.141 rows=30,171 loops=1)

  • Hash Cond: (i."propertyId" = p.id)
5. 16.666 16.666 ↑ 1.0 30,171 1

Seq Scan on "Inventory" i (cost=0.00..1,350.71 rows=30,171 width=151) (actual time=0.006..16.666 rows=30,171 loops=1)

6. 0.041 0.111 ↑ 1.0 96 1

Hash (cost=32.96..32.96 rows=96 width=90) (actual time=0.111..0.111 rows=96 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 18kB
7. 0.070 0.070 ↑ 1.0 96 1

Seq Scan on "Property" p (cost=0.00..32.96 rows=96 width=90) (actual time=0.003..0.070 rows=96 loops=1)

8. 0.100 0.218 ↓ 1.2 270 1

Hash (cost=31.31..31.31 rows=231 width=80) (actual time=0.218..0.218 rows=270 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 34kB
9. 0.118 0.118 ↓ 1.2 270 1

Seq Scan on "Building" b (cost=0.00..31.31 rows=231 width=80) (actual time=0.003..0.118 rows=270 loops=1)

10. 0.008 0.015 ↑ 3.3 15 1

Hash (cost=10.50..10.50 rows=50 width=894) (actual time=0.015..0.015 rows=15 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
11. 0.007 0.007 ↑ 3.3 15 1

Seq Scan on "Layout" ly (cost=0.00..10.50 rows=50 width=894) (actual time=0.002..0.007 rows=15 loops=1)

12. 10.139 20.262 ↑ 1.0 30,171 1

Hash (cost=765.71..765.71 rows=30,171 width=40) (actual time=20.262..20.262 rows=30,171 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 2142kB
13. 10.123 10.123 ↑ 1.0 30,171 1

Seq Scan on "MarketRent" ig (cost=0.00..765.71 rows=30,171 width=40) (actual time=0.004..10.123 rows=30,171 loops=1)

14.          

SubPlan (forHash Join)

15. 90.513 90.513 ↑ 1.0 1 30,171

Index Scan using "Address_pkey" on "Address" (cost=0.14..8.17 rows=1 width=32) (actual time=0.002..0.003 rows=1 loops=30,171)

  • Index Cond: (id = CASE WHEN (i."buildingId" IS NULL) THEN p."addressId" ELSE b."addressId" END)
16. 120.684 89,819.067 ↑ 1.0 1 30,171

Aggregate (cost=1,053.04..1,053.05 rows=1 width=32) (actual time=2.977..2.977 rows=1 loops=30,171)

17. 120.460 89,698.383 ↑ 1.0 2 30,171

Nested Loop (cost=1,036.72..1,053.04 rows=2 width=15) (actual time=2.968..2.973 rows=2 loops=30,171)

18. 90.513 89,457.015 ↑ 1.0 2 30,171

Unique (cost=1,036.58..1,036.59 rows=2 width=16) (actual time=2.963..2.965 rows=2 loops=30,171)

19. 211.197 89,366.502 ↑ 1.0 2 30,171

Sort (cost=1,036.58..1,036.59 rows=2 width=16) (actual time=2.961..2.962 rows=2 loops=30,171)

  • Sort Key: "Inventory_Amenity"."amenityId
  • Sort Method: quicksort Memory: 25kB
20. 120.684 89,155.305 ↑ 1.0 2 30,171

Append (cost=0.00..1,036.57 rows=2 width=16) (actual time=1.484..2.955 rows=2 loops=30,171)

21. 88,099.320 88,099.320 ↑ 1.0 1 30,171

Seq Scan on "Inventory_Amenity" (cost=0.00..1,022.31 rows=1 width=16) (actual time=1.482..2.920 rows=1 loops=30,171)

  • Filter: ("inventoryId" = i.id)
  • Rows Removed by Filter: 30104
22. 935.301 935.301 ↑ 1.0 1 30,171

Seq Scan on "Building_Amenity" (cost=0.00..14.24 rows=1 width=16) (actual time=0.017..0.031 rows=1 loops=30,171)

  • Filter: ("buildingId" = i."buildingId")
  • Rows Removed by Filter: 279
23. 120.908 120.908 ↑ 1.0 1 60,454

Index Scan using "Amenity_pkey" on "Amenity" a_1 (cost=0.14..8.16 rows=1 width=31) (actual time=0.002..0.002 rows=1 loops=60,454)

  • Index Cond: (id = "Inventory_Amenity"."amenityId")