#	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

Unique  (cost=74165.43..74237.92 rows=2906 width=4) (actual time=825.439..827.452 rows=2315 loops=1)
  ->  Merge Semi Join  (cost=74165.43..74230.65 rows=2906 width=4) (actual time=825.438..827.019 rows=2315 loops=1)
        Merge Cond: (farmer.farmer_id = farm_geocoding.farmer_id)
        ->  Sort  (cost=73698.63..73709.44 rows=4327 width=4) (actual time=821.381..821.815 rows=3268 loops=1)
              Sort Key: farmer.farmer_id
              Sort Method: quicksort  Memory: 250kB
              ->  HashAggregate  (cost=73350.76..73394.03 rows=4327 width=4) (actual time=819.637..820.567 rows=3268 loops=1)
                    Group Key: farmer.farmer_id
                    ->  Hash Join  (cost=12735.07..73024.39 rows=130546 width=4) (actual time=214.868..753.047 rows=280810 loops=1)
                          Hash Cond: (zone.field_id = field.field_id)
                          ->  Seq Scan on zone  (cost=78.70..58386.28 rows=180343 width=4) (actual time=1.550..333.006 rows=358843 loops=1)
                                Filter: (((deleted IS NULL) OR (NOT deleted)) AND (NOT (hashed SubPlan 1)))
                                Rows Removed by Filter: 1331
                                SubPlan 1
                                  ->  HashAggregate  (cost=62.35..75.43 rows=1308 width=4) (actual time=1.038..1.211 rows=1308 loops=1)
                                        Group Key: cranfield_data.zone_id
                                        ->  Seq Scan on cranfield_data  (cost=0.00..59.08 rows=1308 width=4) (actual time=0.012..0.534 rows=1308 loops=1)
                          ->  Hash  (cost=11193.77..11193.77 rows=117008 width=8) (actual time=213.072..213.072 rows=130005 loops=1)
                                Buckets: 131072  Batches: 1  Memory Usage: 6103kB
                                ->  Hash Join  (cost=1734.52..11193.77 rows=117008 width=8) (actual time=25.079..163.887 rows=130005 loops=1)
                                      Hash Cond: (field.layer_id = layer.layer_id)
                                      ->  Seq Scan on field  (cost=0.00..7757.51 rows=141784 width=8) (actual time=0.035..79.095 rows=141449 loops=1)
                                            Filter: (((deleted IS NULL) OR (NOT deleted)) AND ((name)::text <> 'Unused Zones'::text))
                                            Rows Removed by Filter: 20148
                                      ->  Hash  (cost=1507.50..1507.50 rows=18161 width=8) (actual time=24.969..24.969 rows=18158 loops=1)
                                            Buckets: 32768  Batches: 1  Memory Usage: 966kB
                                            ->  Hash Join  (cost=861.30..1507.50 rows=18161 width=8) (actual time=10.185..20.679 rows=18158 loops=1)
                                                  Hash Cond: (layer.farm_id = farm.farm_id)
                                                  ->  Seq Scan on layer  (cost=0.00..382.07 rows=22007 width=8) (actual time=0.009..2.520 rows=21827 loops=1)
                                                  ->  Hash  (cost=747.90..747.90 rows=9072 width=8) (actual time=10.131..10.131 rows=9064 loops=1)
                                                        Buckets: 16384  Batches: 1  Memory Usage: 483kB
                                                        ->  Hash Join  (cost=319.36..747.90 rows=9072 width=8) (actual time=1.917..8.217 rows=9064 loops=1)
                                                              Hash Cond: (farm.farmer_id = farmer.farmer_id)
                                                              ->  Seq Scan on farm  (cost=0.00..303.80 rows=9072 width=8) (actual time=0.007..3.113 rows=9071 loops=1)
                                                                    Filter: ((deleted IS NULL) OR (NOT deleted))
                                                                    Rows Removed by Filter: 1799
                                                              ->  Hash  (cost=265.27..265.27 rows=4327 width=4) (actual time=1.887..1.887 rows=4333 loops=1)
                                                                    Buckets: 8192  Batches: 1  Memory Usage: 217kB
                                                                    ->  Seq Scan on farmer  (cost=0.00..265.27 rows=4327 width=4) (actual time=0.008..1.238 rows=4333 loops=1)
        ->  Sort  (cost=466.80..474.07 rows=2906 width=4) (actual time=4.050..4.277 rows=2320 loops=1)
              Sort Key: farm_geocoding.farmer_id
              Sort Method: quicksort  Memory: 205kB
              ->  HashAggregate  (cost=241.52..270.58 rows=2906 width=4) (actual time=3.125..3.488 rows=2320 loops=1)
                    Group Key: farm_geocoding.farmer_id
                    ->  Seq Scan on farm_geocoding  (cost=0.00..226.43 rows=6036 width=4) (actual time=0.024..1.837 rows=5990 loops=1)
                          Filter: (country = 'United Kingdom'::text)
                          Rows Removed by Filter: 2301
Planning time: 1.397 ms
Execution time: 827.872 ms

Per node type stats

node type	count	sum of times	% of query
Hash	4	56.038 ms	6.8 %
Hash Join	4	278.037 ms	33.6 %
HashAggregate	3	69.848 ms	8.4 %
Merge Semi Join	1	0.927 ms	0.1 %
Seq Scan	7	420.132 ms	50.8 %
Sort	2	2.037 ms	0.2 %
Unique	1	0.433 ms	0.1 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
cranfield_data	1	0.534 ms	0.1 %
Seq Scan	1	0.534 ms	100.0 %
farm	1	3.113 ms	0.4 %
Seq Scan	1	3.113 ms	100.0 %
farm_geocoding	1	1.837 ms	0.2 %
Seq Scan	1	1.837 ms	100.0 %
farmer	1	1.238 ms	0.1 %
Seq Scan	1	1.238 ms	100.0 %
field	1	79.095 ms	9.6 %
Seq Scan	1	79.095 ms	100.0 %
layer	1	2.520 ms	0.3 %
Seq Scan	1	2.520 ms	100.0 %
zone	1	331.795 ms	40.1 %
Seq Scan	1	331.795 ms	100.0 %

explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Qmiw : Optimization for: plan #rhM

Color mode:

Visible columns:

Optimization path:

Per node type stats

Per table stats