explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 8HiT

Settings
# exclusive inclusive rows x rows loops node
1. 0.628 471,599.803 ↓ 117.0 117 1

Result (cost=33,134.30..33,134.61 rows=1 width=166) (actual time=471,599.336..471,599.803 rows=117 loops=1)

2. 1.356 471,599.175 ↓ 117.0 117 1

Sort (cost=33,134.30..33,134.30 rows=1 width=162) (actual time=471,599.170..471,599.175 rows=117 loops=1)

  • Sort Key: jk.gnr, jk.bnr, jk.fnr, jk.teig_nr
  • Sort Method: quicksort Memory: 41kB
3. 3.104 471,597.819 ↓ 117.0 117 1

Nested Loop Anti Join (cost=116.69..33,134.29 rows=1 width=162) (actual time=6,352.097..471,597.819 rows=117 loops=1)

4. 1.873 12.994 ↓ 117.0 117 1

Nested Loop (cost=114.13..230.35 rows=1 width=110) (actual time=4.748..12.994 rows=117 loops=1)

5. 3.322 9.834 ↓ 117.0 117 1

Hash Join (cost=113.86..229.56 rows=1 width=104) (actual time=4.734..9.834 rows=117 loops=1)

  • Hash Cond: ((jk.municipality_nr = f.municipality_nr) AND (jk.gnr = f.gnr) AND (jk.bnr = f.bnr) AND (jk.fnr = f.fnr))
6. 1.865 1.865 ↓ 1.3 4,967 1

Seq Scan on sl_lop_temp_jordreg_rep4_ar5_areal jk (cost=0.00..74.88 rows=3,888 width=102) (actual time=0.022..1.865 rows=4,967 loops=1)

7. 0.014 4.647 ↓ 60.0 60 1

Hash (cost=113.84..113.84 rows=1 width=10) (actual time=4.647..4.647 rows=60 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
8. 0.006 4.633 ↓ 60.0 60 1

Subquery Scan on f (cost=113.77..113.84 rows=1 width=10) (actual time=2.910..4.633 rows=60 loops=1)

9. 1.581 4.627 ↓ 60.0 60 1

GroupAggregate (cost=113.77..113.83 rows=1 width=54) (actual time=2.909..4.627 rows=60 loops=1)

  • Group Key: jk_1.municipality_nr, jk_1.gnr, jk_1.bnr, jk_1.fnr
  • Filter: ((((sum(jk_1.t1) + sum(jk_1.t2)) + sum(jk_1.t3)) > '5'::double precision) OR (((sum((jk_1.t4 + jk_1.t5)) + sum(jk_1.t6)) + sum(jk_1.t7)) > '25'::double precision))
  • Rows Removed by Filter: 4039
10. 1.662 3.046 ↓ 4,619.0 4,619 1

Sort (cost=113.77..113.78 rows=1 width=38) (actual time=2.892..3.046 rows=4,619 loops=1)

  • Sort Key: jk_1.gnr, jk_1.bnr, jk_1.fnr
  • Sort Method: quicksort Memory: 553kB
11. 1.384 1.384 ↓ 4,619.0 4,619 1

Seq Scan on sl_lop_temp_jordreg_rep4_ar5_areal jk_1 (cost=0.00..113.76 rows=1 width=38) (actual time=0.037..1.384 rows=4,619 loops=1)

  • Filter: ((farm_gnr IS NULL) AND ((ownership)::text = ANY ('{M,F}'::text[])) AND (gnr <> 0) AND (bnr <> 0) AND (municipality_nr = 2004))
  • Rows Removed by Filter: 348
12. 1.287 1.287 ↑ 1.0 1 117

Index Scan using kode_felles_kommune_unique_komid on felles_kommune fk (cost=0.27..0.79 rows=1 width=12) (actual time=0.011..0.011 rows=1 loops=117)

  • Index Cond: (komid = jk.municipality_nr)
13. 5.265 471,581.721 ↓ 0.0 0 117

Nested Loop (cost=2.56..32,903.88 rows=1 width=8) (actual time=4,030.613..4,030.613 rows=0 loops=117)

14. 4.446 471,553.290 ↓ 198.0 198 117

Nested Loop (cost=2.14..32,902.11 rows=1 width=18) (actual time=3,381.604..4,030.370 rows=198 loops=117)

15. 66,613.818 471,539.367 ↓ 3.0 3 117

Nested Loop (cost=1.72..32,854.06 rows=1 width=14) (actual time=3,381.575..4,030.251 rows=3 loops=117)

  • Join Filter: ((pp.parcel_id)::text = (pp2.parcel_id)::text)
  • Rows Removed by Join Filter: 2401469
16. 64,059.057 250,868.709 ↓ 1,316,725.0 1,316,725 117

Nested Loop (cost=1.29..32,852.53 rows=1 width=27) (actual time=0.446..2,144.177 rows=1,316,725 loops=117)

17. 14,444.289 56,692.350 ↓ 1,112,114.0 1,112,114 117

Nested Loop (cost=0.86..32,849.88 rows=1 width=22) (actual time=0.441..484.550 rows=1,112,114 loops=117)

18. 6,003.153 6,003.153 ↓ 273.0 273 117

Index Scan using property_municipality_nr_gnr_bnr_fnr_key on property p2 (cost=0.43..32,798.97 rows=1 width=10) (actual time=0.433..51.309 rows=273 loops=117)

  • Index Cond: ((jk.gnr = gnr) AND (jk.bnr = bnr))
19. 36,244.908 36,244.908 ↓ 1,357.0 4,071 31,962

Index Scan using property_municipality_nr_gnr_bnr_fnr_key on property p (cost=0.43..50.88 rows=3 width=12) (actual time=0.005..1.134 rows=4,071 loops=31,962)

  • Index Cond: ((municipality_nr = 2004) AND (jk.fnr = fnr))
20. 130,117.302 130,117.302 ↑ 4.0 1 130,117,302

Index Scan using idx_org_property_property_parcel_property_ref on property_parcel pp (cost=0.43..2.61 rows=4 width=13) (actual time=0.001..0.001 rows=1 loops=130,117,302)

  • Index Cond: (property_ref = p.id)
21. 154,056.840 154,056.840 ↑ 2.0 2 154,056,840

Index Scan using idx_org_property_property_parcel_property_ref on property_parcel pp2 (cost=0.43..1.48 rows=4 width=13) (actual time=0.001..0.001 rows=2 loops=154,056,840)

  • Index Cond: (property_ref = p2.id)
22. 9.477 9.477 ↑ 1.0 66 351

Index Scan using uidx_org_property_property_group_municipality_nr_name on property_group pg (cost=0.42..47.39 rows=66 width=4) (actual time=0.006..0.027 rows=66 loops=351)

  • Index Cond: (municipality_nr = 2004)
23. 23.166 23.166 ↓ 0.0 0 23,166

Index Only Scan using idx_org_property_property_group_property_sub on property_group_property pgp (cost=0.42..1.77 rows=1 width=12) (actual time=0.001..0.001 rows=0 loops=23,166)

  • Index Cond: ((property_group_ref = pg.id) AND (gnr = p.gnr) AND (bnr = p.bnr) AND (fnr = p.fnr))
  • Heap Fetches: 0
Planning time : 11.356 ms
Execution time : 471,600.053 ms