explain.depesz.com

PostgreSQL's explain analyze made readable

Result: dYdM

Settings
# exclusive inclusive rows x rows loops node
1. 6.256 14,631.053 ↑ 1.0 1 1

Aggregate (cost=260,526.52..260,526.53 rows=1 width=8) (actual time=14,631.053..14,631.053 rows=1 loops=1)

2. 13.622 14,624.797 ↓ 1,426.6 94,156 1

Hash Join (cost=260,486.24..260,526.36 rows=66 width=0) (actual time=14,606.021..14,624.797 rows=94,156 loops=1)

  • Hash Cond: (m_1.municipality_id = l.municipality_id)
3. 5.863 12.449 ↑ 1.0 1,034 1

Hash Join (cost=70.41..104.70 rows=1,034 width=8) (actual time=1.527..12.449 rows=1,034 loops=1)

  • Hash Cond: (mm.municipality_id = m_1.municipality_id)
4. 3.374 5.934 ↑ 1.0 1,034 1

Hash Join (cost=39.15..70.71 rows=1,034 width=4) (actual time=0.864..5.934 rows=1,034 loops=1)

  • Hash Cond: (mm.municipal_unit_id = um.municipal_unit_id)
5. 1.717 1.717 ↑ 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..1.717 rows=1,034 loops=1)

6. 0.043 0.843 ↑ 1.0 325 1

Hash (cost=35.09..35.09 rows=325 width=8) (actual time=0.843..0.843 rows=325 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 21kB
7. 0.064 0.800 ↑ 1.0 325 1

Hash Join (cost=23.50..35.09 rows=325 width=8) (actual time=0.673..0.800 rows=325 loops=1)

  • Hash Cond: (um.municipal_unit_id = mu.municipal_unit_id)
8. 0.415 0.662 ↑ 1.0 325 1

Hash Join (cost=13.19..23.91 rows=325 width=4) (actual time=0.584..0.662 rows=325 loops=1)

  • Hash Cond: (um.regional_unit_id = rm.regional_unit_id)
9. 0.024 0.024 ↑ 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.024 rows=325 loops=1)

10. 0.000 0.223 ↑ 1.0 74 1

Hash (cost=12.27..12.27 rows=74 width=12) (actual time=0.223..0.223 rows=74 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
11. 0.319 0.507 ↑ 1.0 74 1

Hash Join (cost=8.84..12.27 rows=74 width=12) (actual time=0.108..0.507 rows=74 loops=1)

  • Hash Cond: (pm.prefecture_id = p.prefecture_id)
12. 0.027 0.173 ↑ 1.0 74 1

Hash Join (cost=6.62..9.85 rows=74 width=16) (actual time=0.084..0.173 rows=74 loops=1)

  • Hash Cond: (rm.regional_unit_id = pm.regional_unit_id)
13. 0.062 0.125 ↑ 1.0 74 1

Hash Join (cost=3.96..6.16 rows=74 width=8) (actual time=0.051..0.125 rows=74 loops=1)

  • Hash Cond: (rm.regional_unit_id = u.regional_unit_id)
14. 0.028 0.046 ↑ 1.0 74 1

Hash Join (cost=1.29..3.29 rows=74 width=4) (actual time=0.023..0.046 rows=74 loops=1)

  • Hash Cond: (rm.region_id = r.region_id)
15. 0.008 0.008 ↑ 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.008 rows=74 loops=1)

16. 0.006 0.010 ↑ 1.0 13 1

