explain.depesz.com

PostgreSQL's explain analyze made readable

Result: E0n : PG10-QueryPlan-MoreComplexAggJoins-Single

Settings
# exclusive inclusive rows x rows loops node
1. 1,790.667 11,929.173 ↑ 2.0 68,031 1

GroupAggregate (cost=682,241.67..750,338.95 rows=135,362 width=134) (actual time=8,782.945..11,929.173 rows=68,031 loops=1)

  • Group Key: s.station_id, c.calendarweek
2. 3,723.056 10,138.506 ↑ 1.2 2,929,070 1

Sort (cost=682,241.67..691,389.73 rows=3,659,224 width=14) (actual time=8,782.877..10,138.506 rows=2,929,070 loops=1)

  • Sort Key: s.station_id, c.calendarweek
  • Sort Method: external merge Disk: 74592kB
3. 2,497.168 6,415.450 ↑ 1.2 2,929,070 1

Hash Join (cost=416.49..158,253.42 rows=3,659,224 width=14) (actual time=10.917..6,415.450 rows=2,929,070 loops=1)

  • Hash Cond: (d.calendar_id = c.calendar_id)
4. 2,450.112 3,909.855 ↑ 1.2 2,929,070 1

Hash Join (cost=89.47..111,732.56 rows=3,659,224 width=16) (actual time=2.468..3,909.855 rows=2,929,070 loops=1)

  • Hash Cond: (d.station_id = s.station_id)
5. 1,457.328 1,457.328 ↑ 1.2 2,929,070 1

Seq Scan on daily d (cost=0.00..65,458.11 rows=3,659,224 width=16) (actual time=0.043..1,457.328 rows=2,929,070 loops=1)

  • Filter: ((t_min IS NOT NULL) OR (t_max IS NOT NULL))
  • Rows Removed by Filter: 975841
6. 1.236 2.415 ↑ 1.0 2,554 1

Hash (cost=57.54..57.54 rows=2,554 width=4) (actual time=2.415..2.415 rows=2,554 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 122kB
7. 1.179 1.179 ↑ 1.0 2,554 1

Seq Scan on station s (cost=0.00..57.54 rows=2,554 width=4) (actual time=0.003..1.179 rows=2,554 loops=1)

8. 4.164 8.427 ↑ 1.0 8,401 1

Hash (cost=222.01..222.01 rows=8,401 width=6) (actual time=8.427..8.427 rows=8,401 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 457kB
9. 4.263 4.263 ↑ 1.0 8,401 1

Seq Scan on calendar c (cost=0.00..222.01 rows=8,401 width=6) (actual time=0.003..4.263 rows=8,401 loops=1)

Planning time : 0.429 ms
Execution time : 11,966.897 ms