explain.depesz.com

PostgreSQL's explain analyze made readable

Result: zrE8

Settings
# exclusive inclusive rows x rows loops node
1. 0.020 24,986.319 ↑ 200.0 1 1

GroupAggregate (cost=25,155,987.49..25,156,290.49 rows=200 width=48) (actual time=24,986.318..24,986.319 rows=1 loops=1)

  • Group Key: a.groupby
2. 0.017 24,986.299 ↑ 2,105.3 19 1

Sort (cost=25,155,987.49..25,156,087.49 rows=40,000 width=40) (actual time=24,986.297..24,986.299 rows=19 loops=1)

  • Sort Key: a.groupby
  • Sort Method: quicksort Memory: 25kB
3. 0.002 24,986.282 ↑ 2,105.3 19 1

Subquery Scan on a (cost=25,128,849.95..25,152,929.95 rows=40,000 width=40) (actual time=24,913.010..24,986.282 rows=19 loops=1)

4. 35.169 24,986.280 ↑ 2,105.3 19 1

GroupAggregate (cost=25,128,849.95..25,152,529.95 rows=40,000 width=44) (actual time=24,913.010..24,986.280 rows=19 loops=1)

  • Group Key: t.date, t.groupby
5. 136.043 24,951.111 ↑ 10.3 225,528 1

Sort (cost=25,128,849.95..25,134,669.95 rows=2,328,000 width=68) (actual time=24,909.628..24,951.111 rows=225,528 loops=1)

  • Sort Key: t.date, t.groupby
  • Sort Method: external merge Disk: 5504kB
6. 24.119 24,815.068 ↑ 10.3 225,528 1

Subquery Scan on t (cost=23,803,526.00..24,691,682.27 rows=2,328,000 width=68) (actual time=23,804.323..24,815.068 rows=225,528 loops=1)

7. 518.215 24,790.949 ↑ 10.3 225,528 1

Unique (cost=23,803,526.00..24,668,402.27 rows=2,328,000 width=68) (actual time=23,804.322..24,790.949 rows=225,528 loops=1)

8. 22,042.040 24,272.734 ↑ 20.6 4,190,170 1

Sort (cost=23,803,526.00..24,019,745.06 rows=86,487,627 width=68) (actual time=23,804.321..24,272.734 rows=4,190,170 loops=1)

  • Sort Key: t1.studentid, t1.groupby, t2.date
  • Sort Method: external sort Disk: 106496kB
9. 725.996 2,230.694 ↑ 20.6 4,190,170 1

Merge Join (cost=18,857.78..1,759,832.77 rows=86,487,627 width=68) (actual time=1,029.445..2,230.694 rows=4,190,170 loops=1)

  • Merge Cond: ((t1.studentid)::text = ((t2.studentid)::text))
10. 55.637 55.637 ↓ 1.9 225,528 1

Index Scan using temp1_index2_indicator_oel_l4l on temp1 t1 (cost=0.42..9,978.37 rows=116,397 width=64) (actual time=0.066..55.637 rows=225,528 loops=1)

11. 225.244 1,449.061 ↓ 28.6 4,245,328 1

Materialize (cost=18,857.36..19,600.40 rows=148,608 width=12) (actual time=1,029.374..1,449.061 rows=4,245,328 loops=1)

12. 1,155.092 1,223.817 ↓ 1.9 280,686 1

Sort (cost=18,857.36..19,228.88 rows=148,608 width=12) (actual time=1,029.372..1,223.817 rows=280,686 loops=1)

  • Sort Key: ((t2.studentid)::text)
  • Sort Method: external merge Disk: 9000kB
13. 68.725 68.725 ↓ 1.9 280,701 1

Seq Scan on temp2 t2 (cost=0.00..3,550.08 rows=148,608 width=12) (actual time=0.016..68.725 rows=280,701 loops=1)

Planning time : 0.193 ms