Hash (cost=1.13..1.13 rows=13 width=4) (actual time=0.009..0.010 rows=13 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
17. 0.004 0.004 ↑ 1.0 13 1

Seq Scan on t_region r (cost=0.00..1.13 rows=13 width=4) (actual time=0.002..0.004 rows=13 loops=1)

18. 0.009 0.017 ↑ 1.0 74 1

Hash (cost=1.74..1.74 rows=74 width=4) (actual time=0.017..0.017 rows=74 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
19. 0.008 0.008 ↑ 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.008 rows=74 loops=1)

20. 0.011 0.021 ↑ 1.0 74 1

Hash (cost=1.74..1.74 rows=74 width=8) (actual time=0.020..0.021 rows=74 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
21. 0.010 0.010 ↑ 1.0 74 1

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

22. 0.008 0.015 ↑ 1.0 54 1

Hash (cost=1.54..1.54 rows=54 width=4) (actual time=0.015..0.015 rows=54 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
23. 0.007 0.007 ↑ 1.0 54 1

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

24. 0.041 0.074 ↑ 1.0 325 1

Hash (cost=6.25..6.25 rows=325 width=4) (actual time=0.074..0.074 rows=325 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 20kB
25. 0.033 0.033 ↑ 1.0 325 1

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

26. 0.133 0.652 ↑ 1.0 1,034 1

Hash (cost=18.34..18.34 rows=1,034 width=4) (actual time=0.652..0.652 rows=1,034 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 53kB
27. 0.519 0.519 ↑ 1.0 1,034 1

Seq Scan on t_municipality m_1 (cost=0.00..18.34 rows=1,034 width=4) (actual time=0.006..0.519 rows=1,034 loops=1)

28. 15.717 14,598.726 ↓ 1,426.6 94,156 1

Hash (cost=260,415.00..260,415.00 rows=66 width=4) (actual time=14,598.726..14,598.726 rows=94,156 loops=1)

  • Buckets: 131,072 (originally 1024) Batches: 2 (originally 1) Memory Usage: 3,336kB
29. 16.912 14,583.009 ↓ 1,426.6 94,156 1

Hash Join (cost=260,391.04..260,415.00 rows=66 width=4) (actual time=14,565.437..14,583.009 rows=94,156 loops=1)

  • Hash Cond: (l.location_id = m.location_id)
30. 1.818 1.818 ↑ 1.0 887 1

Seq Scan on t_location l (cost=0.00..18.87 rows=887 width=8) (actual time=0.003..1.818 rows=887 loops=1)

31. 39.098 14,564.279 ↓ 1,426.6 94,156 1

Hash (cost=260,390.21..260,390.21 rows=66 width=4) (actual time=14,564.279..14,564.279 rows=94,156 loops=1)

  • Buckets: 131,072 (originally 1024) Batches: 2 (originally 1) Memory Usage: 3,142kB
32. 51.514 14,525.181 ↓ 1,426.6 94,156 1

Gather (cost=259,957.63..260,390.21 rows=66 width=4) (actual time=14,368.956..14,525.181 rows=94,156 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
33. 53.021 14,473.667 ↓ 1,120.9 31,385 3 / 3

Merge Join (cost=258,957.63..259,383.61 rows=28 width=4) (actual time=14,339.653..14,473.667 rows=31,385 loops=3)

  • Merge Cond: (((h.region)::text = (m.region)::text) AND (((COALESCE(h.location, ''::character varying))::text) = ((COALESCE(m.location, ''::character varying))::text)) AND ((h.municipality)::text = (m.municipality)::text))
34. 325.765 14,353.408 ↑ 1.5 32,040 3 / 3

Sort (cost=258,402.66..258,525.26 rows=49,040 width=101) (actual time=14,279.709..14,353.408 rows=32,040 loops=3)

  • Sort Key: h.region, ((COALESCE(h.location, ''::character varying))::text), h.municipality
  • Sort Method: external merge Disk: 5,016kB
  • Worker 0: Sort Method: external merge Disk: 4,904kB
  • Worker 1: Sort Method: external merge Disk: 4,688kB
35. 14,027.643 14,027.643 ↑ 1.2 39,256 3 / 3

Parallel Seq Scan on t_listing_hist h (cost=0.00..251,897.53 rows=49,040 width=101) (actual time=0.033..14,027.643 rows=39,256 loops=3)

  • Filter: (total_area < '50'::double precision)
  • Rows Removed by Filter: 330,067
36. 63.318 67.238 ↓ 5.7 35,901 3 / 3

Sort (cost=554.98..570.80 rows=6,330 width=81) (actual time=55.102..67.238 rows=35,901 loops=3)

  • Sort Key: m.region, ((COALESCE(m.location, ''::character varying))::text), m.municipality
  • Sort Method: quicksort Memory: 1,402kB
  • Worker 0: Sort Method: quicksort Memory: 1,402kB
  • Worker 1: Sort Method: quicksort Memory: 1,402kB
37. 3.920 3.920 ↑ 1.0 6,330 3 / 3

Seq Scan on t_location_mapping m (cost=0.00..155.30 rows=6,330 width=81) (actual time=2.396..3.920 rows=6,330 loops=3)

Planning time : 31.924 ms
Execution time : 14,653.405 ms