explain.depesz.com

PostgreSQL's explain analyze made readable

Result: E49o : teacher birthday email

Settings
# exclusive inclusive rows x rows loops node
1. 3.797 3,751.026 ↓ 70.9 1,913 1

Nested Loop (cost=0.43..250,677.40 rows=27 width=4) (actual time=6.470..3,751.026 rows=1,913 loops=1)

  • Buffers: shared hit=12928 read=87813
  • I/O Timings: read=2858.033
2. 1,270.729 1,270.729 ↓ 19.6 3,250 1

Seq Scan on person p (cost=0.00..246,475.70 rows=166 width=4) (actual time=2.855..1,270.729 rows=3,250 loops=1)

  • Filter: ((date_part('month'::text, (birthday)::timestamp without time zone) = '1'::double precision) AND (date_part('day'::text, (birthday)::timestamp without time zone) = '28'::double precision))
  • Rows Removed by Filter: 6647482
  • Buffers: shared hit=2 read=80214
  • I/O Timings: read=421.956
3. 1,164.090 2,476.500 ↑ 1.0 1 3,250

Index Scan using pk_dcteacher on dcteacher t (cost=0.43..25.30 rows=1 width=4) (actual time=0.762..0.762 rows=1 loops=3,250)

  • Index Cond: (teacherid = p.personid)
  • Filter: ((NOT excluded) OR (SubPlan 1))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=12926 read=7599
  • I/O Timings: read=2436.077
4.          

SubPlan (for Index Scan)

5. 0.000 1,312.410 ↑ 1.0 1 1,067

Subquery Scan on x (cost=16.91..16.94 rows=1 width=4) (actual time=1.230..1.230 rows=1 loops=1,067)

  • Buffers: shared hit=5157 read=3635
  • I/O Timings: read=1290.414
6. 2.134 1,312.410 ↑ 1.0 1 1,067

GroupAggregate (cost=16.91..16.93 rows=1 width=12) (actual time=1.230..1.230 rows=1 loops=1,067)

  • Group Key: teacherdeactivationevent.teacherid, teacherdeactivationeventreason.reason
  • Buffers: shared hit=5157 read=3635
  • I/O Timings: read=1290.414
7. 3.201 1,310.276 ↑ 1.0 1 1,067

Sort (cost=16.91..16.92 rows=1 width=8) (actual time=1.228..1.228 rows=1 loops=1,067)

  • Sort Key: teacherdeactivationeventreason.reason
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=5157 read=3635
  • I/O Timings: read=1290.414
8. 2.719 1,307.075 ↑ 1.0 1 1,067

Nested Loop (cost=0.85..16.90 rows=1 width=8) (actual time=1.175..1.225 rows=1 loops=1,067)

  • Buffers: shared hit=5152 read=3635
  • I/O Timings: read=1290.414
9. 648.736 648.736 ↑ 1.0 1 1,067

Index Scan using teacherdeactivationevent_teacherid on teacherdeactivationevent (cost=0.43..8.45 rows=1 width=8) (actual time=0.592..0.608 rows=1 loops=1,067)

  • Index Cond: (teacherid = t.teacherid)
  • Buffers: shared hit=2508 read=1812
  • I/O Timings: read=641.820
10. 655.620 655.620 ↑ 1.0 1 1,115

Index Scan using teacherdeactivationeventreason_teacherdeactivationeventid on teacherdeactivationeventreason (cost=0.43..8.45 rows=1 width=8) (actual time=0.587..0.588 rows=1 loops=1,115)

  • Index Cond: (teacherdeactivationeventid = teacherdeactivationevent.id)
  • Buffers: shared hit=2644 read=1823
  • I/O Timings: read=648.594
Planning time : 11.442 ms
Execution time : 3,751.338 ms