explain.depesz.com

PostgreSQL's explain analyze made readable

Result: vUGW

Settings
# exclusive inclusive rows x rows loops node
1. 0.024 2,004.332 ↑ 1.0 1 1

Aggregate (cost=124,452.26..124,452.27 rows=1 width=8) (actual time=2,004.332..2,004.332 rows=1 loops=1)

2. 0.173 2,004.308 ↑ 7.1 419 1

Unique (cost=124,392.36..124,414.83 rows=2,995 width=8) (actual time=2,004.075..2,004.308 rows=419 loops=1)

3. 1.334 2,004.135 ↑ 2.1 1,458 1

Sort (cost=124,392.36..124,399.85 rows=2,995 width=8) (actual time=2,004.074..2,004.135 rows=1,458 loops=1)

  • Sort Key: c.iata, c_1.iata
  • Sort Method: quicksort Memory: 117kB
4. 1,147.542 2,002.801 ↑ 2.1 1,458 1

Hash Join (cost=308.25..124,219.43 rows=2,995 width=8) (actual time=34.884..2,002.801 rows=1,458 loops=1)

  • Hash Cond: (a.city_id = c.id)
  • Join Filter: ((dirs.fst = a.iata) OR (dirs.fst = c.iata))
  • Rows Removed by Join Filter: 4786317
5. 572.378 854.196 ↑ 1.3 4,787,775 1

Nested Loop (cost=154.69..107,637.43 rows=6,258,450 width=28) (actual time=1.032..854.196 rows=4,787,775 loops=1)

6. 5.068 5.068 ↑ 1.0 5,535 1

Index Scan using airports_city_id_idx on airports a (cost=0.28..643.13 rows=5,535 width=20) (actual time=0.009..5.068 rows=5,535 loops=1)

7. 245.329 276.750 ↑ 1.3 865 5,535

Materialize (cost=154.41..28,746.06 rows=1,131 width=8) (actual time=0.000..0.050 rows=865 loops=5,535)

8. 5.480 31.421 ↑ 1.3 865 1

Nested Loop (cost=154.41..28,740.40 rows=1,131 width=8) (actual time=1.019..31.421 rows=865 loops=1)

9. 1.805 3.801 ↑ 1.0 5,535 1

Hash Join (cost=153.56..339.46 rows=5,535 width=8) (actual time=0.945..3.801 rows=5,535 loops=1)

  • Hash Cond: (a_1.city_id = c_1.id)
10. 1.078 1.078 ↑ 1.0 5,535 1

Seq Scan on airports a_1 (cost=0.00..171.35 rows=5,535 width=20) (actual time=0.005..1.078 rows=5,535 loops=1)

11. 0.458 0.918 ↑ 1.0 3,358 1

Hash (cost=111.58..111.58 rows=3,358 width=20) (actual time=0.918..0.918 rows=3,358 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 203kB
12. 0.460 0.460 ↑ 1.0 3,358 1

Seq Scan on cities c_1 (cost=0.00..111.58 rows=3,358 width=20) (actual time=0.004..0.460 rows=3,358 loops=1)

13. 0.000 22.140 ↓ 0.0 0 5,535

Bitmap Heap Scan on dirs (cost=0.86..5.02 rows=11 width=8) (actual time=0.004..0.004 rows=0 loops=5,535)

  • Recheck Cond: ((snd = a_1.iata) OR (snd = c_1.iata))
  • Heap Blocks: exact=161
14. 5.535 22.140 ↓ 0.0 0 5,535

BitmapOr (cost=0.86..0.86 rows=11 width=0) (actual time=0.004..0.004 rows=0 loops=5,535)

15. 11.070 11.070 ↓ 0.0 0 5,535

Bitmap Index Scan on dirs_snd_idx (cost=0.00..0.32 rows=6 width=0) (actual time=0.002..0.002 rows=0 loops=5,535)

  • Index Cond: (snd = a_1.iata)
16. 5.535 5.535 ↓ 0.0 0 5,535

Bitmap Index Scan on dirs_snd_idx (cost=0.00..0.32 rows=6 width=0) (actual time=0.001..0.001 rows=0 loops=5,535)

  • Index Cond: (snd = c_1.iata)
17. 0.514 1.063 ↑ 1.0 3,358 1

Hash (cost=111.58..111.58 rows=3,358 width=20) (actual time=1.063..1.063 rows=3,358 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 203kB
18. 0.549 0.549 ↑ 1.0 3,358 1

Seq Scan on cities c (cost=0.00..111.58 rows=3,358 width=20) (actual time=0.008..0.549 rows=3,358 loops=1)