explain.depesz.com

PostgreSQL's explain analyze made readable

Result: gEAQ

Settings
# exclusive inclusive rows x rows loops node
1. 7.779 107,119.648 ↑ 1.0 1 1

Aggregate (cost=14,842.23..14,842.24 rows=1 width=8) (actual time=107,119.648..107,119.648 rows=1 loops=1)

2. 35.210 107,111.869 ↓ 15,642.0 15,642 1

Nested Loop (cost=442.50..14,842.22 rows=1 width=0) (actual time=489.391..107,111.869 rows=15,642 loops=1)

3. 19.345 107,029.733 ↓ 15,642.0 15,642 1

Nested Loop (cost=442.22..14,840.32 rows=1 width=8) (actual time=488.099..107,029.733 rows=15,642 loops=1)

4. 0.523 21.588 ↓ 8.1 292 1

Nested Loop (cost=53.67..130.93 rows=36 width=85) (actual time=4.266..21.588 rows=292 loops=1)

5. 1.186 6.265 ↓ 10.0 50 1

Hash Join (cost=53.39..64.31 rows=5 width=12) (actual time=3.772..6.265 rows=50 loops=1)

  • Hash Cond: (l.municipality_id = mm.municipality_id)
6. 1.376 2.331 ↑ 1.0 63 1

Bitmap Heap Scan on t_location l (cost=4.76..15.39 rows=63 width=8) (actual time=0.966..2.331 rows=63 loops=1)

  • Filter: (target IS TRUE)
  • Heap Blocks: exact=5
7. 0.955 0.955 ↑ 1.0 63 1

Bitmap Index Scan on t_location_tgt_loc_idx (cost=0.00..4.75 rows=63 width=0) (actual time=0.955..0.955 rows=63 loops=1)

  • Index Cond: (target = true)
8. 0.040 2.748 ↓ 1.5 122 1

