explain.depesz.com

PostgreSQL's explain analyze made readable

Result: S1n2

Settings
# exclusive inclusive rows x rows loops node
1. 716.245 1,563,117.494 ↑ 1.0 1 1

Aggregate (cost=266,071,873.65..266,071,873.66 rows=1 width=8) (actual time=1,563,117.494..1,563,117.494 rows=1 loops=1)

2. 251,026.579 1,562,401.249 ↑ 288.3 10,080,082 1

Hash Join (cost=53,700,714.70..258,806,059.03 rows=2,906,325,848 width=0) (actual time=1,311,597.605..1,562,401.249 rows=10,080,082 loops=1)

  • Hash Cond: (COALESCE(shfl.mmsi, vessels.mmsi) = aisd.mmsi)
  • Join Filter: (aisd."time" > GREATEST(COALESCE(shfl.valid_from, ('2020-01-01 00:00:00'::timestamp without time zone)), (max(sh_basic_log.end_time))))
  • Rows Removed by Join Filter: 87,047,076
3. 4.548 257.150 ↑ 11.6 10,042 1

Merge Left Join (cost=57,236.90..59,007.82 rows=116,204 width=32) (actual time=247.180..257.150 rows=10,042 loops=1)

  • Merge Cond: ((COALESCE(shfl.mmsi, vessels.mmsi)) = sh_basic_log.mmsi)
4. 7.442 114.631 ↓ 1.8 10,042 1

Sort (cost=50,166.72..50,180.65 rows=5,572 width=24) (actual time=111.208..114.631 rows=10,042 loops=1)

  • Sort Key: (COALESCE(shfl.mmsi, vessels.mmsi))
  • Sort Method: quicksort Memory: 1,169kB
5. 2.212 107.189 ↓ 1.8 10,042 1

Hash Full Join (cost=1.38..49,820.03 rows=5,572 width=24) (actual time=0.550..107.189 rows=10,042 loops=1)

  • Hash Cond: (vessels.mmsi = shfl.mmsi)
6. 104.456 104.456 ↓ 1.8 10,025 1

Seq Scan on vessels (cost=0.00..49,797.72 rows=5,572 width=12) (actual time=0.019..104.456 rows=10,025 loops=1)

  • Filter: ((imo > 0) AND (((dimension_bow + dimension_stern))::numeric > 150.0))
  • Rows Removed by Filter: 24,542
7. 0.007 0.521 ↑ 1.0 17 1

Hash (cost=1.17..1.17 rows=17 width=12) (actual time=0.521..0.521 rows=17 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
8. 0.514 0.514 ↑ 1.0 17 1

Seq Scan on sh_fleet shfl (cost=0.00..1.17 rows=17 width=12) (actual time=0.510..0.514 rows=17 loops=1)

9. 3.863 137.971 ↓ 1.3 5,585 1

Sort (cost=7,070.18..7,080.61 rows=4,171 width=12) (actual time=135.967..137.971 rows=5,585 loops=1)

  • Sort Key: sh_basic_log.mmsi
  • Sort Method: quicksort Memory: 454kB
10. 41.793 134.108 ↓ 1.3 5,585 1

HashAggregate (cost=6,735.96..6,777.67 rows=4,171 width=12) (actual time=132.992..134.108 rows=5,585 loops=1)

  • Group Key: sh_basic_log.mmsi
11. 92.315 92.315 ↑ 1.0 160,997 1

Seq Scan on sh_basic_log (cost=0.00..5,930.97 rows=160,997 width=12) (actual time=1.492..92.315 rows=160,997 loops=1)

12. 725,628.256 1,311,117.520 ↓ 1.0 1,175,789,997 1

Hash (cost=33,355,707.40..33,355,707.40 rows=1,167,116,672 width=12) (actual time=1,311,117.520..1,311,117.520 rows=1,175,789,997 loops=1)

  • Buckets: 131,072 (originally 131072) Batches: 65,536 (originally 32768) Memory Usage: 30,752kB
13. 585,489.264 585,489.264 ↓ 1.0 1,175,789,997 1

Seq Scan on ais_data aisd (cost=0.00..33,355,707.40 rows=1,167,116,672 width=12) (actual time=2.772..585,489.264 rows=1,175,789,997 loops=1)

  • Filter: ("time" < '2020-09-03 00:00:00'::timestamp without time zone)
Planning time : 0.853 ms
Execution time : 1,563,118.423 ms