explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Qmiw : Optimization for: plan #rhM

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.433 827.452 ↑ 1.3 2,315 1

Unique (cost=74,165.43..74,237.92 rows=2,906 width=4) (actual time=825.439..827.452 rows=2,315 loops=1)

2. 0.927 827.019 ↑ 1.3 2,315 1

Merge Semi Join (cost=74,165.43..74,230.65 rows=2,906 width=4) (actual time=825.438..827.019 rows=2,315 loops=1)

  • Merge Cond: (farmer.farmer_id = farm_geocoding.farmer_id)
3. 1.248 821.815 ↑ 1.3 3,268 1

Sort (cost=73,698.63..73,709.44 rows=4,327 width=4) (actual time=821.381..821.815 rows=3,268 loops=1)

  • Sort Key: farmer.farmer_id
  • Sort Method: quicksort Memory: 250kB
4. 67.520 820.567 ↑ 1.3 3,268 1

HashAggregate (cost=73,350.76..73,394.03 rows=4,327 width=4) (actual time=819.637..820.567 rows=3,268 loops=1)

  • Group Key: farmer.farmer_id
5. 206.969 753.047 ↓ 2.2 280,810 1

Hash Join (cost=12,735.07..73,024.39 rows=130,546 width=4) (actual time=214.868..753.047 rows=280,810 loops=1)

  • Hash Cond: (zone.field_id = field.field_id)
6. 331.795 333.006 ↓ 2.0 358,843 1

Seq Scan on zone (cost=78.70..58,386.28 rows=180,343 width=4) (actual time=1.550..333.006 rows=358,843 loops=1)

  • Filter: (((deleted IS NULL) OR (NOT deleted)) AND (NOT (hashed SubPlan 1)))
  • Rows Removed by Filter: 1331
7.          

SubPlan (forSeq Scan)

8. 0.677 1.211 ↑ 1.0 1,308 1

HashAggregate (cost=62.35..75.43 rows=1,308 width=4) (actual time=1.038..1.211 rows=1,308 loops=1)

  • Group Key: cranfield_data.zone_id
9. 0.534 0.534 ↑ 1.0 1,308 1

Seq Scan on cranfield_data (cost=0.00..59.08 rows=1,308 width=4) (actual time=0.012..0.534 rows=1,308 loops=1)

10. 49.185 213.072 ↓ 1.1 130,005 1

Hash (cost=11,193.77..11,193.77 rows=117,008 width=8) (actual time=213.072..213.072 rows=130,005 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 6103kB
11. 59.823 163.887 ↓ 1.1 130,005 1

Hash Join (cost=1,734.52..11,193.77 rows=117,008 width=8) (actual time=25.079..163.887 rows=130,005 loops=1)

  • Hash Cond: (field.layer_id = layer.layer_id)
12. 79.095 79.095 ↑ 1.0 141,449 1

Seq Scan on field (cost=0.00..7,757.51 rows=141,784 width=8) (actual time=0.035..79.095 rows=141,449 loops=1)

  • Filter: (((deleted IS NULL) OR (NOT deleted)) AND ((name)::text <> 'Unused Zones'::text))
  • Rows Removed by Filter: 20148
13. 4.290 24.969 ↑ 1.0 18,158 1

Hash (cost=1,507.50..1,507.50 rows=18,161 width=8) (actual time=24.969..24.969 rows=18,158 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 966kB
14. 8.028 20.679 ↑ 1.0 18,158 1

Hash Join (cost=861.30..1,507.50 rows=18,161 width=8) (actual time=10.185..20.679 rows=18,158 loops=1)

  • Hash Cond: (layer.farm_id = farm.farm_id)
15. 2.520 2.520 ↑ 1.0 21,827 1

Seq Scan on layer (cost=0.00..382.07 rows=22,007 width=8) (actual time=0.009..2.520 rows=21,827 loops=1)

16. 1.914 10.131 ↑ 1.0 9,064 1

Hash (cost=747.90..747.90 rows=9,072 width=8) (actual time=10.131..10.131 rows=9,064 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 483kB
17. 3.217 8.217 ↑ 1.0 9,064 1

Hash Join (cost=319.36..747.90 rows=9,072 width=8) (actual time=1.917..8.217 rows=9,064 loops=1)

  • Hash Cond: (farm.farmer_id = farmer.farmer_id)
18. 3.113 3.113 ↑ 1.0 9,071 1

Seq Scan on farm (cost=0.00..303.80 rows=9,072 width=8) (actual time=0.007..3.113 rows=9,071 loops=1)

  • Filter: ((deleted IS NULL) OR (NOT deleted))
  • Rows Removed by Filter: 1799
19. 0.649 1.887 ↓ 1.0 4,333 1

Hash (cost=265.27..265.27 rows=4,327 width=4) (actual time=1.887..1.887 rows=4,333 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 217kB
20. 1.238 1.238 ↓ 1.0 4,333 1

Seq Scan on farmer (cost=0.00..265.27 rows=4,327 width=4) (actual time=0.008..1.238 rows=4,333 loops=1)

21. 0.789 4.277 ↑ 1.3 2,320 1

Sort (cost=466.80..474.07 rows=2,906 width=4) (actual time=4.050..4.277 rows=2,320 loops=1)

  • Sort Key: farm_geocoding.farmer_id
  • Sort Method: quicksort Memory: 205kB
22. 1.651 3.488 ↑ 1.3 2,320 1

HashAggregate (cost=241.52..270.58 rows=2,906 width=4) (actual time=3.125..3.488 rows=2,320 loops=1)

  • Group Key: farm_geocoding.farmer_id
23. 1.837 1.837 ↑ 1.0 5,990 1

Seq Scan on farm_geocoding (cost=0.00..226.43 rows=6,036 width=4) (actual time=0.024..1.837 rows=5,990 loops=1)

  • Filter: (country = 'United Kingdom'::text)
  • Rows Removed by Filter: 2301