explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 5NWf : teacher birthday email with index

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 758.498 ↓ 316.0 1,264 1

Nested Loop (cost=19,463.45..131,561.43 rows=4 width=4) (actual time=116.984..758.498 rows=1,264 loops=1)

  • Buffers: shared hit=105891 read=3343
  • I/O Timings: read=8.944
2. 622.077 732.848 ↓ 108.3 3,250 1

Bitmap Heap Scan on person p (cost=19,463.03..130,131.98 rows=30 width=4) (actual time=115.846..732.848 rows=3,250 loops=1)

  • Recheck Cond: (birthday IS NOT NULL)
  • Rows Removed by Index Recheck: 3560284
  • 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: 1219385
  • Heap Blocks: exact=27278 lossy=52837
  • Buffers: shared hit=80115 read=3343
  • I/O Timings: read=8.944
3. 110.771 110.771 ↓ 1.0 1,222,635 1

Bitmap Index Scan on person_birthday_collapsed_year (cost=0.00..19,463.02 rows=1,218,118 width=0) (actual time=110.771..110.771 rows=1,222,635 loops=1)

  • Buffers: shared read=3343
  • I/O Timings: read=8.944
4. 11.489 26.000 ↓ 0.0 0 3,250

Index Scan using pk_dcteacher on dcteacher t (cost=0.43..47.64 rows=1 width=4) (actual time=0.007..0.008 rows=0 loops=3,250)

  • Index Cond: (teacherid = p.personid)
  • Filter: ((NOT excluded) OR ((SubPlan 1) AND (SubPlan 2)))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=25776
5.          

SubPlan (for Index Scan)

6. 1.142 9.603 ↓ 0.0 0 1,067

Nested Loop (cost=0.99..298.77 rows=14 width=0) (actual time=0.009..0.009 rows=0 loops=1,067)

  • Buffers: shared hit=10589
7. 3.201 3.201 ↑ 21.0 1 1,067

Index Scan using dcproposal_createduserid on dcproposal (cost=0.43..88.23 rows=21 width=4) (actual time=0.003..0.003 rows=1 loops=1,067)

  • Index Cond: (createduserid = t.teacherid)
  • Buffers: shared hit=4225
8. 5.260 5.260 ↓ 0.0 0 1,052

Index Scan using proposalworkflowstatus_altindex2 on proposalworkflowstatus (cost=0.56..10.01 rows=2 width=4) (actual time=0.005..0.005 rows=0 loops=1,052)

  • Index Cond: (proposalid = dcproposal.proposalid)
  • Filter: (workflowstatusid = 3)
  • Rows Removed by Filter: 2
  • Buffers: shared hit=6364
9. 0.000 4.908 ↑ 1.0 1 409

Subquery Scan on x (cost=16.91..16.94 rows=1 width=4) (actual time=0.012..0.012 rows=1 loops=409)

  • Buffers: shared hit=3454
10. 0.409 4.908 ↑ 1.0 1 409

GroupAggregate (cost=16.91..16.93 rows=1 width=12) (actual time=0.012..0.012 rows=1 loops=409)

  • Group Key: teacherdeactivationevent.teacherid, teacherdeactivationeventreason.reason
  • Buffers: shared hit=3454
11. 0.818 4.499 ↑ 1.0 1 409

Sort (cost=16.91..16.92 rows=1 width=8) (actual time=0.011..0.011 rows=1 loops=409)

  • Sort Key: teacherdeactivationeventreason.reason
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=3454
12. 0.269 3.681 ↑ 1.0 1 409

Nested Loop (cost=0.85..16.90 rows=1 width=8) (actual time=0.008..0.009 rows=1 loops=409)

  • Buffers: shared hit=3449
13. 1.636 1.636 ↑ 1.0 1 409

Index Scan using teacherdeactivationevent_teacherid on teacherdeactivationevent (cost=0.43..8.45 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=409)

  • Index Cond: (teacherid = t.teacherid)
  • Buffers: shared hit=1671
14. 1.776 1.776 ↑ 1.0 1 444

Index Scan using teacherdeactivationeventreason_teacherdeactivationeventid on teacherdeactivationeventreason (cost=0.43..8.45 rows=1 width=8) (actual time=0.003..0.004 rows=1 loops=444)

  • Index Cond: (teacherdeactivationeventid = teacherdeactivationevent.id)
  • Buffers: shared hit=1778
Planning time : 2.219 ms
Execution time : 758.732 ms