explain.depesz.com

PostgreSQL's explain analyze made readable

Result: r5j

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 2,863.733 ↓ 0.0 0 1

Unique (cost=4,790.34..4,790.36 rows=2 width=12) (actual time=2,863.733..2,863.733 rows=0 loops=1)

  • Buffers: shared hit=4247 read=14200 dirtied=12265
2. 0.030 2,863.732 ↓ 0.0 0 1

Sort (cost=4,790.34..4,790.34 rows=2 width=12) (actual time=2,863.732..2,863.732 rows=0 loops=1)

  • Sort Key: hrrr_temp_tbl_2018_04.station_id, (max(hrrr_temp_tbl_2018_04."2m_temp_c")), (min(hrrr_temp_tbl_2018_04."2m_temp_c"))
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=4247 read=14200 dirtied=12265
3. 0.002 2,863.702 ↓ 0.0 0 1

Append (cost=413.89..4,790.33 rows=2 width=12) (actual time=2,863.702..2,863.702 rows=0 loops=1)

  • Buffers: shared hit=4241 read=14200 dirtied=12265
4. 0.000 2,852.640 ↓ 0.0 0 1

GroupAggregate (cost=413.89..4,361.08 rows=1 width=12) (actual time=2,852.640..2,852.640 rows=0 loops=1)

  • Group Key: hrrr_temp_tbl_2018_04.station_id
  • Buffers: shared hit=3126 read=14200 dirtied=12265
5.          

Initplan (forGroupAggregate)

6. 0.315 24.755 ↑ 3.5 94 1

Subquery Scan on q1_1 (cost=0.41..413.30 rows=333 width=8) (actual time=1.904..24.755 rows=94 loops=1)

  • Filter: (q1_1.dist_control <= 10::double precision)
  • Rows Removed by Filter: 906
  • Buffers: shared hit=1084 read=16
7. 0.529 24.440 ↑ 1.0 1,000 1

Limit (cost=0.41..400.80 rows=1,000 width=56) (actual time=1.903..24.440 rows=1,000 loops=1)

  • Buffers: shared hit=1084 read=16
8. 23.911 23.911 ↑ 5,780.8 1,000 1

Index Scan using geodesic_2k_gix1 on geodesic_2k geodesic_2k_1 (cost=0.41..2,314,573.96 rows=5,780,812 width=56) (actual time=1.900..23.911 rows=1,000 loops=1)

  • Order By: (pos4326 <-> 0101000020E61000000000000000C052C00000000000004440::geometry)
  • Buffers: shared hit=1084 read=16
9. 2,852.639 2,852.639 ↓ 0.0 0 1

Index Scan using hrrr_temp_tbl_station_idx_2018_04 on hrrr_temp_tbl_2018_04 (cost=0.58..3,947.76 rows=1 width=12) (actual time=2,852.639..2,852.639 rows=0 loops=1)

  • Index Cond: (station_id = ANY ($1))
  • Filter: (("timestamp" >= 2018-02-01 00:00:00::timestamp without time zone) AND ("timestamp" <= 2018-02-01 23:00:00::timestamp without time zone) AND ("2m_temp_c" < 9999::double precision))
  • Rows Removed by Filter: 15792
  • Buffers: shared hit=3126 read=14200 dirtied=12265
10. 0.000 11.060 ↓ 0.0 0 1

HashAggregate (cost=429.22..429.23 rows=1 width=12) (actual time=11.060..11.060 rows=0 loops=1)

  • Group Key: hrrr_temp_tbl_2018_00.station_id
  • Buffers: shared hit=1115
11.          

Initplan (forHashAggregate)

12. 0.266 10.991 ↑ 3.5 94 1

Subquery Scan on q1 (cost=0.41..413.30 rows=333 width=8) (actual time=0.525..10.991 rows=94 loops=1)

  • Filter: (q1.dist_control <= 10::double precision)
  • Rows Removed by Filter: 906
  • Buffers: shared hit=1021
13. 0.501 10.725 ↑ 1.0 1,000 1

Limit (cost=0.41..400.80 rows=1,000 width=56) (actual time=0.522..10.725 rows=1,000 loops=1)

  • Buffers: shared hit=1021
14. 10.224 10.224 ↑ 5,780.8 1,000 1

Index Scan using geodesic_2k_gix1 on geodesic_2k (cost=0.41..2,314,573.96 rows=5,780,812 width=56) (actual time=0.520..10.224 rows=1,000 loops=1)

  • Order By: (pos4326 <-> 0101000020E61000000000000000C052C00000000000004440::geometry)
  • Buffers: shared hit=1021
15. 0.002 11.058 ↓ 0.0 0 1

Bitmap Heap Scan on hrrr_temp_tbl_2018_00 (cost=3.99..15.91 rows=1 width=12) (actual time=11.058..11.058 rows=0 loops=1)

  • Recheck Cond: (station_id = ANY ($0))
  • Filter: (("timestamp" >= 2018-02-01 00:00:00::timestamp without time zone) AND ("timestamp" <= 2018-02-01 23:00:00::timestamp without time zone) AND ("2m_temp_c" < 9999::double precision))
  • Buffers: shared hit=1115
16. 11.056 11.056 ↓ 0.0 0 1

Bitmap Index Scan on hrrr_temp_tbl_station_idx_2018_00 (cost=0.00..3.98 rows=64 width=0) (actual time=11.056..11.056 rows=0 loops=1)

  • Index Cond: (station_id = ANY ($0))
  • Buffers: shared hit=1115