Hash (cost=47.59..47.59 rows=83 width=4) (actual time=2.747..2.748 rows=122 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 13kB
9. 0.178 2.708 ↓ 1.5 122 1

Hash Join (cost=25.54..47.59 rows=83 width=4) (actual time=2.525..2.708 rows=122 loops=1)

  • Hash Cond: (mm.municipal_unit_id = um.municipal_unit_id)
10. 0.165 0.165 ↑ 1.0 1,034 1

Seq Scan on t_municipality_mapping mm (cost=0.00..17.34 rows=1,034 width=8) (actual time=0.005..0.165 rows=1,034 loops=1)

11. 0.017 2.365 ↓ 2.5 66 1

Hash (cost=25.22..25.22 rows=26 width=8) (actual time=2.365..2.365 rows=66 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
12. 0.057 2.348 ↓ 2.5 66 1

Hash Join (cost=17.49..25.22 rows=26 width=8) (actual time=2.301..2.348 rows=66 loops=1)

  • Hash Cond: (mu.municipal_unit_id = um.municipal_unit_id)
13. 0.028 0.028 ↑ 1.0 325 1

Seq Scan on t_municipal_unit mu (cost=0.00..6.25 rows=325 width=4) (actual time=0.003..0.028 rows=325 loops=1)

14. 0.013 2.263 ↓ 2.5 66 1

Hash (cost=17.16..17.16 rows=26 width=4) (actual time=2.262..2.263 rows=66 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
15. 0.046 2.250 ↓ 2.5 66 1

Hash Join (cost=9.43..17.16 rows=26 width=4) (actual time=2.220..2.250 rows=66 loops=1)

  • Hash Cond: (um.regional_unit_id = rm.regional_unit_id)
16. 0.025 0.025 ↑ 1.0 325 1

Seq Scan on t_municipal_unit_mapping um (cost=0.00..6.25 rows=325 width=8) (actual time=0.003..0.025 rows=325 loops=1)

17. 0.287 2.179 ↓ 1.3 8 1

Hash (cost=9.36..9.36 rows=6 width=12) (actual time=2.179..2.179 rows=8 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
18. 0.331 1.892 ↓ 1.3 8 1

Hash Join (cost=7.28..9.36 rows=6 width=12) (actual time=1.884..1.892 rows=8 loops=1)

  • Hash Cond: (u.regional_unit_id = rm.regional_unit_id)
19. 0.007 0.007 ↑ 1.0 74 1

Seq Scan on t_regional_unit u (cost=0.00..1.74 rows=74 width=4) (actual time=0.002..0.007 rows=74 loops=1)

20. 0.000 1.554 ↓ 1.3 8 1

Hash (cost=7.21..7.21 rows=6 width=8) (actual time=1.554..1.554 rows=8 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
21. 0.636 2.003 ↓ 1.3 8 1

Hash Join (cost=5.40..7.21 rows=6 width=8) (actual time=1.541..2.003 rows=8 loops=1)

  • Hash Cond: (p.prefecture_id = pm.prefecture_id)
22. 0.008 0.008 ↑ 1.0 54 1

Seq Scan on t_prefecture p (cost=0.00..1.54 rows=54 width=4) (actual time=0.004..0.008 rows=54 loops=1)

23. 0.004 1.359 ↓ 1.3 8 1

Hash (cost=5.33..5.33 rows=6 width=12) (actual time=1.359..1.359 rows=8 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
24. 0.302 1.355 ↓ 1.3 8 1

Hash Join (cost=3.25..5.33 rows=6 width=12) (actual time=1.349..1.355 rows=8 loops=1)

  • Hash Cond: (pm.regional_unit_id = rm.regional_unit_id)
25. 0.009 0.009 ↑ 1.0 74 1

Seq Scan on t_prefecture_mapping pm (cost=0.00..1.74 rows=74 width=8) (actual time=0.005..0.009 rows=74 loops=1)

26. 0.311 1.044 ↓ 1.3 8 1

Hash (cost=3.18..3.18 rows=6 width=4) (actual time=1.043..1.044 rows=8 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
27. 0.334 0.733 ↓ 1.3 8 1

Hash Join (cost=1.18..3.18 rows=6 width=4) (actual time=0.722..0.733 rows=8 loops=1)

  • Hash Cond: (rm.region_id = r.region_id)
28. 0.010 0.010 ↑ 1.0 74 1

Seq Scan on t_region_mapping rm (cost=0.00..1.74 rows=74 width=8) (actual time=0.003..0.010 rows=74 loops=1)

29. 0.007 0.389 ↑ 1.0 1 1

Hash (cost=1.16..1.16 rows=1 width=4) (actual time=0.388..0.389 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
30. 0.382 0.382 ↑ 1.0 1 1

Seq Scan on t_region r (cost=0.00..1.16 rows=1 width=4) (actual time=0.380..0.382 rows=1 loops=1)

  • Filter: ((region)::text = 'ΑΤΤΙΚΗΣ'::text)
  • Rows Removed by Filter: 12
31. 14.800 14.800 ↑ 1.2 6 50

Index Scan using t_location_mapping_location_idx on t_location_mapping m (cost=0.28..13.26 rows=7 width=81) (actual time=0.070..0.296 rows=6 loops=50)

  • Index Cond: (location_id = l.location_id)
32. 95,759.648 106,988.800 ↓ 54.0 54 292

Bitmap Heap Scan on t_listing_hist h (cost=388.55..408.58 rows=1 width=101) (actual time=87.577..366.400 rows=54 loops=292)

  • Recheck Cond: (((municipality)::text = (m.municipality)::text) AND ((region)::text = (m.region)::text))
  • Rows Removed by Index Recheck: 3
  • Filter: ((total_area < '50'::double precision) AND ((COALESCE(m.location, ''::character varying))::text = (COALESCE(location, ''::character varying))::text))
  • Rows Removed by Filter: 6,967
  • Heap Blocks: exact=1,982,354
33. 188.632 11,229.152 ↓ 0.0 0 292

BitmapAnd (cost=388.55..388.55 rows=5 width=0) (actual time=38.456..38.456 rows=0 loops=292)

34. 1,511.392 1,511.392 ↓ 16.9 7,215 292

Bitmap Index Scan on t_listing_hist_idx_municipality (cost=0.00..21.03 rows=427 width=0) (actual time=5.176..5.176 rows=7,215 loops=292)

  • Index Cond: ((municipality)::text = (m.municipality)::text)
35. 9,529.128 9,529.128 ↓ 8.4 108,073 292

Bitmap Index Scan on t_listing_hist_idx_region (cost=0.00..367.27 rows=12,877 width=0) (actual time=32.634..32.634 rows=108,073 loops=292)

  • Index Cond: ((region)::text = (m.region)::text)
36. 46.926 46.926 ↑ 1.0 1 15,642

Index Only Scan using t_municipality_pkey on t_municipality m_1 (cost=0.28..1.90 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=15,642)

  • Index Cond: (municipality_id = mm.municipality_id)
  • Heap Fetches: 0
Planning time : 55.362 ms
Execution time : 107,143.942 ms