explain.depesz.com

PostgreSQL's explain analyze made readable

Result: DRJU

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.046 24,259.673 ↓ 10.7 128 1

Unique (cost=8,219,066.49..8,219,066.61 rows=12 width=44) (actual time=24,259.625..24,259.673 rows=128 loops=1)

2. 0.211 24,259.627 ↓ 10.7 128 1

Sort (cost=8,219,066.49..8,219,066.52 rows=12 width=44) (actual time=24,259.623..24,259.627 rows=128 loops=1)

  • Sort Key: e.event_id, e.nws_alert_id, (array_agg(DISTINCT z.zip))
  • Sort Method: quicksort Memory: 146kB
3. 977.760 24,259.416 ↓ 10.7 128 1

GroupAggregate (cost=0.72..8,219,066.27 rows=12 width=44) (actual time=333.720..24,259.416 rows=128 loops=1)

  • Group Key: e.event_id
4. 67.285 23,281.656 ↑ 27.8 1,030,151 1

Nested Loop (cost=0.72..8,075,814.68 rows=28,650,288 width=18) (actual time=180.394..23,281.656 rows=1,030,151 loops=1)

5. 2,824.412 22,948.011 ↑ 27.7 6,659 1

Nested Loop (cost=0.29..5,749,020.44 rows=184,512 width=18) (actual time=180.166..22,948.011 rows=6,659 loops=1)

  • Join Filter: (SubPlan 1)
  • Rows Removed by Join Filter: 3985375
6. 17.530 17.530 ↓ 10.8 129 1

Index Scan using realtime_weather_event_pkey on realtime_weather_event e (cost=0.29..3,066.20 rows=12 width=12) (actual time=17.184..17.530 rows=129 loops=1)

  • Filter: ((expires > '2019-11-26 15:28:36+00'::timestamp with time zone) AND ((event)::text = ANY ('{"Ice Storm Warning","Blizzard Warning","Winter Storm Warning","Tropical Storm Watch","Tropical Storm Warning","Hurricane Watch","Hurricane Warning"}'::text[])))
  • Rows Removed by Filter: 50531
7. 131.513 145.899 ↓ 1.0 30,946 129

Materialize (cost=0.00..1,250.00 rows=30,752 width=12) (actual time=0.000..1.131 rows=30,946 loops=129)

8. 14.386 14.386 ↓ 1.0 30,946 1

Seq Scan on uszips z (cost=0.00..1,096.24 rows=30,752 width=12) (actual time=0.023..14.386 rows=30,946 loops=1)

  • Filter: (("substring"((zip)::text, 1, 3) <> '000'::text) AND ("substring"((zip)::text, 1, 3) <> '001'::text))
  • Rows Removed by Filter: 116
9.          

SubPlan (for Nested Loop)

10. 0.000 19,960.170 ↑ 200.0 5 3,992,034

Result (cost=0.29..28.31 rows=1,000 width=32) (actual time=0.004..0.005 rows=5 loops=3,992,034)

11. 15,968.136 19,960.170 ↑ 200.0 5 3,992,034

ProjectSet (cost=0.29..13.31 rows=1,000 width=32) (actual time=0.004..0.005 rows=5 loops=3,992,034)

12. 3,992.034 3,992.034 ↑ 1.0 1 3,992,034

Index Scan using realtime_weather_event_pkey on realtime_weather_event (cost=0.29..8.31 rows=1 width=21) (actual time=0.001..0.001 rows=1 loops=3,992,034)

  • Index Cond: (event_id = e.event_id)
13. 266.360 266.360 ↑ 1.5 155 6,659

Index Only Scan using zip_county_geohash_zip_idx on zip_county_geohash zg (cost=0.43..10.26 rows=235 width=6) (actual time=0.012..0.040 rows=155 loops=6,659)

  • Index Cond: (zipcode = (z.zip)::text)
  • Heap Fetches: 1030151
Planning time : 0.550 ms