explain.depesz.com

PostgreSQL's explain analyze made readable

Result: jBAs : teacher birthday email with null check

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

Nested Loop (cost=0.43..247,905.15 rows=4 width=4) (actual time=1.821..681.718 rows=1,264 loops=1)

  • Buffers: shared hit=105992
2. 652.555 652.555 ↓ 108.3 3,250 1

Seq Scan on person p (cost=0.00..246,475.70 rows=30 width=4) (actual time=0.483..652.555 rows=3,250 loops=1)

  • Filter: ((birthday IS NOT NULL) AND (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=80216
3. 12.445 29.250 ↓ 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.008..0.009 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
4.          

SubPlan (for Index Scan)

5. 0.090 10.670 ↓ 0.0 0 1,067

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

  • Buffers: shared hit=10589
6. 4.268 4.268 ↑ 21.0 1 1,067

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

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

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

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

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

  • Buffers: shared hit=3454
9. 0.409 5.726 ↑ 1.0 1 409

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

  • Group Key: teacherdeactivationevent.teacherid, teacherdeactivationeventreason.reason
  • Buffers: shared hit=3454
10. 1.227 5.317 ↑ 1.0 1 409

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

  • Sort Key: teacherdeactivationeventreason.reason
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=3454
11. 0.678 4.090 ↑ 1.0 1 409

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

  • Buffers: shared hit=3449
12. 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
13. 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.004..0.004 rows=1 loops=444)

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